Oracle Custom Procedure for defragmentation Partition tables and their associated index and move non partitioned table

create or replace procedure sp_process_move_data as
lv_tablespace varchar2(100) := '<tablespace_name>';
lv_objects_owner varchar2(100) := '<schema_name>';
lv_sql varchar2(100);

type lr_user_segments is record ( segment_name user_segments.segment_name%type,
segment_type user_segments.segment_type%type,
segsize number,
partition_number number);

type lt_user_segments is table of lr_user_segments index by binary_integer;
type lt_user_tab_partitions is table of user_tab_partitions%rowtype index by binary_integer;
type lt_user_indexes is table of user_indexes%rowtype index by binary_integer;
type lt_user_ind_partitions is table of user_ind_partitions%rowtype index by binary_integer;

la_user_tab_partitions lt_user_tab_partitions;
la_user_indexes lt_user_indexes;
la_user_ind_partitions lt_user_ind_partitions;
la_user_segments lt_user_segments;

procedure log_msg(pv_msg in varchar2) is
lv_ind number;
begin
execute immediate 'select nvl(max(sr_no),0)+1 from process_log'
into lv_ind;

execute immediate 'insert into process_log (sr_no,process_msg) values(:sr_no,:msg)' using lv_ind,pv_msg;
commit;
exception
when others then
log_msg(sqlerrm);
end log_msg;

begin

begin
execute immediate 'drop table process_log';
exception
when others then
null;
end;

begin
execute immediate 'create table process_log (sr_no number,process_msg varchar2(4000 char))';
exception
when others then
null;
end;

--- selecting segment name and size in table space.Also checks whether the segment is partitioned or not by using partition number
select segment_name,
segment_type,
sum(bytes) segsize,
count(*) partition_number
bulk collect into la_user_segments
from user_segments
where segment_type in ('TABLE PARTITION','TABLE')
and tablespace_name = lv_tablespace
and segment_name not like 'BIN$%'
--and segment_name in('STG_CONTACTINFO','CT_SUBSCRIBERS') ----use this condition for testing ---
group by segment_name, segment_type
order by segsize asc;


for iSegments in 1..la_user_segments.count loop
----if condition bifurcate the code for partitioned or non partitioned object
if la_user_segments(iSegments).partition_number >= 1  and la_user_segments(iSegments).segment_type ='TABLE PARTITION' then

-- selecting respective partitions for a table
select *
bulk collect into la_user_tab_partitions
from user_tab_partitions
where table_name = la_user_segments(iSegments).segment_name;

for iTabPart in 1..la_user_tab_partitions.count loop
---moving the partitions in loop of a table
begin
lv_sql:= 'alter table '||la_user_tab_partitions(iTabPart).table_name;
lv_sql := lv_sql||' move partition '||la_user_tab_partitions(iTabPart).partition_name;
lv_sql := lv_sql||' tablespace '||lv_tablespace;

execute immediate lv_sql;
log_msg('Moved Table:'||la_user_tab_partitions(iTabPart).table_name ||' Partition:'||la_user_tab_partitions(iTabPart).partition_name);
exception
when others then
log_msg('Moved Table:'||la_user_tab_partitions(iTabPart).table_name ||' Partition:'||la_user_tab_partitions(iTabPart).partition_name||'.Error=> '||sqlerrm);
goto end_of_code;
end;
end loop;
---- else of if condition bifurcate the code for partitioned or non partitioned object
else
---moving entire non partitioned table
-- for rec2 in c2(rec.segment_name) loop

--execute immediate 'analyze table '||la_user_segments(iSegments).segment_name||' compute statistics';
begin
execute immediate 'alter table ' || la_user_segments(iSegments).segment_name ||' move tablespace ' || lv_tablespace;
log_msg('Moved Table:'||la_user_segments(iSegments).segment_name);
exception
when others then
log_msg('Moved Table:'||la_user_segments(iSegments).segment_name||';Error=> '||sqlerrm);
goto end_of_code;
end;
-- end loop;
---if condition bifurcates the code for the partitioned index of a NON partitioned table
--- a non partion table may have partitioned index
end if;

--selecting respective indexes for a table
select *
bulk collect into la_user_indexes
from user_indexes
where table_name = la_user_segments(iSegments).segment_name;

for IUsrIdx in 1..la_user_indexes.count loop
---if condition bifurcates the code for the partitioned index of a partitioned table
if la_user_indexes(IUsrIdx).partitioned = 'YES' then
-- selecting index partitions for an index
select *
bulk collect into la_user_ind_partitions
from user_ind_partitions
where index_name = la_user_indexes(IUsrIdx).index_name;

for iUsrIdxPart in 1..la_user_ind_partitions.count loop
begin
execute immediate 'alter index '||la_user_indexes(iUsrIdx).index_name||' rebuild partition '||la_user_ind_partitions(iUsrIdxPart).partition_name||' online';
log_msg('Rebuild Index:'||la_user_indexes(iUsrIdx).index_name ||' Partition:'||la_user_ind_partitions(iUsrIdxPart).partition_name);
exception
when others then
log_msg('Rebuild Index:'||la_user_indexes(iUsrIdx).index_name ||' Partition:'||la_user_ind_partitions(iUsrIdxPart).partition_name||'.Error=> '||sqlerrm);
goto end_of_code;
end;
end loop;
else
begin
execute immediate 'alter index ' || la_user_indexes(iUsrIdx).index_name ||' rebuild online';
log_msg('rebuild Index:'||la_user_indexes(iUsrIdx).index_name);
exception
when others then
log_msg('rebuild Index:'||la_user_indexes(iUsrIdx).index_name||'.Error => '||sqlerrm);
goto end_of_code;
end;
end if;
end loop;
----gathering statatistics for the prtioned table at partition level and for NON partioned table
----bifurcation of code for partioned and non partitioned object for the statistics gathered.
if la_user_segments(iSegments).partition_number > 1 then

select *
bulk collect into la_user_tab_partitions
from user_tab_partitions
where table_name = la_user_segments(iSegments).partition_number;

for iUsrTabPart in 1..la_user_tab_partitions.count loop
begin
DBMS_STATS.GATHER_TABLE_STATS( lv_objects_owner,
la_user_segments(iSegments).segment_name,
partname => la_user_tab_partitions(iUsrTabPart).partition_name,
cascade => true,
granularity => 'PARTITION');
log_msg('Stats gatherd Table:'||la_user_segments(iSegments).segment_name ||'Patition:'||la_user_tab_partitions(iUsrTabPart).partition_name);
exception
when others then
log_msg('Stats gatherd Table:'||la_user_segments(iSegments).segment_name ||'Patition:'||la_user_tab_partitions(iUsrTabPart).partition_name||'.Error=> '||sqlerrm);
goto end_of_code;
end;
end loop;
else
begin
DBMS_STATS.GATHER_TABLE_STATS( lv_objects_owner,
la_user_segments(iSegments).segment_name,
cascade => true);
log_msg('Stats gatherd Table:'||la_user_segments(iSegments).segment_name);
exception
when others then
log_msg('Stats gatherd Table:'||la_user_segments(iSegments).segment_name||'.Error=> '||sqlerrm);
goto end_of_code;
end;
end if;

end loop;
<<end_of_code>>
null;
exception
when others then
log_msg('Main => '||sqlerrm);
end;
/