Shulou(Shulou.com)05/31 Report--

This article mainly shows you "how to disable and enable foreign keys and triggers in batches in the database". The content is simple and clear. I hope it can help you solve your doubts. Let the editor lead you to study and learn the article "how to disable and enable foreign keys and triggers in a batch in the database".

SET SERVEROUTPUT ON SIZE 100000

BEGIN

For c in (select 'alter table "' | | t.owner | |''.'| | t.table_name | | 'disable constraint' | | t.constraint_name as v_sql

From dba_constraints t where t.constraint_type ='R 'and owner in (' CCOD','RECORD','UCDS')

) loop

DBMS_OUTPUT.PUT_LINE (C.V_SQL)

Begin

EXECUTE IMMEDIATE c.v_sql

Exception when others then

Dbms_output.put_line (sqlerrm)

End

End loop

For c in (select 'alter trigger "' | | t.owner | |''.'| | t.trigger_name | |

'disable' as v_sql

From dba_triggers t

Where owner in ('CCOD','RECORD','UCDS')) loop

Dbms_output.put_line (c.v_sql)

Begin

Execute immediate c.v_sql

Exception when others then

Dbms_output.put_line (sqlerrm)

End

End loop

End

/

-enable scripting

SET SERVEROUTPUT ON SIZE 100000

BEGIN

For c in (select 'alter table "' | | t.owner | |''.'| | t.table_name | | 'enable constraint' | | t.constraint_name as v_sql

From dba_constraints t where t.constraint_type ='R 'and owner in (' CCOD','RECORD','UCDS')) loop

DBMS_OUTPUT.PUT_LINE (C.V_SQL)

Begin

EXECUTE IMMEDIATE c.v_sql

Exception when others then

Dbms_output.put_line (sqlerrm)

End

End loop

For c in (select 'alter trigger "' | | t.owner | |''.'| | t.trigger_name | |

'enable' as v_sql

From dba_triggers t

Where owner in ('CCOD','RECORD','UCDS')) loop

Dbms_output.put_line (c.v_sql)

Begin

Execute immediate c.v_sql

Exception when others then

Dbms_output.put_line (sqlerrm)

End

End loop

End

/

The above is all the contents of the article "how to batch disable and enable foreign keys and triggers in the database". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.