在測試環境中使用某個賬號ESCMOWNER對數據庫進行ALTER操作時,老是報如下錯誤:
ORA-00604: error occurred at recursive SQL level 1
ORA-20016: (ALTER) Action denied as it is a protected object!
ORA-06512: at line 7
不清楚發生了什麼情況,於是想跟蹤當前會話,查看具體原因,查看當前會話的SID為967
SELECT USERENV('SID') FROM DUAL;
以system登錄,查看會話ID=967的SID,SERIAL#
SELECT SID, SERIAL# FROM V$SESSION WHERE SID=967;
跟蹤會話ID、SERIAL#為967,2276的會話,如下所示:
SQL> exec dbms_system.set_sql_trace_in_session(967,2276,true);
PL/SQL procedure successfully completed.
在會話967中啟用跟蹤標識,如下所示
ALTER SESSION SET TRACEFILE_IDENTIFIER='kerry_test'
ORACLE 10g 的跟蹤文件位於$ORACLE_BASE/admin/SID/udump 下,查看跟蹤文件,
[oracle@get-linux01 udump]$ ls *kerry*
epps_ora_1960_kerry_test.trc
[oracle@get-linux01 udump]$ more epps_ora_1960_kerry_test.trc
具體的內容如下所示
查看SELECT * FROM DBA_OBJECTS WHERE OBJECT_NAME='PROTECT_ESCMOWNER_BTR' 發現PROTECT_ESCMOWNER_BTR為觸發器,具體腳本如下所示
CREATE OR REPLACE TRIGGER PROTECT_ESCMOWNER_BTR
BEFORE DDL
ON ESCMOWNER.SCHEMA
BEGIN
if sys.sysevent = 'ALTER' and sys.dictionary_obj_type in ('TRIGGER','VIEW','PACKAGE','PACKAGE BODY','PROCEDURE','FUNCTION','SYNONYM') then
NULL;
elsif sys.sysevent = 'ANALYZE' then
NULL;
else
RAISE_APPLICATION_ERROR(-20016,'('||sys.sysevent||') Action denied as it is a protected object!');
end if;
END PROTECT_ESCMOWNER_BTR;
結論:
這個數據庫觸發器應該是前任DBA設置的,而我一直使用system更新腳本,所以一直沒有觸發該觸發器。禁用該觸發器後問題解決。