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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.