今天遇到一則由於Oracle Spatial相關表不存在而導致個別使用者無法刪除的問題。比較有代表性，記錄在此。

1.問題現象
在SYS使用者下刪除一個普通使用者時丟擲ORA-00604和ORA-00942錯誤。具體的報錯資訊即問題現象如下所示。
sys@secdb> drop user SEC_TARGET cascade;
drop user SEC_TARGET 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 11

2.問題分析
由於該問題是由於執行一條SQL語句到導致，因此我們可以跟蹤一下這個SQL語句的執行過程，希望可以得到一些蛛絲馬跡。
1）啟用SQL TRACE跟蹤功能
sys@secdb> alter session set sql_trace=true;

Session altered.

sys@secdb> alter session set events'10046 trace name context forever,level 4';

Session altered.

2）再次執行報錯的SQL語句
sys@secdb> drop user cascade;

sys@secdb> drop user SEC_TARGET cascade;
drop user SEC_TARGET 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 11

3）關閉SQL TRACE跟蹤功能
sys@secdb> alter session set sql_trace=false;

Session altered.

4）此時我們便可以在udump目錄下找到SQL TRACE生成的對應的跟蹤檔案。
使用vi命令檢視跟蹤檔案。擷取部分資訊如下。
secdb@bj-secdb1 /oracle/app/oracle/admin/secdb/udump$ vi secdb_ora_5711.trc
……
=====================
PARSING IN CURSOR #43 len=963 dep=1 uid=49 ct=47 lid=49 tim=1267560510536585 hv=560193968 ad='751138a8'
declare
   stmt varchar2(200);
BEGIN
     if dictionary_obj_type = 'USER' THEN
       stmt := 'DELETE FROM SDO_GEOM_METADATA_TABLE ' ||
     ' WHERE SDO_OWNER = :owner ';
       EXECUTE IMMEDIATE stmt USING dictionary_obj_name;
       stmt := 'DELETE FROM SDO_MAPS_TABLE ' ||
    ' WHERE SDO_OWNER = :owner ';
       EXECUTE IMMEDIATE stmt USING dictionary_obj_name;
       stmt := 'DELETE FROM SDO_STYLES_TABLE ' ||
    ' WHERE SDO_OWNER = :owner ';
       EXECUTE IMMEDIATE stmt USING dictionary_obj_name;
       stmt := 'DELETE FROM SDO_THEMES_TABLE ' ||
   ' WHERE SDO_OWNER = :owner ';
       EXECUTE IMMEDIATE stmt USING dictionary_obj_name;
       stmt := 'DELETE FROM SDO_LRS_METADATA_TABLE ' ||
   ' WHERE SDO_OWNER = :owner ';
       EXECUTE IMMEDIATE stmt USING dictionary_obj_name;
       stmt := 'DELETE FROM SDO_TOPO_METADATA_TABLE ' ||
   ' WHERE SDO_OWNER = :owner ';
       EXECUTE IMMEDIATE stmt USING dictionary_obj_name;
    end if;
end;
END OF STMT
PARSE #43:c=0,e=34,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1267560510536583
BINDS #43:
=====================
PARSING IN CURSOR #39 len=62 dep=2 uid=49 ct=7 lid=49 tim=1267560510536996 hv=1927404519 ad='75113308'
DELETE FROM SDO_GEOM_METADATA_TABLE WHERE SDO_OWNER = :owner
END OF STMT
PARSE #39:c=0,e=189,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=1,tim=1267560510536992
BINDS #39:
kkscoacd
Bind#0
acdty=01 mxl=32(13) mxlc=00 mal=00 scl=00 pre=00
acflg=13 fl2=206001 frm=01 csi=31 siz=32 ff=0
kxsbbbfp=2b8dc5fe6568 bln=32 avl=13 flg=09
value="SEC_TARGET"
EXEC #39:c=1000,e=986,p=0,cr=1,cu=0,mis=1,r=0,dep=2,og=1,tim=1267560510538028
=====================
PARSING IN CURSOR #42 len=53 dep=2 uid=49 ct=7 lid=49 tim=1267560510538223 hv=1486718551 ad='7510cc48'
DELETE FROM SDO_MAPS_TABLE WHERE SDO_OWNER = :owner
……

TRACE跟蹤資訊顯式的很清晰，在執行刪除使用者的過程中執行了幾條DELETE刪除語句，問題便出現在這裡。
經確認其中提到的表SDO_GEOM_METADATA_TABLE資料庫中已不存在。這便是報錯的根本原因。表SDO_GEOM_METADATA_TABLE是Oracle Spatial用到的表。

3.處理方法
既然知道了導致使用者無法刪除的原因是由於找不到表SDO_GEOM_METADATA_TABLE所致。那處理方法便是找回之。
我們可以考慮使用catmd.sql指令碼重新初始化Oracle Spatial用到的表的方法進行恢復。

1）catmd.sql指令碼所在目錄
$ORACLE_HOME/md/admin

2）使用SYS使用者執行catmd.sql指令碼
sys@secdb> @?/md/admin/catmd.sql

……此處省略大量執行資訊……

3）在此嘗試使用者刪除
sys@secdb> drop user SEC_TARGET cascade;

User dropped.

OK，使用者刪除成功。

4.小結
本文展示了一則由於Oracle Spatial相關表不存在而導致個別使用者無法刪除的問題。
這則問題給我們的啟示是：
1）當出現因SQL執行相關的故障時可以考慮結合SQL TRACE功能進行問題分析和處理；
2）任何表面看上去不可思議的現象一定有其不為人知的原因；
3）往往問題原因與問題現象本身有較大出入；
4）作為DBA要對資料中的每個使用者瞭然於心、瞭然於口、瞭然於手！
-- The End --

ORACLE下使用者無法順利刪除問題處理一則-ORA-00604和ORA-00942錯誤

相關文章