DISABLE CONSTRAINTS [message #379491] |
Tue, 06 January 2009 14:53  |
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   |
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 #379664 is a reply to message #379491] |
Wed, 07 January 2009 09:43   |
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 #379669 is a reply to message #379491] |
Wed, 07 January 2009 09:57   |
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 #379978 is a reply to message #379691] |
Thu, 08 January 2009 16:12   |
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:
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 #380100 is a reply to message #379978] |
Fri, 09 January 2009 03:03   |
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  |
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!!
|
|
|