I am on 12.1.0.2.
I need to write a PL/SQL block to disable or drop all the constarinats dynamically , do some large DML ( deletes) and then enable or rebuild the indexes / constraints.
I can dynamically disable the constarints or mark indexes unusable but the issue is I can't fugure out a way to dynamically enable them ( considering the fact that I don't know if any new indexes or constraints may be added on the base table anytime after the script was written ) . Hence I need to capture the DDL in real time.
I am having a road block when I mark the existing indexes unusable, I can't issue any DML ( Deletes) on the base table. I also set the skip_unusable_indexes = true at session level but had no luck.
I think the only way now is to capture the DDL of existing indexes and constarints before dropping them and then recreateing then on the other size of the DML ( delete).
I know DBMS_METADATA.GET_DDL can help get the DDL but not sure how to capture this and issue on the other side of the deletes.
Can someone please share a sample PL/SQL block code that can do this?
Many Thanks