Home » SQL & PL/SQL » SQL & PL/SQL » DISABLE CONSTRAINTS (ORACLE 9I)
DISABLE CONSTRAINTS [message #379491] Tue, 06 January 2009 14:53 Go to next message
manningda
Messages: 31
Registered: January 2008
Member
Friends:

Have a test environment where I will frequently want to refresh data provided from client.

Trying to:
1. DISABLE Contraints
2. TRUNCATE tables
3. refresh data
4. re-enable constraints

Following failing with ORA-00942 Table or View does not exist.
And ORA-06512: at line 14,


But... the actual SELECT query succeeds if run alone and obviously ALL_CONSTRAINTS does exist.


Can someone help me see what Im missing?

THANKS!!!



DECLARE
	CURSOR ConstraintRefs IS
		SELECT constraint_name, table_name
		FROM all_constraints
		WHERE owner IN ('owner1','owner2','etc');

	cnstOFF VARCHAR(100);

BEGIN
	FOR Constraint IN ConstraintRefs LOOP
	cnstOFF := 'ALTER TABLE '||Constraint.Table_Name||
		   ' DISABLE CONSTRAINT '||Constraint.Constraint_Name;

	EXECUTE IMMEDIATE CnstOff;
	END LOOP;

END;
Re: DISABLE CONSTRAINTS [message #379493 is a reply to message #379491] Tue, 06 January 2009 15:10 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
Do you have the rights to other schema's objects with a direct grant and not a role? Procedures require a direct grant and not through a role.

Upon further review, I see that you are using ALL_CONSTRAINTS, but you do not preface the object with the schema in your ALTER command.

[Updated on: Tue, 06 January 2009 15:12]

Report message to a moderator

Re: DISABLE CONSTRAINTS [message #379548 is a reply to message #379491] Wed, 07 January 2009 00:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
Have a test environment where I will frequently want to refresh data provided from client.

Why not using an export/drop/import of the schema?

Regards
Michel
Re: DISABLE CONSTRAINTS [message #379660 is a reply to message #379548] Wed, 07 January 2009 09:25 Go to previous messageGo to next message
manningda
Messages: 31
Registered: January 2008
Member
Joy / Michael:
THANKS for the suggestions!!!!
Follow up note on each follow...


Joy:
Q: Do you have rights to the other schema's objects?
A: I assumed yes as I am logging in as SYS DBA, but perhaps I made a mistake. Embarassed How would I confirm?


Michael:
Q: Why not export/drop/import?
A: K... maybe Im overlooking an imp parameter Confused . I think you're suggesting to drop all user objects then re-import from the most current .dmp from the production sys yes?

If we're on the same thought; here're some more q's.
a) Are you dropping with an imp parameter? If yes, which one?
b) If no, wouldn't you still need to disable constraints to effectively write a procedure to "DROP" all user obj's?


What seemed logical was to truncate all existing objects and reload them with an IMP -FROMUSER / -TOUSER.

But...

In the long run Ill still need to periodically disable constraints for some of the live db support. This is an "inherited" system with limited documentation, so an effective "DISABLE ALL CONSTRAINTS" procedure will still be valuable in the long run.

I started with a procedure that disabled all constraints from the USER_CONSTRAINTS view and that succeeded with no error, but... The owners I actually want to work with aren't in the USER_CONSTRAINTS view, but are in the ALL_CONSTRAINTS view.

So... That's how we got to the code below; but I'm still struggling to see what's written that would produce a [TABLE OR VIEW DOES NOT EXIST].

The original CURSOR ConstrantRefs that succeeded was:

ECLARE

	CURSOR ConstraintRefs IS

	SELECT  constraint_name, table_name
	FROM user_constraints
	WHERE constraint_type='R'
	AND table_name IN
		(SELECT table_name
		FROM user_tables);

	CnstOff VARCHAR2(100);


BEGIN
	FOR Constraint IN ConstrainRefs LOOP
	CnstOff:= 'ALTER TABLE '||Constraint.Table_Name||
		  ' DISABLE CONSTRAINT '||Constraint.Constraint_Name;
	
	EXECUTE IMMEDIATE CnstOff;
	END LOOP;
END;	


Continued thanks for all suggestions!!!
Re: DISABLE CONSTRAINTS [message #379664 is a reply to message #379491] Wed, 07 January 2009 09:43 Go to previous messageGo to next message
cookiemonster
Messages: 13964
Registered: September 2008
Location: Rainy Manchester
Senior Member
As Joy Division pointed out before:
If you don't specify the owner then oracle assumes the object is in the current schema (sys), unless you've got synonyms set up and I bet you haven't.

And stop using sys - use system or some other DBA user. Sys doesn't always work the you expect, and if you get a script like that wrong in sys you'll probably corrupt the data dictionary.
Re: DISABLE CONSTRAINTS [message #379668 is a reply to message #379664] Wed, 07 January 2009 09:52 Go to previous messageGo to next message
manningda
Messages: 31
Registered: January 2008
Member
EUREKA!! Surprised
CUDOS -2- JOY AND COOKIE MONSTER!!! Surprised

Yes, was not qualifying the owner name in the ALTER TABLE statement and THAT's why [TABLE NOT FOUND]!!

Corrected:
DECLARE
	CURSOR ConstraintRefs IS
		SELECT constraint_name, owner, table_name
		FROM all_constraints
		WHERE owner IN ('91D','OPR$3P3','PEMS','WEBSYS');

	CnstOff VARCHAR(100);

BEGIN
	FOR Const IN ConstraintRefs LOOP
	CnstOff:='ALTER TABLE '||Const.owner||'.'||Const.table_name||
		       ' DISABLE CONSTRAINT '||Const.Constraint_Name;

	EXECUTE IMMEDIATE CnstOff;
	END LOOP;

END;


Now, Im getting a DEPENDANCIES error on some of the constraints attempting to disable.

I think I can sort that out from here, but don't be surprised if we're back with new question later.

CONTINUED THANKS TO ALL WILLING TO SHARE THEIR KNOWLEGE / EXPERIENCE!!!!
Re: DISABLE CONSTRAINTS [message #379669 is a reply to message #379491] Wed, 07 January 2009 09:57 Go to previous messageGo to next message
cookiemonster
Messages: 13964
Registered: September 2008
Location: Rainy Manchester
Senior Member
If the purpose of this script is to allow you to truncate tables then the only constraints you need to disable are the foreign keys.
That being the case specifying a constraint_type of R will simplify matters.
And don't run it in SYS.
Re: DISABLE CONSTRAINTS [message #379691 is a reply to message #379660] Wed, 07 January 2009 12:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
1/ if you export, drop and import full schema you don't need to disable constraints. By the way drop is the step in itself, it is not part of import.

2/
- Never ever use SYS but for maintenance purpose (startup, shutdown, backup, recover)
- SYS is special
- SYS is Oracle proprietary (try to open a SR/TAR starting with "i did that with SYS" and you'll see the immediate answer)
- SYS does not act like any other user
- When you use SYS Oracle deactivates some code path and activates others
- Whatever you do with SYS will neither validate nor invalidate the same thing with any other user.

NEVER EVER use SYS for anything that can be done by another user.
Use SYS ONLY for something that can't be done by someone else.


Regards
Michel
Re: DISABLE CONSTRAINTS [message #379978 is a reply to message #379691] Thu, 08 January 2009 16:12 Go to previous messageGo to next message
manningda
Messages: 31
Registered: January 2008
Member
Michael / Cookie:

I hear you about "DON'T USE SYS!"

Sill refining my ORACLE education and frankly a little weak on user roles and permissions. Have developed a habit of falling back to a user with SYSDBA priv's to develop an idea and then refining for a particular user if correct.

Not actually working AS SYSDBA, but with a new user configured to be as close to SYS DBA as can be.

But to be clear --- All readers should heed Michael and Cookie's advice here and not assume my bad habit.

I am working ENTIRELY in a test environment with MULTIPLE REDUNDANT backups to fall back on when necessary.

Now with that said, new question...

Have this DISABLE CONSTRAINTS concept wrapped up and put to the side for any case where Ill need it, but also working with the DROP USER idea for the "data-reload" scenario.

DROP CASCADE succeeds for 2 of 3 users I need to wipe before attempting a reload.

Third user fails with ORA-01935: missing user or role name

User name 91D:

DROP USER 91D CASCADE


The user is definately there and has multiple tables and objects in it's schema.

Could the problem be that the user id begins with a numeric charecter?

Does anyone have a method for "aliasing" 91D in the script or any other ideas?

CONTINUED THANKS!!!
Re: DISABLE CONSTRAINTS [message #379980 is a reply to message #379491] Thu, 08 January 2009 16:27 Go to previous messageGo to next message
manningda
Messages: 31
Registered: January 2008
Member
found it

DROP USER "91D" CASCADE


Once more;
THANKS TO ALL WHO TAKE THE TIME TO SHARE WITH THOSE STILL LEARNING!
Re: DISABLE CONSTRAINTS [message #380072 is a reply to message #379980] Fri, 09 January 2009 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
Have developed a habit of falling back to a user with SYSDBA priv's to develop an idea and then refining for a particular user if correct.
Not actually working AS SYSDBA, but with a new user configured to be as close to SYS DBA as can be.

Nothing is closed to SYSDBA.
Carefully reread my warning:
- When you use SYS Oracle deactivates some code path and activates others
- Whatever you do with SYS will neither validate nor invalidate the same thing with any other user.

What you developed with SYS may work or not you can't know. What will happen if it took you weeks to develop something and then you see you can't use it (but with SYS)?

Regards
Michel
Re: DISABLE CONSTRAINTS [message #380100 is a reply to message #379978] Fri, 09 January 2009 03:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
YOu want to try ideas out as a user with DBA, not SYSDBA.

DBA lets you do anything inside the database.
SYSDBA only lets you doe things like shutdown the Database - you don't need it.
Re: DISABLE CONSTRAINTS [message #380199 is a reply to message #380100] Fri, 09 January 2009 08:35 Go to previous message
manningda
Messages: 31
Registered: January 2008
Member
JRow / Michael:

Maybe this is a new learning opportunity for me...

I will openly confess I have heard this advice repeatedly, but here's the scenario that is drawing me away from it in this circumstance...

Have an inherited sys where documentation; and unfortunately some user credentials have been lost along the way.

Have a support user id that is DBA.

Using this have never been able to effectively DISABLE CONSTRAINTS; and from time-2-time I find other actions that DBA "should" be able to do, but can't.

As you mention, most priv's are there for DBA and the exceptions are not frequent; but when they occur SYSDBA will always allow the level of control we seem to have lost w/DBA.

So...
As we work to diagnose what the issue is with the DBA acct's; I make use a SYSDBA acct to provide a type of "SUPER-USER" control to add efficency to the troubleshooting.

What works for the SYSDBA is applied against another user to identify the correct permissions to execute the task.

At the risk of repetition... This is a TEST ENVIRONMENT where mistakes are as valuable as success.

"Crap! That broke it!" Is as valuable a learning experience as any other in this environment; but keep in mind that is also supported by a carefully managed series of BACKUP FILES that allow the environment to be quickly refreshed when we crash it.

So...
I don't want to say anything to dismiss good advice; and acknowledge this is a use of SYSDBA in contradiction with standard advice... but that is how we got here.

Any suggestions for alternate approach is always welcome; but for now this allows me to:
1. quickly pursue an issue with wide ranging control and access within the db.
2. experience the ORACLE environment in a way that i think makes for a stronger knowledge-base in the long run
...

SINCERE AND CONTINUED THANKS!!
I remain open and excited to hear alternative ideas!!
Previous Topic: Calculate EMA
Next Topic: Unable to Insert CLOB
Goto Forum:
  


Current Time: Thu Feb 13 19:35:29 CST 2025