Strange Error, Never Expected.

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