Oracle:Dynamically generate a script to shrink tablespace(s) to 70% occupied or to the highest watermark.

Contributed by : Nanie Asedillio

undef tbsnm
PROMPT
ACCEPT tbsnm PROMPT ‘Enter Value Tablespace (Return For all) : ‘

declare
cursor c1 is select tablespace_name
from dba_tablespaces
where tablespace_name not in (’SYSTEM’,'SYSAUX’,'UNDOTBS’,'UNDO’,'TOOLS’,'USERS’)
and contents=’PERMANENT’
and tablespace_name=nvl(upper(’&tbsnm’),tablespace_name)
union
select tablespace_name
from dba_segments
where tablespace_name not in (’SYSTEM’,'SYSAUX’,'UNDOTBS’,'UNDO’,'TOOLS’,'USERS’)
and tablespace_name=nvl(upper(’&&tbsnm’),tablespace_name);

/* cursor to check HWM for each datafile */
cursor c2 (tbsname2 varchar2) is
select F.file_name,
L.max_level,
TS.total_size
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/1024,0) as total_size
from dba_data_files
group by file_id) TS,
(select file_id,
Round((Sum(bytes)/1024/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 = tbsname2
and TS.total_size <> L.max_level;

/* cursor to check if tbspace is 70% used or not */
cursor c3 (tbsname3 varchar2) is
select kbytes_alloc allocsize,
kbytes_alloc-nvl(kbytes_free,0) usedmb,
((kbytes_alloc-nvl(kbytes_free,0))/kbytes_alloc)*100 pcused
from ( select sum(bytes)/1024/1024 Kbytes_free,
max(bytes)/1024/1024 largest,
tablespace_name
from sys.dba_free_space
group by tablespace_name ) fs,
( select sum(bytes)/1024/1024 Kbytes_alloc,
tablespace_name,
count(*) data_files
from sys.dba_data_files
group by tablespace_name )df
where fs.tablespace_name (+) = df.tablespace_name
and df.tablespace_name = tbsname3;

/* cursor to pick up datafiles that are not used yet */
cursor c4 (tbsname4 varchar2) is
select file_id,
file_name,
sum(bytes)/1024/1024 kbytes_alloc
from dba_data_files
where tablespace_name = tbsname4
and file_id not in (select file_id
from dba_extents
where tablespace_name=tbsname4)
group by file_id,file_name;

c3rec c3%rowtype;
tbspctfree number(5,2) := 0;
dbblksize number(7,7) := 0;
totspred number(15,2) := 0;
targetsize number(15,2) := 0;
newtarget number(15,2) := 0;
lasttotspred number(15,2) := 0;
spacereduz number(15,2) := 0;

begin
for tbsp in c1 loop

/* open cursor to check if tablespace is 70% used */
open c3(tbsp.tablespace_name);
fetch c3 into c3rec;
targetsize := ceil(c3rec.allocsize-(c3rec.usedmb/0.7));
dbms_output.enable(1000000);
dbms_output.put_line(’ Tablespace ‘||tbsp.tablespace_name||’ is ‘||round(c3rec.pcused,2)||’% used’);

if targetsize > 0 then
dbms_output.put_line(’Reduce it by ‘||targetsize||’ to obtain >=70% usage requirement’||chr(10));
else
dbms_output.put_line(’No need to reduce it’||chr(10));

end if;

if c3rec.pcused < 70 then

/* get db_block_size */
select value/1024/1024 into dbblksize
from v$parameter
where name = ‘db_block_size’;

totspred := 0; /* reset the value to 0 */

for dtfreeup in c4(tbsp.tablespace_name) loop
spacereduz := ceil(dtfreeup.kbytes_alloc-dtfreeup.kbytes_alloc+1);
totspred := totspred+(dtfreeup.kbytes_alloc-1);

/* dbms_output.put_line(’spacereduz b4 if is: ‘||spacereduz||’ totspred is ‘||totspred); */

if totspred >= targetsize then
/* dbms_output.put_line(’totspred >= targetsize’); */
newtarget := ceil(dtfreeup.kbytes_alloc-(targetsize-lasttotspred));
dbms_output.put_line(’alter database datafile ”’||dtfreeup.file_name||”’ resize ‘||newtarget||’M; —allocated is ‘||dtfreeup.kbytes_alloc);
totspred := lasttotspred+(dtfreeup.kbytes_alloc-newtarget);
/* dbms_output.put_line(’lastotspred+newtarget is ‘||totspred); */

else
dbms_output.put_line(’alter database datafile ”’||dtfreeup.file_name||”’ resize ‘||spacereduz||’M; —allocated is ‘||dtfreeup.kbytes_alloc);
lasttotspred := totspred;
end if;
/* dbms_output.put_line(’spacereduz b4 if is: ‘||spacereduz||’totspred is ‘||totspred); */
exit when (totspred >= targetsize);

end loop; /* end loop for c4 cursor */

if totspred < targetsize then /* if targetsize hasn’t been reached, then try to reduce space on datafiles with contents */
for dtreduce in c2(tbsp.tablespace_name) loop

totspred := totspred+((dtreduce.total_size-ceil(dbblksize*dtreduce.max_level))); /* calculate how much space is removed from the target */
/* dbms_output.put_line(’totspred b4 if: ‘||totspred); */

if totspred >= targetsize then

newtarget := dtreduce.total_size-(targetsize-lasttotspred);
/* dbms_output.put_line(’new target size is ‘||newtarget); */
totspred := lasttotspred+(dtreduce.total_size-newtarget);
/* dbms_output.put_line(’totspred + newtarget: ‘||totspred); */
dbms_output.put_line(’alter database datafile ”’||dtreduce.file_name||”’ resize ‘||ceil(newtarget)||’M; —allocated is ‘||dtreduce.total_size);

else

dbms_output.put_line(’alter database datafile ”’||dtreduce.file_name||”’ resize ‘||ceil(dbblksize*dtreduce.max_level)||’M; —allocated is ‘||dtreduce.total_size);
/* store last totspred value for reference later */
lasttotspred := totspred;

end if;

/* dbms_output.put_line(’totspred after if: ‘||totspred); */
exit when (totspred >= targetsize);

end loop; /* c2 loop */
end if;

dbms_output.put_line(chr(10)||’Total reduced space is ‘||totspred||’M out of the target size w/c is ‘||targetsize||’M');
dbms_output.put_line(’IF total reduced space is lower than the target usage then it is due to the High-Water mark value’);
dbms_output.put_line(’Tablespace reorganization for ‘||tbsp.tablespace_name||’ might be required’||chr(10));

end if;

close c3;

end loop; /* tbsp loop */

end;
/