Change the default temporary tablespace

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 for the Temp2 tablespace. Here’s how to create the second temporary tablespace and perform the switch:

Create temporary teblespace Temp2 tempfile '/path/to/temp201.dbf'
size 10M autoextend on next 1M;

Alter database default temporary tablespace TEMP2;

This will change the new default temporary tablespace for the database to Temp2. For the users who have already been explicitly assigned the tablespcae Temp1 , you would need to change that using the following command:

Select 'alter user '||USERNAME||' default temporary tablespace Temp2;'
From dba_users where temporary_tablespace = 'TEMP1';

Also, you can check which temporary tablespace is the default one by checking the database_properties dictionary view:

select property_name, property_value
from database_properties
where property_name like '%TEMP%';

 PROPERTY_NAME            PROPERTY_VALUE
 ------------------------ --------------------------------
DEFAULT_TEMP_TABLESPACE   TEMP2

So now, we can completely discard the old tablespace.

Tags:

Rename a Postgresql database

#database #postgresql

Here's the procedure to rename a PostgreSQL database: 1. Disconnect from the database that you want to rename and connect to a different database. 2. Check and terminate all active connections to the database that you want to rename. 3. Use the `ALTER DATABASE` statement to rename the database to the new one. Let’s take a look at an example of... 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: ```sql -- 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_databas... 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

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: ```sql set head off set pages 0 set long 9999999 spool user_script.sql SELECT DBMS... Read more

20 Feb 2018 - 1 minute read

Restart a hung Oracle database

#oracle

On rare occasions, mostly during a high server load peak or a process failure, an Oracle instance may not accept any connection. Either from regular users as well as SYSDBA. This situation is called a **hung database** and must be quickly resolved as the database isn't accessible for your users anymore. As you cannot connect to the hung database... Read more

20 May 2017 - 1 minute read

Enable parallel DML

#oracle #sql

Effectively using parallel DML can speed up data manipulation. By the way, DML stands for (Data Manipulation Language), and refers to Insert, Update and Delete which plays at the row level. DDL (Data Definition Language) refers to Create, Alter or Drop statements and is used to change the structure of database objects. Using parallel DML is us... Read more

20 Jan 2015 - less than 1 minute read