OracleDB‎ > ‎Tablespace‎ > ‎

Temp Tablespace

lookup the name of your default temporary tablespace:
 SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

Usage of the temp tablespace
 select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;

Lookup files in the temporary tablespaces
 select file_name, tablespace_name from dba_temp_files;

Add file to temporary tablespace
 ALTER TABLESPACE temp ADD TEMPFILE '<full path and file name>' SIZE 500M;

reuse an existing temp file
 ALTER TABLESPACE temp ADD TEMPFILE '<full path and file name>' reuse;

resize a temp file
 alter database tempfile '<temp file>' resize 500M;

MOVING TEMP FILES WITHOUT RESTARTING ORACLE


One can’t move the temp tablespace during mount stage like other datafiles using “ALTER DATABASE RENAME FILE..” so a workaround this issue is to create a new temp tablespace.

 SQL> drop tablespace temp;
 drop tablespace temp
 *
 ERROR at line 1:
 ORA-12906: cannot drop default temporary tablespace

– create a new temp tablespace
 SQL> CREATE TEMPORARY TABLESPACE TEMP2
 TEMPFILE ‘/u01/oradata/TESTDB/temp2_01.dbf’ SIZE 1000M AUTOEXTEND ON NEXT 100M MAXSIZE 5000M
 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K
 SEGMENT SPACE MANAGEMENT MANUAL
 /


– change default temporary tablespace
 SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;


– drop old temp tablespace
 SQL> drop tablespace temp including contents and datafiles;


– recreate the temp with new file location
 SQL> CREATE TEMPORARY TABLESPACE TEMP
 TEMPFILE ‘/u03/oradata/TESTDB/temp_01.dbf’ SIZE 1000M AUTOEXTEND ON NEXT 100M MAXSIZE 5000M
 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K
 SEGMENT SPACE MANAGEMENT MANUAL
 /

– make the temp default again
 SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;


– drop temp2 tablespace
 SQL> drop tablespace temp2 including contents and datafiles;

Size and maxsize of temp files
 select file_name, bytes/1024/1024 mb, maxbytes/1024/1024 max_mb from dba_temp_files order by file_name;

Temp tablespace usage by session:
 SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
 S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
 COUNT(*) sort_ops
 FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
 WHERE T.session_addr = S.saddr
 AND S.paddr = P.addr
 AND T.tablespace = TBS.tablespace_name
 GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
 S.program, TBS.block_size, T.tablespace
 ORDER BY sid_serial;

lookup queries that are using the temp tablespace
 SELECT
        v$session.sid,
        v$session.serial#,
        v$session.username,
        v$sort_usage.blocks * dba_tablespaces.block_size / 1024 / 1024 AS mb_used,
        v$sort_usage.tablespace,
        v$sort_usage.sqladdr AS address,
        v$sqlarea.hash_value,
        v$sqlarea.sql_text
 FROM v$session
 INNER JOIN v$sort_usage ON v$session.saddr = v$sort_usage.session_addr
 INNER JOIN v$sqlarea ON v$sort_usage.sql_id = v$sqlarea.sql_id
 INNER JOIN dba_tablespaces ON v$sort_usage.tablespace = dba_tablespaces.tablespace_name
 ORDER BY mb_used DESC;

Comments