# [26ai] Oracle Temporary Tablespace Groups

*If you prefer to read in Spanish* [***Spanish version***](https://dbaenlasombra.com/26ai-oracle-temporary-tablespace-groups)*.*

Today, we’re going to talk about **Temporary Table**[**space Groups.**](https://dbaenlasombra.com/oci-proxy-pdb?showSharer=true)

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
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
 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
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
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
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 :)