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.

Drop Temporary Tablespace Hangs With “enq: TS – contention”

Background process SMON is using the temporary tablespace to be dropped with “enq: TS – contention” wait event.

After created a new temporary tablespace TEMPNEW as database default temporary tablespace, then try to drop the old temporary tablespace “TEMP”, it hangs up forever.

SQL> drop tablespace temp including contents and datafiles;

Check session wait event “enq: TS – contention” against TEMP tablespace.

SQL> select SID,EVENT,P2TEXT,P2,SECONDS_IN_WAIT,WAIT_CLASS 
       from v$session_wait 
      where sid=3413;

SID   EVENT                P2TEXT        P2  SECONDS_IN_WAIT WAIT_CLASS
----- -------------------- ------------- --- -------------- ----------
 3413 enq: TS - contention tablespace ID  2  973             Other

SQL> select TS#,name from v$tablespace where ts#=2;

TS#         NAME
---------- -----------
 2         TEMP

Check who is still using temporary tablespace TEMP.

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

INST_ID     USERNAME   TABLESPACE  CONTENTS  SQL_ID       BLOCKS
---------- ---------   ----------- --------- ------------ ----------
 1         USER1       TEMP        TEMPORARY 4xhb02sm7sgqk 128
 1         USER2       TEMP        TEMPORARY 8r74hjykx88y7 128
 1         USER1       TEMP        TEMPORARY 4xhb02sm7sgqk 128
 1         USER1       TEMP        TEMPORARY 8r74hjykx88y7 128
 1         USER2       TEMP        TEMPORARY cjjt4dw97qc1n 128
 1         USER2       TEMP        TEMPORARY 8r74hjykx88y7 128

6 rows selected.

Those user process are waiting for TS enqueue which is held by SMON.

SQL> SELECT SID,ID1,ID2,LMODE,REQUEST FROM V$LOCK WHERE TYPE='TS';

SID         ID1       ID2        LMODE       REQUEST
---------- ---------- ---------- ---------- ----------
 3413      2          1          0           6
 3413      2          0          6           0
 4261      7          2          3           0

SQL> SELECT PROGRAM FROM V$SESSION WHERE SID in (3413,4261);

PROGRAM
-----------------------------
sqlplus@ractest1 (TNS V1-V3)
oracle@ractest1 (SMON)

SOLUTION

  1. Bounce the database.
  2. then drop the temporary tablespace successfully.