Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2218
  • Last Modified:

Disable all table Constraints Oracle

Below I have attached a script I use to disable all scripts in the specified table, which works perfect. I am unsure on how I would modify this so it would disable all constraints in the entire database.

-- DISABLE --
accept tab prompt "Enter the name of the table to disable its constraints: "
set serveroutput on
declare
    sql_stm    varchar2(2000);
begin
    dbms_output.enable(1000000);
    for x in (select constraint_name
                    ,decode(constraint_type,'P',' cascade') casc
               from user_constraints
               where table_name = upper('&tab')) loop
        sql_stm := 'alter table &tab disable constraint '||
            x.constraint_name||x.casc;
        dbms_output.put_line(sql_stm);
        execute immediate sql_stm;
    end loop;
end;
/

Open in new window

0
CurtinProp
Asked:
CurtinProp
5 Solutions
 
fluglashCommented:
add outer loop like that:


for y in (select table_name 
             from user_tables) loop
    for x in (select constraint_name
              ,decode(constraint_type,'P',' cascade') casc
              from user_constraints
              where table_name = y.table_name) loop
        sql_stm := 'alter table ' || y.table_name || ' disable constraint '||
            x.constraint_name||x.casc;
        dbms_output.put_line(sql_stm);
        execute immediate sql_stm;
    end loop;
end loop;

Open in new window

0
 
sujith80Commented:
Disabling all the constraints in the database could break a lot and lot of things.
Are you sure you want to do that?

See the changed script. It disables all the constraints of the current database schema.
You can change user_constraints to dba_constraints for all the constraints in the database. But check all the constraints to make sure that they are supposed to be dropped.
set serveroutput on
declare
    sql_stm    varchar2(2000);
begin
    dbms_output.enable(1000000);
    for x in (select table_name, constraint_name
                    ,decode(constraint_type,'P',' cascade') casc
               from user_constraints ) loop
        sql_stm := 'alter table '||x.table_name||' disable constraint '||
            x.constraint_name||x.casc;
        dbms_output.put_line(sql_stm);
        execute immediate sql_stm;
    end loop;
end;
/

Open in new window

0
 
Ivo StoykovCommented:
Hi

Here is a sample. It will create a script you could run in sqlplus or Command window

HTH

I
set linesize 120;
SET head off;
SET echo off;
SET pagesize 0;
SET feedback off;
 
SPOOL disable_all_constraints.sql;
SELECT 'ALTER TABLE ' ||
       owner ||
       '.' ||
       table_name ||
       ' DISABLE CONSTRAINT ' ||
       constraint_name ||
       ';' "ALTER Statement"
  FROM dba_constraints
/
 
SPOOL off;

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
johnsoneSenior Oracle DBACommented:
If you are going to attempt all tables in the database, it would be much safer to specify a list of owners.  At a minimum, you should exclude SYS and SYSTEM.  Disabling constraints in those schemas would most likely render your database unsupportable.
0
 
sujith80Commented:
@johnsone:
Very good point.
0
 
dbmullenCommented:
you provided your own answer
just remove the accept and the "where clause"
-- DISABLE --
--accept tab prompt "Enter the name of the table to disable its constraints: "
set serveroutput on
declare
    sql_stm    varchar2(2000);
begin
    dbms_output.enable(1000000);
    for x in (select constraint_name
                    ,decode(constraint_type,'P',' cascade') casc
               from user_constraints
--               where table_name = upper('&tab')
               ) loop
        sql_stm := 'alter table &tab disable constraint '||
            x.constraint_name||x.casc;
        dbms_output.put_line(sql_stm);
        execute immediate sql_stm;
    end loop;
end;
/

Open in new window

0
 
sakthikumarCommented:
Is there any single alter option to disable all constraints that belong to a table
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now