Ask the Expert

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.

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: