[26ai] Oracle Temporary Tablespace Groups

If you prefer to read in Spanish Spanish version.

Today, we’re going to talk about Temporary Tablespace Groups.

Even though It’s not a feature in 26ai, we’re going to set it up.

First of all, this type of tablespace allows users , when performing operations that need a large amount of temporary space, such as sorting, Oracle can distribute the workload across multiple temporary tablespaces, thereby avoiding saturation when relying on a single temporary tablespace.

In this lab, we will use the following:

• Source: 26ai on VM DB System in Oracle Cloud (test).

So, let's get down to business!

Step 1: Create the PDB TEST on Source and check which temporary tablespace is assigned by default:

SQL> SHOW PDBS

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 TEST                           READ WRITE NO

SQL> Select Property_Name, Property_Value 
     From database_properties 
     Where property_name = 'DEFAULT_TEMP_TABLESPACE';

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

Step 2: Let’s define a new temporary tablespace group with several temporary tablespaces. Oracle doesn’t provide a statement in order to create a tablespace group, we need to speficy it when creating the tablespace.

 SQL> 
 CREATE TEMPORARY TABLESPACE GRP_TMP_TAB_1 TEMPFILE  SIZE 50M TABLESPACE GROUP GRP_TMP;
  Tablespace created.
SQL> 
 CREATE TEMPORARY TABLESPACE GRP_TMP_TAB_2 TEMPFILE  SIZE 50M TABLESPACE GROUP GRP_TMP;
  Tablespace created.

We can chech the new temporary tablespace group in DBA_TABLESPACE_GROUPS:

SQL> Select * From DBA_TABLESPACE_GROUPS ;

GROUP_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
GRP_TMP                        GRP_TMP_TAB_1
GRP_TMP                        GRP_TMP_TAB_2

In case we already have a temporary tablespace, or if we have created a new one without assigning it to a group, we can move it as follows:

SQL> CREATE TEMPORARY TABLESPACE GRP_TMP_TAB_3 TEMPFILE  SIZE 50M ;

Tablespace created.

SQL> ALTER TABLESPACE GRP_TMP_TAB_3 TABLESPACE GROUP GRP_TMP;

Tablespace altered.

SQL>  Select * From DBA_TABLESPACE_GROUPS ;

GROUP_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
GRP_TMP                        GRP_TMP_TAB_1
GRP_TMP                        GRP_TMP_TAB_2
GRP_TMP                        GRP_TMP_TAB_3

Step 3: Lastly, Let’s set our temporary tablespace group GRP_TMP as the default temporary tablespace.

SQL>  ALTER DATABASE DEFAULT TEMPORARY TABLESPACE GRP_TMP;

Database altered.

SQL> 
   Select Property_Name, Property_Value 
   From database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME                                      PROPERTY_VALUE
-------------------------------------------------- ----------------------------------------------------------------------------------------------------
DEFAULT_TEMP_TABLESPACE                            GRP_TMP

When a temporary tablespace group is set as the default temporary tablespace, none of the temporary tablespace within the group can be dropped. If you need to drop one of them, you must first assign another temporary tablespace as the default..

Looking forward to seeing you in the next article :)