-------------------------------------------------------------------------------------
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;
