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.
Make DBA Life Easy 