-
Hi Fellows,
I am having Troule Altering any Table, This is kinda weird, Never had such.
I am on 8.1.6 on Win 2000 .
can not alter any table to Add , Drop or Modify any thing in it, This is he error that I get.
SQL> alter table medloc modify(seq_no null);
alter table medloc modify(seq_no null)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Cannot Drop column or Table
ORA-06512: at line 5
SQL> alter table medloc add(newc number);
alter table medloc add(newc number)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Cannot Drop column or Table
ORA-06512: at line 5
Can any one please help me, WHY??
Thanks..
-
00604, 00000, "error occurred at recursive SQL level %s"
// *Cause: An error occurred while processing a recursive SQL statement
// (a statement applying to internal dictionary tables).
// *Action: If the situation described in the next error on the stack
// can be corrected, do so; otherwise contact Oracle Support.
Previously once i got these error when i imported data from one database to another database. First i imported the data from database a to database b but i am not satisfied with that import and again i imported data in database b. Then when i connected as user i got this problem. Then i dropped the all users except SYS,SYSTEM,OUTLN. Then again i imported data into database b. This time the problem fixed. So if this is your situation then try the above method. If it's not then give a call to customer support. Any way wait some more time. Out folks will get back you with some more good ideas. Good luck.
-
Any Thought Please.
The Database does not let me Alter any table for any Action.
Thanks
-
alter table medloc add(newc number);
This could be because you are trying to alter the table as a sys. Try qualifying the tablename with the schema name
alter table schema.tablename add (column datatye);
example:
alter table scott.emp add(dob date);
I hope this should work fine.
Sam
-
Thanks for You Input.
It's not the case. the Table is in my own Schema and Even by specifying User.Table_name does not solve this problem.
Any othe Possibility...
Thanks
-
as per the documentaion it seems like the internal dictionary tables are messed up.
Was there any software or database upgrade applied recently or any other major change ?
- Rajeev
-
Are there any DB level triggers written ??? Just check out.
The error message "ORA-20001: Cannot Drop column or Table" seems to be a user defined one.
-
No Not at all.
I only Installed EZSQL for Testing, I suppose It does not harm any thing.
Rest is as it is? Even did not change any thing in SYS or even in Dictionary.
-
02001, 00000, "user SYS is not permitted to create indexes with freelist groups"
// *Cause: user tried to create an index while running with sys authorization.
// *Action:
Sam
-
There is no Instance level Trigger. I don't see even Logging as Sys.
Yes I do have DBA Privs and even SYSDBA, but at this time I was logged on as Normal DBA.
Any other Thougt.
Please
-
is this the only table you have trouble with ?
Did you try --
select object_name, status from user_objects where status <> 'VALID'
just to see what the object status is ?
- Rajeev
-
I agree with the previous statements in that it looks like a data dictionary problem. I had something similar and the only thing that I could do was recreate the db.
One question: does it generate a trace file for you?
-
It's quite possible that there is something wrong with your data dict.
Create a new table and try to alter it's structure.
Keep this post updated.
Good Luck,
Pinakin.
-
I check the metalink and it was not informative. To be on the safer side, can you do
create table schema.new-name as select * from schema.old_table;
If you have any indices created under, then you have to recreate them. On the new table can you try altering the information what you would want to.
Sam
-
As a last resolute, you could rerun the catlog.sql and catproc.sql from $ORACLE_HOME/rdbms/admin and I think that would fix the problem for you.
Sam
-
hmmm... Sam, about running catproc and catalog.sql my understanding was that these rebuild the data dictionary for all the SYS objects and not for USER objects.
Catproc and catalog.sql are hard-coded scripts and have no idea of what user might have created so I am doubtful but I will be watching to see if that fixes the problem.
- Rajeev
-
One other thing that he could do is that export the user schema, drop the user and recreate the user and import the user schema. How does this sound??
Sam
-
okay Mister
the ball is in your court now; let us know what your findings were with the above suggestions !
-
Thanks to All,
I happen to find the Problem. There was a Trigger that was restricting to Modify.
Sorry! I could have explore first.
Thank a lot for all of you, who participated in it.
Thanks again.
-
Mind checking if you have ALTER TABLE privilege ?? :-)
-amar