Drop Contraints / Drop Indexes /Enable Constraints

-------------------------------------------------------------------------------------
DROP CONSTRAINTS
-------------------------------------------------------------------------------------
  
spool LOGS\DROP_FK_CONS_SS_CM2_BKP.log
  set serveroutput on;
  SET TIMING ON;
  SET FEEDBACK ON;
  SET ECHO ON;
  select instance_name from v$instance;
  SHOW USER;
     begin
                        for cur in (select fk.owner, fk.constraint_name , fk.table_name
                            from all_constraints fk, all_constraints pk
                            where fk.CONSTRAINT_TYPE = 'R' and
                            PK.OWNER = 'SS_CM2_BKP' AND
                            PK.TABLE_NAME not LIKE 'BIN%' and
              FK.R_CONSTRAINT_NAME = PK.CONSTRAINT_NAME AND
              FK.OWNER = 'SS_CM2_BKP' AND
              --FK.STATUS = 'ENABLED'   and
              FK.TABLE_NAME not LIKE 'BIN%') loop
              DBMS_OUTPUT.PUT_LINE ('ALTER TABLE '||CUR.OWNER||'.'||CUR.TABLE_NAME||' DROP CONSTRAINT '||CUR.CONSTRAINT_NAME);
                         EXECUTE IMMEDIATE 'ALTER TABLE '||CUR.OWNER||'.'||CUR.TABLE_NAME||' DROP CONSTRAINT '||CUR.CONSTRAINT_NAME;
       
                       END LOOP;
                    END;
          /
  select fk.owner, fk.constraint_name , fk.table_name ,fk.status
                            from all_constraints fk, all_constraints pk
                            where fk.CONSTRAINT_TYPE = 'R' and
                            pk.owner = 'SS_CM2_BKP' and
               PK.TABLE_NAME not LIKE 'BIN%' and
                            FK.R_CONSTRAINT_NAME = PK.CONSTRAINT_NAME AND
              FK.OWNER = 'SS_CM2_BKP' AND
              FK.TABLE_NAME NOT LIKE 'BIN%';
              spool off;


  spool LOGS\DROP_ALL_OTHER_CONS_SS_CM2_BKP.log
  set serveroutput on;
  SET TIMING ON;
  SET FEEDBACK ON
    SET ECHO ON;
  select instance_name from v$instance;
  SHOW USER;

            begin
                        for cur in (select owner, constraint_name , table_name
                            from all_constraints
                            WHERE OWNER = 'SS_CM2_BKP'
             -- AND STATUS = 'ENABLED'
              AND CONSTRAINT_TYPE <> 'R'
              AND TABLE_NAME not LIKE 'BIN%'
                            ) loop
              DBMS_OUTPUT.PUT_LINE ('ALTER TABLE '||cur.owner||'.'||cur.table_name||' DROP CONSTRAINT '||cur.constraint_name);
                          execute immediate 'ALTER TABLE '||cur.owner||'.'||cur.table_name||' DROP CONSTRAINT '||cur.constraint_name;
                       END LOOP;
                    end;
/
SELECT constraint_name, constraint_type, index_name, status
  FROM all_constraints
 WHERE OWNER = 'SS_CM2_BKP'
 AND CONSTRAINT_TYPE <> 'R';

   spool off;      
-------------------------------------------------------------------------------------
DROP INDEXES
-------------------------------------------------------------------------------------

  spool LOGS\DROP_ALL_INDEXES_SS_CM2_BKP.log
  set serveroutput on;
  SET TIMING ON;
  SET FEEDBACK ON
    SET ECHO ON;
  select instance_name from v$instance;
  SHOW USER;

            begin
                        for cur in (SELECT OWNER, INDEX_NAME, TABLE_OWNER, TABLE_NAME FROM ALL_INDEXES WHERE OWNER = 'SS_CM2_BKP'
and TABLE_OWNER = 'SS_CM2_BKP'
                            ) loop
              DBMS_OUTPUT.PUT_LINE ('drop index '||CUR.OWNER||'.'||CUR.INDEX_NAME);
                          EXECUTE IMMEDIATE 'drop index '||CUR.OWNER||'.'||CUR.INDEX_NAME;
             END LOOP;
                    end;
/
SELECT OWNER, INDEX_NAME, TABLE_OWNER, TABLE_NAME FROM ALL_INDEXES WHERE OWNER = 'SS_CM2_BKP'
and TABLE_OWNER = 'SS_CM2_BKP';

   spool off; 

-------------------------------------------------------------------------------------
ENABLE CONSTRAINTS
-------------------------------------------------------------------------------------

 spool LOGS\enable_CONS_ALL.log
  set serveroutput on;
  set timing on;
  select instance_name from v$instance;
  SHOW USER;

  begin
                        for cur in (select owner, constraint_name , table_name
                            from all_constraints
                            where owner = 'SS_CM2_BKP'
              and constraint_type <> 'R') loop
              Begin
              DBMS_OUTPUT.PUT_LINE ('ALTER TABLE '||cur.owner||'.'||cur.table_name||' MODIFY CONSTRAINT "'||cur.constraint_name||'" ENABLE ');
                          execute immediate 'ALTER TABLE '||cur.owner||'.'||cur.table_name||' MODIFY CONSTRAINT "'||cur.constraint_name||'" ENABLE ';
              EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE (cur.table_name);
DBMS_OUTPUT.PUT_LINE (SQLERRM);
Continue;
End;
                       end loop;
                    end;
/
SELECT constraint_name, constraint_type, index_name, status
  FROM all_constraints
 WHERE owner = 'SS_CM2_BKP' and constraint_type <> 'R';

   spool off;      


  spool LOGS\enable_FK_ALL.log
  set serveroutput on;
  set timing on;
  select instance_name from v$instance;
  SHOW USER;
     begin
                        for cur in (select fk.owner, fk.constraint_name , fk.table_name
                            from all_constraints fk, all_constraints pk
                            where fk.CONSTRAINT_TYPE = 'R' and
                            pk.owner = 'SS_CM2_BKP' and
                            fk.R_CONSTRAINT_NAME = pk.CONSTRAINT_NAME and
              fk.owner = 'SS_CM2_BKP' ) loop
              begin
              DBMS_OUTPUT.PUT_LINE ('ALTER TABLE '||cur.owner||'.'||cur.table_name||' MODIFY CONSTRAINT "'||cur.constraint_name||'" ENABLE ');
                          execute immediate 'ALTER TABLE '||cur.owner||'.'||cur.table_name||' MODIFY CONSTRAINT "'||cur.constraint_name||'" ENABLE ';
                            EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE (cur.table_name);
DBMS_OUTPUT.PUT_LINE (SQLERRM);
Continue;
End;
                       end loop;
                    end;
          /
  select fk.owner, fk.constraint_name , fk.table_name ,fk.status
                            from all_constraints fk, all_constraints pk
                            where fk.CONSTRAINT_TYPE = 'R' and
                            pk.owner = 'SS_CM2_BKP' and
                            fk.R_CONSTRAINT_NAME = pk.CONSTRAINT_NAME and
              fk.owner = 'SS_CM2_BKP' ;
              spool off;