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;
