Script - Reclaim Space

Segment Advisor - To reclaim Space 

declare 
schema_to_check is table of varchar2(30); 
check_schema schema_to_check := schema_to_check('MANZOOR'); 
type tbls is ref cursor return dba_tables%rowtype; 
tblss tbls; 
procedure get_segmnt_advice(tbln tbls, usr varchar2) is 
firq number; 
secq number; 
thrq number; 
forq number; 
fifq number; 
sixq number; 
objt_id number; 
shr_qry varchar2(100); 
msginf varchar2(100); 
allocated_spc varchar2(50); 
used_scp varchar2(50); 
reclaim_spc varchar2(50); 
recl varchar2(30); 
full_tbls dba_tables%rowtype; 
begin 
loop 
fetch tbln into full_tbls; 
exit when tbln%NOTFOUND; 
<>>
dbms_advisor.create_task
(
advisor_name => 'Segment Advisor',
task_name => 'Get_Segment_Advice',
task_desc => 'To_Reduce_HWM'
);
dbms_advisor.create_object
(
task_name => 'Get_Segment_Advice',
object_type => 'TABLE',
attr1 => usr,
attr2 => full_tbls.table_name,
attr3 => NULL,
attr4 => 'null',
attr5 => NULL,
object_id => objt_id
);
dbms_advisor.set_task_parameter
(
task_name => 'Get_Segment_Advice',
parameter => 'RECOMMEND_ALL',
value => 'TRUE'
);
dbms_advisor.execute_task
(
task_name => 'Get_Segment_Advice'
);
select more_info into msginf from dba_advisor_findings where task_name = 'Get_Segment_Advice';
firq := to_number(instr(msginf,':'));
secq := to_number(instr(msginf,':',firq+1));
thrq := to_number(instr(msginf,':',secq+1));
forq := to_number(instr(msginf,':',thrq+1));
fifq := to_number(instr(msginf,':',forq+1));
sixq := to_number(instr(msginf,':',fifq+1));
allocated_spc := substr(msginf,firq+1,secq-(firq+1));
used_scp := substr(msginf,thrq+1,forq-(thrq+1));
reclaim_spc := round(substr(msginf,fifq+1,sixq-(fifq+1))/1024/1024) ' MB';
recl := round(substr(msginf,fifq+1,sixq-(fifq+1))/1024/1024);
dbms_output.put_line ('Table : ' upper(full_tbls.table_name) ' Reclaimable Space : ' reclaim_spc );
if to_number(recl) > 0 then
dbms_output.put_line ('Space Exists to Reclaim -- Executing Commands to Reclaim Space.....');
dbms_output.put_line ('===================================================================');
dbms_output.put_line ('Space Reclaimed!!');
shr_qry := 'alter table ' usr '.' full_tbls.table_name ' enable row movement';
execute immediate shr_qry;
shr_qry := 'alter table ' usr '.' full_tbls.table_name ' shrink space cascade';
execute immediate shr_qry;
dbms_output.put_line ('After Shrink the table Reclaim space as below');
dbms_output.put_line ('=============================================');
dbms_advisor.delete_task('Get_Segment_Advice');
goto <>>;
end if;
dbms_advisor.delete_task('Get_Segment_Advice');
end loop;
exception
when others then
dbms_output.put_line (SQLCODE ': ' SQLERRM);
end get_segmnt_advice;
begin
for i in check_schema.first..check_schema.last loop
dbms_output.put_line('Reclaimable Space Details for Schema : ' upper(check_schema(i)));
dbms_output.put_line('==========================================================');
open tblss for
select * from dba_tables where owner = check_schema(i) and table_name not in (
select table_name from dba_external_tables where owner = check_schema(i));
get_segmnt_advice(tblss,check_schema(i));
close tblss;
end loop;
end;
/


Output:
======


sql> segmntadivsr.sql;

Reclaimable Space Details for Schema : MANZOOR
===============================================
Table : EMP Reclaimable Space : 0 MB
Table : DEPT Reclaimable Space : 0 MB
Table : SALARY Reclaimable Space : 20 MB
Space Exists to Reclaim -- Executing Commands to Reclaim Space...
=================================================================
Space Reclaimed!!!
After Shrink the table Reclaim Space as below:
==============================================
Table : SALARY Reclaimable Space : 0 MB
Table : BOOKS Reclaimbale Space : 0 MB
Table : COPYOFSAL Reclaimbale Space : 10 MB
Space Exists to Reclaim -- Executing Commands to Reclaim Space...
=================================================================
Space Reclaimed!!!
After Shrink the table Reclaim Space as below:
==============================================
Table : COPYOFSAL Reclaimbale Space : 0 MB

Completed!!!