col property_value format a30
col description format a55
SELECT *
FROM database_properties
WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';
Create the new temp tablespace (if not done yet).SQL> col property_value format a30
col description format a55
SELECT *
FROM database_properties
WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';SQL> SQL> 2 3
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ ------------------------------ -------------------------------------------------------
DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace
SQL>
CREATE TEMPORARY TABLESPACE <tablespace_name>
TEMPFILE '<path_and_file_name>'
SIZE <integer><K | M | G | T>
AUTOEXTEND <ON | OFF>
TABLESPACE GROUP <group_name>
EXTENT MANAGEMENT LOCAL UNIFORM SIZE <extent_size>;
SQL> CREATE TEMPORARY TABLESPACE "TEMPNEW" TEMPFILE '/u01/oradata/ORCL10G/tempnew02.dbf' SIZE 20M AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K;
Switch to the new temp tablespace.
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE <tablespace_name>;
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPNEW;
Wait until no more session is using the old temp tablespace.
To check who is currently using the temp tablespace,
When no more sessions found using the old temp tablespace, proceed on dropping it.--- TEMPORARY TABLESPACE - Who is Using Temp Tablespace
set lines 200
col sid_serial for a10
col username for a12
col osuser for a15
col spid for a6
col module for a20
col program for a20
col tablespace for a10
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 mb_used DESC;
SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;