| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Funny?
Tried this Mark ????
Doc ID: Note:1066812.6
Type: PROBLEM
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 05-MAR-1999
Last Revision Date: 19-APR-2001
Problem Description:
You are trying to drop a user and you receive the following errors:
SVRMGRL> DROP user fred;
ORA-604 "error occurred at recursive SQL level %s" ORA-942 "table or view does not exist"
You verified the user existed by querying dba_users and you checked for any
objects owned by the user in dba_objects and there where zero. You even
created
a new user and tried to drop that user and still the errors appeared.
After setting event 10046 (or 942) to see where the statement is failing, by issuing from with the session:
alter session set events '942 trace name errorstack forever'; or
alter session set events '10046 trace name context forever, level 4';
>From the trace file written to the udump directory, you can see that the
DROP
USER <username> command fails with the error 942 and 604 on the statement:
select name from system.aq$_queue_tables where schema=:1;
You verified that the table system.aq$_queue_tables does not exist.
Solution Description:
As user SYS, run CATNOQUEUE.SQL and then CATQUEUE.SQL.
Explanation:
The missing table aq$_queue_tables is causing the errors to appear. This table is needed in order to drop the user.
If the database was created using the database configuration assistant, before running catqueue.sql, SYSTEM's default tablespace is changed from SYSTEM to USERS. When catqueue.sql runs the tables are created in the USERS tablespace not SYSTEM:
If the USERS tablespace is then dropped then the tables are dropped.
By running CATNOQUEUE.SQL you will drop the existing AQ tables and then
running
CATQUEUE.SQL you will redefine the new types and dictionary tables.
On Windows NT, cat*.sql scripts are located in the directory:
%ORACLE_HOME%\rdbms80\admin\
On UNIX, cat*.sql scripts are located in the directory:
$ORACLE_HOME/rdbms/admin
References:
[BUG:752571] RUNNING CATPROC AFTER DROPING USERS TABLESPACE CAUSES ORA-3113 Fixed in 8.1.6.
Search Words:
ora-00942 ora-00604
.
-----Original Message-----
Sent: 01 March 2002 12:23
To: Multiple recipients of list ORACLE-L
Mark,
I am also on 8.1.7 on Win2K but the DROP USER CASCADE works for me.
Dave
-----Original Message-----
Sent: Thursday, February 28, 2002 6:33 PM
To: Multiple recipients of list ORACLE-L
Hi All,
Anyone ever seen this before? 8.1.7 on a Win2K machine.
SQL> select username,
2 account_status
3 from dba_users
4 where username in ('MARK','SCOTT');
USERNAME ACCOUNT_STATUS ------------------------------ -------------------------------- SCOTT OPEN MARK OPEN
SQL> drop user scott cascade;
drop user scott cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
I can't drop *ANY* users! Not even logged in as SYS!
Cheers
Mark
Mark Leith | T: +44 (0)1905 330 281
Sales & Marketing | F: +44 (0)870 127 5283
Cool Tools UK Ltd | E: mark_at_cool-tools.co.uk
===================================================
http://www.cool-tools.co.uk
Maximising throughput & performance
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith INET: mark_at_cool-tools.co.uk Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Farnsworth, Dave INET: DFarnsworth_at_Ashleyfurniture.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robertson Lee - lerobe INET: lerobe_at_acxiom.co.uk Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Fri Mar 01 2002 - 06:58:36 CST
 |
 |