Temporary Tablespace Group

Temporary Tablespace and Temporary Tablespace Group

Create a temporary tablespace, and implicitly add it to a temporary tablespace group.

SQL>CREATE BIGFILE TEMPORARY TABLESPACE TEMP01 TEMPFILE '+DATA1' 
    SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 32G EXTENT MANAGEMENT 
    LOCAL UNIFORM SIZE 1M TABLESPACE GROUP TEMP_GRP;

Add a temporary tablespace to the temporary tablespace group.

SQL>ALTER TABLESPACE temp02 TABLESPACE GROUP TEMP_GRP;

Remove a temporary tablespace from a temporary tablespace group.

SQL>ALTER TABLESPACE temp03 TABLESPACE GROUP '';

Assign a user to a default temporary tablespace group.

SQL>ALTER USER TESTUSER TEMPORARY TABLESPACE TEMP_GRP;

Assign database a  default temporary tablespace group.

SQL>ALTER PLUGGABLE DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_GRP;

Pluggable database altered.

Check temporary tablespace and its files.

SQL> select tablespace_name,FILE_ID,file_name,AUTOEXTENSIBLE,
     MAXBYTES/1024/1024/1024, INCREMENT_BY,USER_BYTES/1024/1024/1024 
     from dba_temp_files;

Check temporary tablespace and group.

SQL>select * from DBA_TABLESPACE_GROUPS;

Check who is using temporary tablespace.

SQL> select INST_ID,USERNAME,TABLESPACE,CONTENTS,SQL_ID,BLOCKS 
     from gv$SORT_USAGE;

Drop temporary tablespace.

SQL> drop tablespace temp02 including contents and datafiles;
drop tablespace temp02 including contents and datafiles
*
ERROR at line 1:
ORA-10921: Cannot drop tablespace belonging to default temporary 
           tablespace group

Drop  temporary tablespace group.

After remove all the temporary tablespace from temporary tablespace group, 
the temporary tablespace group will be dropped from database automatically.

ORA-10921: Cannot drop tablespace belonging to default temporary tablespace group

Before dropping a temporary tablespace, make sure it is not a default temporary tablespace.

Trying to drop a temporary tablespace which belongs to a default temporary tablespace group.

SQL> select * from DBA_TABLESPACE_GROUPS;

GROUP_NAME          TABLESPACE_NAME
------------------- ------------------------------
TEMP_OLTP           TEMP
TEMP_OLTP           TEMP2
TEMP_OLTP           TEMP3

SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-10921: Cannot drop tablespace belonging to default temporary tablespace group


SQL> DROP TABLESPACE temp3 INCLUDING CONTENTS AND DATAFILES;
 DROP TABLESPACE temp3 INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-10921: Cannot drop tablespace belonging to default temporary tablespace group

Change default temporary tablespace from  temporary tablespace group TEMP_OLTP to a temporary tablespace TEMP.:

SQL> alter pluggable database default temporary tablespace temp;

Pluggable database altered.

Now drop temporary tablespaces:

SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> DROP TABLESPACE temp3 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> select * from DBA_TABLESPACE_GROUPS;

GROUP_NAME      TABLESPACE_NAME
--------------- -----------------
TEMP_OLTP        TEMPA

Make temporary tablespace group as default again:

SQL> alter pluggable database default temporary tablespace TEMP_OLTP;

Pluggable database altered.