Oracle PL SQL disable all constraints of database tables

Question

I try to disable all constraint of all database tables. The database is named "database_test". The performance is not a problem.

I wrote a pl/sql script which give me all constraints with the table.

My problem is : when I run the script SQL developper say me "ORA-00972: identifier is too long". But I use only the fields already defined in the database.

CREATE OR REPLACE PROCEDURE DISPLAY_CONSTRAINT_DATABASE AS 
BEGIN
    FOR i IN (SELECT DISTINCT OWNER, OBJECT_NAME FROM ALL_OBJECTS WHERE OBJECT_TYPE = 'TABLE' AND OWNER = 'DB_NAME')
    LOOP
        FOR j IN (SELECT CONSTRAINT_NAME FROM ALL_CONSTRAINTS WHERE owner = i.OWNER AND table_name = i.OBJECT_NAME AND CONSTRAINT_TYPE='R')
        LOOP
            dbms_utility.exec_ddl_statement('alter table "DB_NAME.' || i.OBJECT_NAME || ' DISABLE CONSTRAINT ' || j.CONSTRAINT_NAME);
        END LOOP; 
    END LOOP; 
END DISPLAY_CONSTRAINT_DATABASE;

score 3 · Accepted Answer · 2014-07-31 14:04:10Z

3

Your double quotes are wrong. You are generating SQL statements like this:

alter table "DB_NAME.FOOBAR disable constraint some_constraint;

Which misses the second double quote (my guess is that you probably wanted to put the second quote after the table name which would have been wrong as well).

You need to put each part of the identifier in quotes, not the whole thing:

alter table "DB_NAME"."FOOBAR" disable constraint some_constraint;

I also don't see the necessity to use dbms_sql:

execute immediate 'alter table "DB_NAME"."' || i.OBJECT_NAME || '" DISABLE CONSTRAINT ' || j.CONSTRAINT_NAME;

To avoid having to repeat the owner, I would actually change the statement to:

execute immediate 'alter table "'||j.owner||'"."' || i.OBJECT_NAME || '" DISABLE CONSTRAINT ' || j.CONSTRAINT_NAME;

Thus you only need to "hard-code" the owner name once in the procedure.

edited Jul 31, 2014 at 14:04

answered Jul 30, 2014 at 15:09

user330315

Sign up to request clarification or add additional context in comments.

5 Comments

Samuel Dauzon Over a year ago

Thank you. Now SQL developer say me "ORA-01031: insufficient privileges". However, I'm connected in 'system' with all privileges.

Alex Poole Over a year ago

@jedema - SYSTEM has ALTER ANY TABLE through the DBA role only, I think, so that wouldn't be available in a definers-rights procedure. You could do this as SYS, but you should not be creating your own objects in either schema. Why aren't you doing this under the DB_TEMP schema?

user330315 Over a year ago

@jedema: the question is: why are you using the SYSTEM account in the first place? You should be using a regular user (most probably the one for which you want to disable the constraints). Btw: DB_NAME does not denote a "database" it denotes a "schema" (or "user" in Oracle).

Samuel Dauzon Over a year ago

I don't know how to use DB_TEMP. A very great thank you @a_horse_with_no_name because if I use another user it works. At the beginning I used "SYSTEM" beause in another account I had not privileges to access DBA_OBJECTS table. But now, with ALL_OBJECTS its works.

Samuel Dauzon Over a year ago

@a_horse_with_no_name : Can you remove the two brackets superfluous at the end of "execute immediate" line please. I can't edit for 2 characters. Otherwise these lines don't work. Thank you.

Collectives™ on Stack Overflow

Oracle PL SQL disable all constraints of database tables

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related