Hi ,
I am a newbie DBA trying to automate code for table reorg defragmentation activity , I have a master table (reorg_master) that contains details of all tables that is selected as candidate for reorg defragmentation.
further from the master table if there is any partitioned table is available , then all partition related information will be populated inside another table named as (reorg_master_part)
1. When i do reorg for non-partition tables alone my initial flag which is set to 0 will be updated to 1 making completion of table reorg , skipping it form upcoming loops.
2 - Here is where my problem lies
i am trying to set flag as 0 for all partitions during initial load and once all move operation is completed for partition each partition will be marked to flag 3 ,
once for a particular table all partitions are marked as 3 the master table has to be updated with flag value 1 .
Issue i am facing is , for table 1 and table 2 which contain 50 partitions each , once table 1 gets selected inside loop and it completes all 50 partitions as flag 3 it goes out of loop and starts table 2.
That makes sense but i want it to set flag in my master_reorg table as 1 once all respective table move operation completed and my master_reorg_part table entries marked as 3 1 , I am not in my workplace right now so couldn't post my exact code but i can give the logical flow below
Begin
--Reorg defragmentation code
update master_reorg_part set flag=3 where table_name=var.table_name and owner=var.owner;
commit;
for flag_val in (select count(*) v_flag,table_name,owner from reorg_master_part where flag=0 )
loop
if v_flag is not null
dbms_output('still processing');
else
update master_reorg set flag=1 where table_name=flag_val.table_name and owner=flag_val.owner;
commit;
end loop;
end loop;
end;
/
legend :
master_reorg table :
0- Reorg to be started
1- Reorg completed
master_reorg_part table :
0- Reorg to be started for partition
3- Reorg for partition completed
i have wrote sample code based on my logic , any help would be appreciated and ignore my syntax errors or typos
Will try to post my complete code if that helps