This content has been marked as final. Show 11 replies
You can write a small pl/sql block to disable all the FK constraints for 12 tables and truncate. YOu can use dynamic sql to disable constraint and truncate the table.
DELETE and TRUNCATE are two different animals. If you want to delete, and FK are not on cascade delete you can write a PL/SQL code to query USER_DEPENDENCIES (I assume all tables belong to one user and you run code connected as such) and delete starting from child tables. If you need to truncate, you will have to drop FKs, truncate tables and recreate FKs.
Can someone share a sample code to disable foreign keys and cascade delete? thanks.
One way could be:
BEGIN -- Disable constraints -- Truncate (TABLE_NAME_1 is an example you must change the name for your tables FOR reg IN (SELECT table_name FROM user_tables WHERE table_name IN ('TABLE_NAME_1')) LOOP EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || reg.table_name; END LOOP; -- Enable constraints END; /
To enable again just change 'DISABLE' for 'ENABLE' and remove CASCADE. At the end you should ensure that all your constraints are enabled, for that case use:
DECLARE c_action CONSTANT VARCHAR2(10) := 'DISABLE'; BEGIN FOR reg IN (SELECT uc.table_name, uc.constraint_name FROM user_constraints uc WHERE uc.table_name IN ('TABLE_NAME_1')) LOOP EXECUTE IMMEDIATE 'ALTER TABLE ' || reg.table_name || ' ' || c_action || ' CONSTRAINT ' || reg.constraint_name || ' CASCADE'; END LOOP; END; /
The query above do not show any results
SQL> SELECT * FROM USER_CONSTRAINTS UC WHERE UC.status != 'ENABLED';
Tip: In these cases I prefer to change the 'EXECUTE IMMEDIATE' for a simply DBMS_OUTPUT.PUT_LINE to show the statements before the real execution.
Edited by: Walter Fernández on Jun 1, 2009 6:09 PM
Edited by: Walter Fernández on Jun 1, 2009 6:10 PM - Adding more information...
Edited by: Walter Fernández on Jun 1, 2009 6:15 PM - Adding tip
thank you all for your valuable input. i disabled FKs, truncated tables, enabled back FKs, and insert my new data, and all worked just fine. thanks again.
Your welcome, don't forget to mark the thread as resolved and give (If you want, of course) any point to the participants ;)
FOR i IN (SELECT table_name FROM user_tables)
EXECUTE IMMEDIATE('DROP TABLE ' || user || '.' || i.table_name || ' CASCADE CONSTRAINTS PURGE');
Why are you shouting?
OP doesn't want to DROP the tables; they wan't to TRUNCATE them.
why are you resurrecting a 4 YEAR old thread?
Is dropping the only option ? I suppose we can disable it and enable the constraints after truncating the table.