Shrink TEMP tablespace

SQL> select
 2 ( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
 3 ( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
 4 ( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
 5 ( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"
 6 from dual;

Size in GB
----------
88.6112671

TABLESPACE_NAME SIZEMB FREEMB
------------------------------ ---------- ----------
SYSAUX 3610 175.38
UNDOTBS1 2025 2000.75
USERS 51336.5 9275.88
SYSTEM 810 9.81
TEMP 32767

SQL> SELECT * FROM dba_temp_free_space;

TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------------ --------------- --------------- ----------
TEMP 3.4359E+10 3.4359E+10 3.4356E+10

SQL> set timi on
SQL> ALTER TABLESPACE temp SHRINK SPACE KEEP 40M;

Tablespace altered.

Elapsed: 00:07:01.15
SQL> select
 2 ( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
 3 ( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
 4 ( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
 5 ( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"
 6 from dual;

Size in GB
----------
56.6513062

Komentáře