Create Temporary Tablespace in Oracle and Make it Default

This article covers the steps to create a temporary tablespace in Oracle and make it the default temporary tablespace for the database.

Step-By-Step

This method works for both regular Oracle databases and multitenant databases. Each PDB and CDB has its own separate temporary tablespace. When creating a temporary tablespace for a PDB, make sure you are logged into the PDB where you want the temporary tablespace to reside.

I will add a temporary tablespace, TEMP_TBL, to the ORCLPDB PDB database for this example. I will make it the default temporary tablespace for the PDB and then drop the original TEMP temporary tablespace.

Creating a temporary tablespace is almost the same as creating a regular tablespace except for the two keywords, temporary and tempfile. These two keywords are the only difference when creating a temporary tablespace.

Either log in directly to the PDB or log in to the container and then switch to the PDB.

From here, check the current default temporary tablespace.

set line 200
column file_name format a75	
column tablespace_name format a30	 
select tablespace_name, file_name, bytes/1024/1024, maxbytes/1024/1024, autoextensible
from dba_temp_files
order by 1, 2, 3 desc;

Create Temporary Tablespace Current Default Temporary Tablespace

Now, create the new temporary tablespace, make it the default for the PDB, and check your changes.

create temporary tablespace TEMP_TBL tempfile 'C:\ORACLE\ORCL\ORCLPDB\TEMP_TBL01.DBF' size 500M autoextend on maxsize unlimited;

alter database default temporary tablespace TEMP_TBL;

Create Temporary Tablespace Create New and Make it Default

Before dropping the old temporary tablespace, check if users are using the old default temporary tablespace. If users are still using the old temporary tablespace, you cannot drop it until they log out.

col username for a15;
col tablespace for a15
select a.sid, a.serial#, a.username, sum(b.blocks) * c.block_size/1024/1024 mb_user, b.tablespace
from v$session a, v$sort_usage b, dba_tablespaces c
where a.saddr = b.session_addr
and b.tablespace = c.tablespace_name
group by a.sid, a.serial#, a.username, c.block_size, b.tablespace;

Create Temporary Tablespace Check if Users Use Old Default Temporary Tablespace

Here, you can see that one user is still using the old temporary tablespace, and if we try to drop it, we receive the error below.

Create Temporary Tablespace Drop Error Due to User

With the user disconnected, we can drop the old temporary tablespace and confirm the new default temporary tablespace is TEMP_TBL.

Create Temporary Tablespace Drop Old Temporary Tablespace and Confirm New

Creating a new temporary tablespace is just as easy as creating a regular tablespace. Just remember to use ‘ temporary’ for the tablespace and ‘tempfile’ for the data file.

For questions, please contact us.