SQL> ALTER TABLESPACE temp SHRINK TEMPFILE '/u01/oracle/oradata/temp01.dbf' KEEP 20M;
The KEEP
clause specifies the minimum size of the tablespace or tempfile.
If this is omitted, the database will shrink the tablespace or tempfile
to the smallest possible size.
SQL> ALTER TABLESPACE temp SHRINK SPACE;
SQL> ALTER DATABASE TEMPFILE ‘+DB1_DATA01/orcl/tempfile/temp.264.32801683′
RESIZE 200M;
Before 11G
1. Create new temporary tablespace with smaller size.
SQL> create temporary tablespace TEMP2
tempfile '/app/oradata/u01/temp01.dbf' size 100M
extent management local uniform size 128K;
SQL> alter database default temporary tablespace temp2;
SQL> drop tablespace temp including contents and datafiles;
f any users are using the temporary tablespace currently,
you would not be able to drop the tablespace.
So, let us find out wihich users are using the
temporary tablespace right now using bellow query:
SQL> SELECT b.tablespace,
ROUND(((b.blocks*p.value)/1024/1024),2) as "SIZE",
a.sid,
a.serial#,
a.username,
a.program
FROM sys.v_$session a,
sys.v_$sort_usage b,
sys.v_$parameter p
WHERE p.name = 'db_block_size'
AND a.saddr = b.session_addr
ORDER BY b.tablespace, b.blocks;
Then kill or wait until the above users finish their jobs, and then try to
drop the temporary tablespace again.
You can also see the usage of temporary tablespace using bellow query:
select TABLESPACE_NAME, BYTES_USED, BYTES_FREE
from V$TEMP_SPACE_HEADER;