由于工程割接，在新数据库上测试imp一个用户需要多少时间，在把imp的报错慢慢修正后，再一次drop user进行重新导入，却发现不能drop user了：
|
1 2 3 4 5 6 |
SQL> drop user newsims cascade; drop user newsims cascade * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables |
但是在当前用户下查询user_query_tables会发现没有记录：
|
1 2 3 |
SQL> select QUEUE_TABLE from USER_QUEUE_TABLES; no rows selected |
既然没有query table，那为什么在drop user的时候，还会报错需要用DBMS_AQADM.DROP_QUEUE_TABLE 去手工清除query table？难道是发生了数据字典不一致的问题？而且，究竟哪些表是query table？
根据现网的生产数据库查询：
|
1 2 3 4 5 6 |
SQL> select QUEUE_TABLE from USER_QUEUE_TABLES; QUEUE_TABLE ------------------------------ AQROUTERLOG_QT PSCONNECTIONFACTORY |
确实发现了2个query table。
忽然发现这2个表之前在做imp的时候，由于如下类似的报错(相关案例见这里)：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
…… . . importing table "PROVINCE_PLACARD_HISTORY" 8358 rows imported IMP-00060: Warning: Skipping table "NEWSIMS"."PSCONNECTIONFACTORY" because object type "SYS"."AQ$_JMS_USERPROPARRAY" does not exist or has different identifier . . importing table "PUNISHAFFECT" 5117 rows imported . . importing table "PUNISHMESSURE" 6 rows imported . . importing table "PUNISHRIGHTWRONG" 0 rows imported …… IMP-00041: Warning: object created with compilation warnings "CREATE FORCE VIEW "NEWSIMS"."AQ$PSCONNECTIONFACTORY" " " ("QUEUE","MSG_ID","CORR_ID","MSG_PRIORITY","MSG_STATE","DELAY","EXPIRATIO" "N","ENQ_TIME","ENQ_USER_ID","ENQ_TXN_ID","DEQ_TIME","DEQ_USER_ID","DEQ_TXN_" "ID","RETRY_COUNT","EXCEPTION_QUEUE_OWNER","EXCEPTION_QUEUE","USER_DATA","OR" "IGINAL_QUEUE_NAME","ORIGINAL_QUEUE_OWNER","EXPIRATION_REASON","SENDER_NAME"" ","SENDER_ADDRESS","SENDER_PROTOCOL","ORIGINAL_MSGID") AS " "SELECT q_name QUEUE, msgid MSG_ID, corrid CORR_ID, priority MSG_PRIORITY, d" "ecode(state, 0, 'READY'," " 1, 'WAIT'," " 2, 'PROCESSED'," " 3, 'EXPIRED') MSG_STATE, delay, expiratio" "n, enq_time, enq_uid ENQ_USER_ID, enq_tid ENQ_TXN_ID, deq_time, deq_uid DEQ" "_USER_ID, deq_tid DEQ_TXN_ID, retry_count, decode (state, 0, exception_qsc" "hema, " " 1, exception_qschema, " " 2, exception_qschema, " " NULL) EXCEPTION_QUEUE_OWNER, decode (sta" "te, 0, exception_queue, " " 1, exception_queue, " " 2, exception_queue, " " NULL) EXCEPTION_QUEUE, user_data, decod" "e (state, 3, " " decode (deq_tid, 'INVALID_TRANSACTION', NULL, " " exception_queue), NULL)" " ORIGINAL_QUEUE_NAME, decode (state, 3, " " decode (deq_tid, 'INVALID_TRANSACTION', NULL, " " exception_qschema), NULL)" " ORIGINAL_QUEUE_OWNER, decode(state, 3, " " decode(deq_time, NULL, " " decode(deq_tid, NULL, 'TIME_EXPIRATION'," " 'INVALID_TRANSACTION', NULL," " 'MAX_RETRY_EXCEEDED'), NULL), NULL) " " EXPIRATION_REASON , sender_name SENDER_NAME, s" "ender_address SENDER_ADDRESS, sender_protocol SENDER_PROTOCOL, dequeue_msgi" "d ORIGINAL_MSGID FROM NEWSIMS.PSCONNECTIONFACTORY WHERE state != 7 AND s" "tate != 9 WITH READ ONLY" …… IMP-00017: following statement failed with ORACLE error 1403: "BEGIN " "SYS.DBMS_AQ_IMP_INTERNAL.IMPORT_QUEUE(HEXTORAW('2E0DD2F817565153E040007F010" "02E63'),'PSCONNECTIONFACTORY','AQ$_PSCONNECTIONFACTORY_E',1,0,0,0,0,'except" "ion queue');" "COMMIT; END;" IMP-00003: ORACLE error 1403 encountered ORA-01403: no data found ORA-06512: at "SYS.DBMS_AQ_SYS_IMP_INTERNAL", line 641 ORA-06512: at "SYS.DBMS_AQ_IMP_INTERNAL", line 60 ORA-06512: at line 2 IMP-00091: Above error occurred on the following function and object: CREATE AQ$_PSCONNECTIONFACTORY_E. Remaining PL/SQL blocks for this object will be skipped. |
于是在imp的时候就加上了TOID_NOVALID的参数：TOID_NOVALIDATE=\(SYS.AQ\$_JMS_MESSAGE,SYS.AQ\$_JMS_USERPROPARRAY,SYS.AQ\$_JMS_TEXT_MESSAGE\)
而且发现这2个表相关的表，在imp的报错中也是出现过的：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SQL> select name FROM user_queues where queue_table LIKE '%AQROUTERLOG_QT%'; NAME ------------------------------ AQ$_AQROUTERLOG_QT_E AQROUTERLOG SQL> SQL> select name FROM user_queues where queue_table LIKE '%PSCONNECTIONFACTOR%'; NAME ------------------------------ AQ$_PSCONNECTIONFACTORY_E PSSPBASICINFO PSADMINBASICINFO PSERRORMESSAGEQUEUE PS1860BASICINFO |
会不会是因为用了TOID_NOVALIDATE没有进行校验，所以实际的query table和数据字典user_query_tables中的情况不一致了？
如果是这样，那么手工清除这2个query table之后就应该能drop user了。
在为上线的新数据库上：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
SQL> conn newsims/passwd Connected. SQL> SQL> SQL> SQL> SQL> alter session set events'10851 trace name context forever,level 2'; Session altered. SQL> EXEC DBMS_AQADM.DROP_QUEUE_TABLE('AQROUTERLOG_QT',TRUE,true); PL/SQL procedure successfully completed. SQL> EXEC DBMS_AQADM.DROP_QUEUE_TABLE('PSCONNECTIONFACTORY',TRUE,true); PL/SQL procedure successfully completed. SQL> SQL> conn / as sysdba Connected. SQL> drop user newsims cascade; User dropped. |
2条评论
小荷又解决了一个故障，good
re 小熊：
其实在处理的时候，我们可以不必去现网查看是哪些queue table，可以利用errorstack level 1去trace到是哪些queue table。
这个方法是在群里面讨论的时候，阿虚告诉我的。
这个方法很妙，不仅可以解决这个问题，对于ora-942 table or view does not exsist的报错，仅仅知道有表或视图不存在，但是不知道是哪个，也可以用这个方法trace出来表名或者视图名。
另外对于exp的报错，也可以用这个来trace，这边也有一个处理的案例。http://www.oracleblog.org/working-case/using-errorstack-to-solve-exp-problem/