SET PAGES 999 LINESIZE 100 TRIMS ON ECHO OFF VERIFY OFF FEEDBACK OFF
SET SERVEROUTPUT ON
TTITLE OFF
CLEAR COLUMN
SPOOL shrink_ts.sql
PROMPT
declare
wtablespace varchar2 (30) := '&Tablespace_name_in_caps';
/* database block size (in K) */
wblocksize_kb number := 0;
/* datafile max level */
cursor cdf is
select F.file_name,
L.max_level
from dba_data_files F,
(select E.file_id,
(E.block_id + E.blocks-1) as max_level
from dba_extents E,
(select file_id, Max(block_id) as max_block_id
from dba_extents
group by file_id) MB
where E.file_id = MB.file_id
and E.block_id = MB.max_block_id) L,
(select file_id,
Round(Sum(bytes)/1024,0) as total_size
from dba_data_files
group by file_id) TS,
(select file_id,
Round((Sum(bytes)/1024),0) as free_size
from dba_free_space
group by file_id) FS
where F.file_id = L.file_id
and F.file_id = TS.file_id
and F.file_id = FS.file_id
and F.tablespace_name = Upper(wtablespace)
and TS.total_size <> L.max_level;
begin
dbms_output.enable(100000);
/* database block size (in bytes) */
select value/1024 into wblocksize_kb
from v$parameter
where name = 'db_block_size';
for rdf in cdf loop
dbms_output.put_line( 'alter database datafile ''' ||
rdf.file_name || ''' resize ' ||
rdf.max_level*wblocksize_kb || 'K;' );
end loop;
end;
/
SPOOL OFF
CLEAR COLUMN
SET SERVEROUTPUT OFF
SET PAGES 24 LINESIZE 80 TRIMS ON ECHO OFF VERIFY ON FEEDBACK ON
PROMPT
PROMPT Output saved at shrink_ts.sql
PROMPT
