删除用户的案例
今天在群里有个兄弟问了一个问题，再删除用户时，遇到一下错误
SQL> drop user mi cascade;
drop user mi cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
这个时候就开SQL_TRACE，看看到底错处在哪？
SQL>alter session set sql_trace=true;
SQL> drop user mi cascade;
drop user mi cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
SQL>alter session set sql_trace=false;
然后查看跟踪后的文件，并tkprof格式化了
发现如下报错
PARSE ERROR #3:len=59 dep=1 uid=0 oct=3 lid=0 tim=611757714719 err=942
select name from system.aq$_queue_tables where schema = :1
EXEC #1:c=50000,e=713441,p=0,cr=2405,cu=0,mis=0,r=0,dep=0,og=1,tim=611757715008
ERROR #1:err=604 tim=3716596852
*** 2007-12-04 14:27:25.207
错误里提示缺少system.aq$_queue_tables视图
然后
select * from system.aq$_queue_tables 报错确实不存在，
看来需要重建，从创建视图的catproc.sql里找到
CREATE TABLE “SYSTEM”.”AQ$_QUEUE_TABLES”
( “SCHEMA” VARCHAR2(30) NOT NULL ENABLE,
“NAME” VARCHAR2(30) NOT NULL ENABLE,
“UDATA_TYPE” NUMBER NOT NULL ENABLE,
“OBJNO” NUMBER NOT NULL ENABLE,
“FLAGS” NUMBER NOT NULL ENABLE,
“SORT_COLS” NUMBER NOT NULL ENABLE,
“TIMEZONE” VARCHAR2(64),
“TABLE_COMMENT” VARCHAR2(2000),
CONSTRAINT “AQ$_QUEUE_TABLES_PRIMARY” PRIMARY KEY (”OBJNO”)
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE “SYSTEM” ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE “SYSTEM” ;
然后运行创建，
然后再次删除用户时，还是报错，不过这次报错不一样
SQL> drop user mi cascade;
drop user mi cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00054: resource busy and acquire with NOWAIT specified
看错误提示，有用户在使用MI的OBJECT，
所以查看lock
SQL> select * from v$lock where type=’TM’ and id1 in (select object_id from dba
_objects where owner=’MI’);
ADDR KADDR SID TY ID1 ID2 LMODE
—————- —————- ———- — ———- ———- ———-
REQUEST CTIME BLOCK
———- ———- ———-
0700000034656DE8 0700000034656E10 320 TM 57541 0 4
0 11 0
看来真有用户在使用，进一步查看相关信息
根据SID 和OBJECT_ID
SQL> select username from v$session where sid=320;
select object_name from dba_objects where object_id=57541;
USERNAME
——————————
SYSTEM
SQL>
OBJECT_NAME
——————————————————————————–
KC44
可能是开发人员忘记提交
所以根据SID，差SERIAL#,直接在SQL里干掉SESSION
select sid,serial# from v$session where sid=320;
然后执行alter system kill session (’320,serial#’);
SQL> alter system kill session ‘320,968′;
System altered.
SQL> drop user mi cascade;
drop user mi cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
ORA-06512: at line 7
结果还是不行
待续。。。。。
相关连接：
http://www.eygle.com/case/sql_trace_2.htm
http://www.itpub.net/viewthread.php?tid=455405&extra=&page=2
