Q

Error after rerunning catalog.sql and catproc.sql

I was getting the following error:

 SQL> drop user ambrish cascade;
 drop user ambrish cascade
 *
 ERROR at line 1:
 ORA-00604: error occurred at recursive SQL level 1
 ORA-00942: table or view does not exist
So, as per the tip given on this Web site, I reran the catalog.sql and catproc.sql, but after running these files I am get this error:
 
 SQL> create user abc
   2  identified by abc;
 identified by abc
               *
 ERROR at line 2:
 ORA-04045: errors during recompilation/revalidation
 of
 SYS.CDC_CREATE_CTABLE_BEFORE
 ORA-06508: PL/SQL: could not find program unit being
 called
 ORA-06512: at line 2
 ORA-06508: PL/SQL: could not find program unit being
 called
 ORA-06512: at line 2
 
I have an Oracle9i database running on Linux.

When you drop any object or user, the Oracle database does a ton of work behind the scenes, mainly to update the data dictionary. The data dictionary is really nothing more than database tables. And how else do you interact with database tables but the SQL language?!?! So when you issue a DROP USER command, the system issues SQL statements on your behalf. These SQL statements are called "recursive SQL". Unfortunately, you can't see these recursive SQL statements in SQL*Plus to find out which SQL statement is having the problem. But you can easily set up a trace, which will show you all of the recursive SQL statements. So try the following:

ALTER SESSION SET sql_trace=TRUE;
DROP USER my_user CASCADE;
ALTER SESSON SET sql_trace=FALSE;
Then, look in USER_DUMP_DEST for a trace file that was just generated. Examining this trace file can help you determine what exact recursive SQL statement is having problems, and how to fix it.

For other advice, make sure that when your run CATALOG and CATPROC, that you are connected as SYS.

For More Information


This was first published in March 2003

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close