DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: script to disable all constraints

  1. #1
    Join Date
    Jan 2001
    Posts
    157

    Question

    Is there a script that will disable all constraints? I know of scripts that will disable one constraint at a time but that's not what I want. I want one that will disable say about 50 constraint after it's has been executed.


    NOTE:
    Just like the one that disables all triggers in the database when the script is executed

  2. #2
    Join Date
    Feb 2001
    Posts
    22
    I think that you can code a simple script or PL/SQL to get all constraint name from USER_CONSTRAINTS dictionary or DBA_CONSTRAINTS dictionary.

    For UNIX, following is a simple method to disable all user constraints.

    Create a shell script

    #!/bin/sh
    (
    SQLPLUS -S /NOLOG <<-EOF
    CONNECT USERID/PASSWORD
    SET FEEDBACK OFF
    SET PAGESIZE 0
    SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS
    EOF
    ) | while read constraint_name
    do
    echo "ALTER ${constraint_name} DISABLE NOVALIDATE
    done | sqlplus userid/passwd

    If you want to perform more advanced functions, I suggest that you can use PL/SQL instead.







Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width