disabling all the constraints in the schema
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: disabling all the constraints in the schema

Hybrid View

  1. #1
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Hi,
    I want to write a script which disables all the integrity constraints in a given schema.

    Can somebody help me with this

    Thanks
    Ronnie

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    The table you are looking for is user_constraints...
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Apr 2001
    Location
    Louisville KY
    Posts
    295
    spool dissem
    select 'Alter Table '||owner||'.'||table_name||' Disable Constraint '||constraint_name;'
    from dba_constraints
    order by ower,table_name;
    spool off
    spool enabem
    'Alter Table '||owner||'.'||table_name||' Disable Constraint '||constraint_name;'
    from dba_constraints order by ower,table_name;
    spool off



    Then edit the spool files to trim out some feedback from the queries.
    Joseph R.P. Maloney, CSP,CDP,CCP
    'The answer is 42'

  4. #4
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Originally posted by jrpm
    spool dissem
    select 'Alter Table '||owner||'.'||table_name||' Disable Constraint '||constraint_name;'
    from dba_constraints
    order by ower,table_name;
    spool off
    spool enabem
    'Alter Table '||owner||'.'||table_name||' Disable Constraint '||constraint_name;'
    from dba_constraints order by ower,table_name;
    spool off



    Then edit the spool files to trim out some feedback from the queries.
    Thanks a lot guys.

    jrpm, I think u mean to say enable constraint.

    ALso what does ur signature the answer is 42 mean. just curios :-)

    Ronnie

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,027
    If you read the 5 part trilogy the hitchhikers guide to the galaxy you would know that the answer to the ultimate question about life the universe and everything is 42, but they don't know the question. the books are by Douglas Adams annd they are a good read.

  6. #6
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Be aware that when you disable a constraint the associate index is dropped. When you enable it again the index is created in the users default tablespace and not in the tablespace where it has been put originally.

    Sanjay

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