Why rebuild indexes? If there are any orphaned keys, they will be removed by the autotask overnight. Or launch the process manually if for some reason you want to do it sooner.
we have primary indexes on table.. once drop sub partition .. we need to re build index am i correct..
No, you are not correct. Look at your 12.x new features, and you will see.
By the way, there is something wrong with your keyboard: it inserts ".." when you mean"."
could you please provide any link. that's very help full for me..
we are using oracle 12c. I have 300 + tables and will be going to create sub partition on monthly bases.. we need to drop data on every month because table should have only last 1 years data.
i am going to create package with below steps. could you please help me here by provide best your valuable inputs.
here is my flow.
1. will check is there any job is running.. if running .. will check every 10 mints until all jobs completed..
2. will disable all jobs
3. drop all sub partition 's
4. re build all indexes on all tables
5. enable jobs
Why do this with subpartitions when you haven’t even mentioned a partitioning scheme?
Checking for jobs running and disabling them is a fools game, just make sure your DDL waits patiently to get locks, once it’s got a lock on the table it will only be blocking some statements from running (for a short amount of time) they should also be handled fine unless you have jobs doing DDL too.
Definitely use the update global indexes clause to make sure global indexes are maintained in the nightly maintenance job, then no need to rebuild them entirely each time.
Thanks for input. we have partitioning scheme on top of that, we have sub partitions for drop last moth data.