When use Temporary Tablespace Groups

I was managing a database that had a temporary tablespace which was having problems to hold the results of a sort. Then I solved this problem by creating a temporary tablespace group. And according to Oracle Corporation, “… enables parallel execution servers in a single parallel operation to use multiple temporary tablespaces.”. So how can I do that?

If you are managing a multitenant database you must have in mind what problem is occurring in one of your PDB or the CDB.
Let’s see if we are going to create this temporary tablespace group in the PDB. The first thing you have to do is to switch to the current PDB:

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDBTECH01			  READ WRITE NO
SQL>alter session set container=PDBTECH01;

Session altered.

Check the current temporary tablespace:

SQL>desc DATABASE_PROPERTIES 
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 PROPERTY_NAME				   NOT NULL VARCHAR2(128)
 PROPERTY_VALUE 				    VARCHAR2(4000)
 DESCRIPTION					    VARCHAR2(4000)

SQL>SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE
   PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';  2  

PROPERTY_NAME
--------------------------------------------------------------------------------
PROPERTY_VALUE
--------------------------------------------------------------------------------
DEFAULT_TEMP_TABLESPACE
TEMP

SQL> show con_name

CON_NAME
------------------------------
PDBTECH01

Create the tablespaces that it will be part of the group:

SQL>create temporary tablespace tmp01 tempfile '/u01/app/oracle/oradata/CDBTECH/PDBTECH01/temp01new.dbf'  size 10m autoextend on;

Tablespace created.



SQL>  create temporary tablespace tmp02 tempfile '/u01/app/oracle/oradata/CDBTECH/PDBTECH01/temp02new.dbf'  size 10m autoextend on tablespace group GRUPO;

Tablespace created.

SQL> alter tablespace tmp02 tablespace group GRUPO;

Tablespace altered.

Checking the existing groups:

SQL>desc dba_tablespace_groups
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 GROUP_NAME				   NOT NULL VARCHAR2(30)
 TABLESPACE_NAME			   NOT NULL VARCHAR2(30)

SQL>
SQL>select group_name, tablespace_name from dba_tablespace_groups;

GROUP_NAME		       TABLESPACE_NAME
------------------------------ ------------------------------
GRUPO			       TMP01
GRUPO			       TMP02

Defining the temporary tablespace group called GRUPO as the default temporary tablespace for this PDB:

SQL>  ALTER DATABASE DEFAULT TEMPORARY TABLESPACE GRUPO; 

Database altered.

Checking the current default temporary tablespace of this PDB:

SQL>SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE
   PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';  2  

PROPERTY_NAME
--------------------------------------------------------------------------------
PROPERTY_VALUE
--------------------------------------------------------------------------------
DEFAULT_TEMP_TABLESPACE
GRUPO

SQL> SHOW CON_NAME

CON_NAME
------------------------------
PDBTECH01

You can also check that the temporary tablespace in the CDB remains unchanged:

SQL> conn /as sysdba
Connected.
SQL>  SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE
   PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';  2  

PROPERTY_NAME
--------------------------------------------------------------------------------
PROPERTY_VALUE
--------------------------------------------------------------------------------
DEFAULT_TEMP_TABLESPACE
TEMP

brunors

Hi! I am Bruno, a Brazilian born and bred, and I am also a naturalized Swedish citizen. I am a former Oracle ACE and, to keep up with academic research, I am a Computer Scientist with an MSc in Data Science and another MSc in Software Engineering. I have over ten years of experience working with companies such as IBM, Epico Tech, and Playtech across three different countries (Brazil, Hungary, and Sweden), and I have joined projects remotely in many others. I am super excited to share my interests in Databases, Cybersecurity, Cloud, Data Science, Data Engineering, Big Data, AI, Programming, Software Engineering, and data in general.
(Continue reading)