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!!!