收缩表空间 shrink_ts.sql

sqlplus /nolog
conn / as sysdba

set linesize 120 pagesize 50000
select 'alter database datafile '||file_id||' autoextend on maxsize 8G;' from dba_data_files;

set linesize 120 pagesize 50000
select 'alter database datafile '||file_id||' autoextend on;' from dba_data_files where tablespace_name like '%UNDO%';

set linesize 120 pagesize 50000
select 'alter database tempfile '||file_id||' autoextend on;' from dba_temp_files;

set linesize 120 pagesize 50000
select 'alter database datafile '||file_id||' autoextend on maxsize 8G;' from dba_data_files
union
select 'alter database datafile '||file_id||' autoextend on ;' from dba_data_files where tablespace_name like '%UNDO%'
union
select 'alter database tempfile '||file_id||' autoextend on;' from dba_temp_files;

set linesize 120 pagesize 50000
select/*+ ordered use_hash(a,c) */
'alter database datafile '''||a.file_name||''' resize '
||round(a.filesize - (a.filesize - c.hwmsize-1) *0.99)||'M;'
from
(select file_id,file_name,round(bytes/1024/1024) filesize from dba_data_files) a,
(select file_id,round(max(block_id)*8/1024) HWMsize from dba_extents group by file_id) c
where a.file_id = c.file_id
and
a.filesize - c.hwmsize > 10;

set linesize 120 pagesize 50000
select/*+ ordered use_hash(a,c) */
'alter database datafile '||a.file_id||' resize ' ||round(a.filesize - (a.filesize - c.hwmsize-1) *0.90)||'M;'
from
(select file_id,file_name,tablespace_name,round(bytes/1024/1024) filesize from dba_data_files) a,
(select file_id,round(max(block_id)*8/1024) HWMsize from dba_extents group by file_id) c
where a.file_id = c.file_id
and
a.tablespace_name='&tablespace_name'
and
a.filesize - c.hwmsize > 100;

set linesize 120 pagesize 50000
select/*+ ordered use_hash(a,c) */
'alter database datafile '||a.file_id||' resize ' ||round(a.filesize - (a.filesize - c.hwmsize-1) *0.95)||'M;'
from
(select file_id,file_name,tablespace_name,round(bytes/1024/1024) filesize from dba_data_files) a,
(select file_id,round(max(block_id)*8/1024) HWMsize from dba_extents group by file_id) c
where a.file_id = c.file_id
and
a.tablespace_name='&tablespace_name'
and
a.filesize - c.hwmsize > 10;