marți, 7 ianuarie 2014

What if you want to freeze all the constraints in a schema?

Sometime, you need to recreate tables or another objects, and this action is not posible because of the dependencies, represented by constraints. If you try to overcome this issue, you will find that is a big waste of time, 'cause you're trying on and on to find all the dependencies and tables related one to another.
So, another idea could be the next one:
Disable ALL the constraints in the schema, for a short period of time, but be aware, don't do this while the users who can load some data are connected!

The script that disables the constraints: 

declare
nume_ct varchar2(50);
begin 
for nume_ct in (select constraint_name,u.table_name from user_constraints c, user_tables u where c.table_name = u.table_name)
    loop 
    EXECUTE IMMEDIATE 'alter table ' || nume_ct.table_name || ' disable constraint ' ||nume_ct.constraint_name;
    end loop;                
end;


Script that enables the constraints: 

declare
nume_ct varchar2(50);
begin 
for nume_ct in (select constraint_name,u.table_name from user_constraints c, user_tables u where c.table_name = u.table_name)
    loop 
    EXECUTE IMMEDIATE 'alter table ' || nume_ct.table_name || ' enable constraint ' ||nume_ct.constraint_name;
    end loop;                
end;

Niciun comentariu:

Trimiteți un comentariu