Tag: database

Rename a Postgresql database

#database #postgresql

Here’s the procedure to rename a PostgreSQL database: Disconnect from the database that you want to rename and connect to a different database. Check and terminate all active connections to the database that you want to rename. Use the ALTER DATABASE statement to rename the database to the new one. Let’s take a look at an example of re... Read more

20 May 2019 - 1 minute read

How to duplicate a Postgresql database

#database #postgresql

If you need to duplicate an existing Postgresql database, and possibly transfer ownership of the database objects to a new user, here’s how to do that in a quick way: -- First, I recommand getting the size of the database to copy, as this might be important for the rest of the process. SELECT pg_database.datname,pg_size_pretty(pg_database_size(... Read more

20 Apr 2019 - less than 1 minute read

Forcing ASMM component to shrink

#oracle #database

Here’s the way to force the shared pool to shrink dynamically. Documentation states that ASMM can only increase shared pool, and can’t shrink. When the automatic shared memory management feature is enabled, the internal tuning algorithm tries to determine an optimal size for the shared pool based on the workload. It usually converges on this va... Read more

20 Mar 2019 - 2 minute read

How to extract Oracle user DDL

#database #oracle

In some cases, you need to duplicate an Oracle user, along with all it’s privileges. Doing this manually can be tedious. Hopefully, we can use the data dictionary to extract the data we need and dump it into a SQL file to modify and replay. Here’s how to do it: set head off set pages 0 set long 9999999 spool user_script.sql SELECT DBMS_METADA... Read more

20 Feb 2018 - 1 minute read

Change default schema on Postgresql

#database #postgresql

In PostgreSQL, users can have many namespaces to resolve objects names. These are called schemas like in Oracle, and can be altered through the search_path variable. Here’s how to check current search path: SHOW search_path; /* Result search_path ------------------ "$user", public */ Usually it defaults to the username and public. So when yo... Read more

20 Jan 2018 - less than 1 minute read

Rename all tables in a Postgresql schema

#database #postgresql

After a conversion usually done through AWS Database Migration Service, you may have a Postgresql database converted with table names in uppercase. These tables needs renaming, because you can’t access it unless you specify the table name between quotes, like so. Select count(*) from schema."MY_TABLE_NAME"; Otherwise the table are not found, ... Read more

20 Dec 2017 - less than 1 minute read

PostgreSQL Survival Guide

#postgresql #database

PostgreSQL has been gaining a lot of popularity these days, so let’s have a look at the basics of administering a PostgreSQL database. We’ll cover the structure exploration, object manipulation and some user managment. Quick remainder of some mainly used PostgreSQL datatypes: Name Storage size Description ... Read more

20 Jan 2016 - 3 minute read

Check database uptime

#database #oracle #mysql

Sometimes you have to check when a database was started for the last time. To get the information, just log on to the database and use the following query to get the last startup time: On Oracle database SELECT to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') AS "DB Startup Time" FROM sys.v_$instance; On Mysql database SHOW GLOBAL STATUS LI... Read more

20 Mar 2015 - less than 1 minute read

Change the default temporary tablespace

#database #oracle #sql

When your temporary tablespace gets full and you can’t resize it, you may consider switching ti a new ont to reclaim disk space. Changing the default temporary tablespace will allows you to discard the old one entirely, and reduce the total database size. In the following example, Temp1 is the current temporary tablespace, that we want to switch... Read more

20 Feb 2015 - 1 minute read

Oracle sqlplus silence output

#oracle #sqlplus #database

Use the following code on your scheduled Oracle scripts to silence terminal output except your data. SET autocommit off; SET echo off; SET feedback off; SET head off; SET heading off; SET linesize 0; SET newpage none; SET newpage none; SET pagesize 0; SET sqlprompt ''; SET sqlnumber off; SET sqlbl off; SET trim... Read more

20 Jul 2014 - less than 1 minute read

MySQL transaction isolation

#database #mysql #transaction

You can get session-level and global transaction isolation levels using these commands : SELECT @@global.tx_isolation; SELECT @@tx_isolation; SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE} -- Prepare session SET autocommit =... Read more

20 Jun 2014 - less than 1 minute read

How to prepare a DML list script

#database #oracle #script

Sometimes, you need to execute a lot of DML instructions on a database. This might look trivial but there are some very important elements to consider before running this kind of script on a production environment. Because depending on how many lines are you going to modify between commits, and the amount of queries that will run during the scr... Read more

20 May 2014 - 1 minute read

Resolving the “ERROR 126 Incorrect key file” error

#database #mysql

The Incorrect key file error: ERROR 126 (HY000) at line 3: Incorrect key file for table '/var/tmp/#sql3f5_1b6c4e_1.MYI'; try to repair it This error probably means that you ran out of disk space on /var/tmp while MySQL was trying to create a temporary table to resolve a complex query. The generation of temporary tables can be caused by derive... Read more

20 Mar 2014 - 1 minute read

Data export/import on MySQL

#database #mysql #import #export

MySQL provides you with a lot of flexibility when it comes to import or export data between databases. Let’s examine a few possibilities to export/import data: To export a whole database The following command will export a whole MySQL database on a sql file: mysqldump -u user DB_NAME > /tmp/file.sql To export a single table The followin... Read more

20 Feb 2014 - less than 1 minute read

Check all tables on MySQL

#database #mysql

Data corruption is every DBA’s worst nightmare and can happen anytime. Use this command to check and repair all tables on a MySQL database : mysqlcheck -u root -p --auto-repair --check --optimize --all-databases Read more

20 Jan 2014 - less than 1 minute read