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 existSo, 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 2I 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
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in March 2003