Cannot create or drop any oracle objects

Question

I'm trying to update an oracle database so that it's schema matches a second database exactly. In doing so I've hosed something. I can't create or drop any object.

create or replace procedure eag.test
as
begin
    null;
end;

Error:

ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist

I get the same error for any create or drop I try to do. Any ideas?

EDIT: running Oracle 10.2.0.4

Answer 1

You have a corrupt data dictionary.

If you can build a new database and start you sync process over. This time not using sysdba. Only sysdba has privileges to be destructive to the data dictionary.

If you need the data but can start over you may still be able to export the data using exp or expdp.

If you can't start over with a new database. Open a SAR with Oracle support. Even if someone else might be able to walk you through this they are the only ones who can bring your database back to an Oracle Supported state.

Answer 2

Worth checking if you have any DDL triggers on the database. There's probably a few built in ones from MDSYS, but you may have your own (eg for auditing DDL)

select owner, trigger_type, triggering_event, trigger_name, base_object_type, when_clause, description 
from dba_triggers d
where table_name is null
and status = 'ENABLED'
order by d.triggering_event, d.owner

Answer 3

Potentially this is a corrupt database but don't be too hasty. Recursive errors can occur when session and process limits are exceeded.

There is a slim chance you have exceeded the processes limit. How many processes do you currently have in your instance ? Are you close to the limit imposed by the parameter "processes" ? If so, recursive SQL could be failing and presenting the error.

Check parameter processes from sqlplus session (or IDE) with :

show parameter processes

Then check number of processes from v$process :

select count(*) from v$process

Are you close to the threshold ? If you are, increase the processes parameter : eg if your processes limit was 150 increase to 175

alter system set processes=175 scope=spfile

You will have to restart the server for the change to take effect as you cannot dynamically modify the processes parameter.

Otherwise, trace the session and see where the problem actually is :

From your current session issue the following :

alter session set sql_trace=TRUE

Then run your create or replace again, when it bombs out look at your trace file that is created in the USER_DUMP_DEST directory. Look for ORA errors in there, they may lead to a different problem but it may save a load of time.