oracle11g ddl trace event事件小記
--oracle11g有個功能，可以trace ddl,具體可見：
--構建一個針對scott使用者的trace ddl的觸發器
create or replace trigger trace_ddl
after logon on database
begin
if user like 'SCOTT' then
execute immediate 'alter session set max_dump_file=umlimited';
--為產生的trace檔案作標識，便於查詢定位
execute immediate 'alter session set tracefile_identifier="scott"';--注：scott用引引號括起
--開啟oracle ddl跟蹤事件，即可trace ddl變更
execute immediate 'alter session set events "trace [SQL_DDL]"';--這裡用"雙引號是在execute immediate中
end if;
end;
--但自trace檔案可知trace ddl好像不被啟用或支援,補充一點：測試版本為oracle11g r2
Breaking the connection before proto/dty negotiation, error raised 3113
*** 2013-03-17 19:00:47.202
*** SESSION ID:(139.141) 2013-03-17 19:00:47.202
*** SERVICE NAME:(orcl) 2013-03-17 19:00:47.202
*** MODULE NAME:(sqlplus.exe) 2013-03-17 19:00:47.202
*** ACTION NAME:() 2013-03-17 19:00:47.202
Skipped error 604 during the execution of SYS.TRACE_DDL
*** 2013-03-17 19:00:47.226
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=0, mask=0x0)
----- Error Stack Dump -----
ORA-00604: error occurred at recursive SQL level 1
ORA-02248: invalid option for ALTER SESSION
ORA-06512: at line 3
--是否有關ddl trace的開關未啟用
SQL> select * from x$ksppi k where k.ksppinm like '%enable_ddl_logging%';
ADDR INDX INST_ID KSPPINM KSPPITY KSPPDESC KSPPIFLG KSPPILRMFLG KSPPIHASH
---------------- ---------- ---------- -------------------------------------------------------------------------------- ---------- -------------------------------------------------------------------------------- ---------- ----------- ----------
000007FF04BE5E08 2091 1 enable_ddl_logging 1 enable ddl logging 65792 0 2442221836
SQL> alter system set "enable_ddl_logging"=true scope=spfile;
System altered
SQL> select * from x$ksppi k where k.ksppinm like '%enable_ddl_logging%';
ADDR INDX INST_ID KSPPINM KSPPITY KSPPDESC KSPPIFLG KSPPILRMFLG KSPPIHASH
---------------- ---------- ---------- -------------------------------------------------------------------------------- ---------- -------------------------------------------------------------------------------- ---------- ----------- ----------
000007FF04BE4DA8 2091 1 enable_ddl_logging 1 enable ddl logging 65792 0 2442221836
SQL> select * from x$ksppcv p1,x$ksppi p2 where p1.indx=p2.indx and p1.inst_id=p2.inst_id and p2.ksppinm='enable_ddl_logging';
ADDR INDX INST_ID KSPPSTVL KSPPSTDVL KSPPSTDF KSPPSTVF KSPPSTCMNT ADDR INDX INST_ID KSPPINM KSPPITY KSPPDESC KSPPIFLG KSPPILRMFLG KSPPIHASH
---------------- ---------- ---------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------- ---------- -------------------------------------------------------------------------------- ---------------- ---------- ---------- -------------------------------------------------------------------------------- ---------- -------------------------------------------------------------------------------- ---------- ----------- ----------
000007FF06573140 2091 1 FALSE FALSE TRUE 0 000007FF050D7EE8 2091 1 enable_ddl_logging 1 enable ddl logging 65792 0 2442221836
--經過開啟隱含引數 enable_ddl_logging還是報之前的錯，看來與此引數無關
Skipped error 604 during the execution of SYS.TRACE_DDL
*** 2013-03-17 19:30:00.714
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=0, mask=0x0)
----- Error Stack Dump -----
ORA-00604: error occurred at recursive SQL level 1
ORA-02248: invalid option for ALTER SESSION
ORA-06512: at line 3
--又再分析了下，trace檔案一直報無效的選項即是無效的命令；我又在sqlplus中重試了下,這下用'單引號，結果ok了
SQL> alter session set events 'trace [SQL_DDL]';
Session altered.
--根據上述重修正觸發器如下
create or replace trigger sys.trace_ddl
after logon on database
begin
if user like '%SCOTT%' then
execute immediate 'alter session set max_dump_file=umlimited';
--為產生的trace檔案作標識，便於查詢定位
execute immediate 'alter session set tracefile_identifier="scott"';--注：scott用引引號括起
--開啟oracle ddl跟蹤事件，即可trace ddl變更
execute immediate 'alter session set events '''trace [SQL_DDL]'''';
end if;
end;
/
--又報錯了，不過這下和原錯不一樣，說觸發器無效
*** 2013-03-17 19:36:46.455
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=0, mask=0x0)
----- Error Stack Dump -----
ORA-04098: trigger 'SYS.TRACE_DDL' is invalid and failed re-validation
--觸發器狀態也是正常的喲
SQL> select * from user_triggers ur where ur.trigger_name='TRACE_DDL';
TRIGGER_NAME TRIGGER_TYPE TRIGGERING_EVENT TABLE_OWNER BASE_OBJECT_TYPE TABLE_NAME COLUMN_NAME REFERENCING_NAMES WHEN_CLAUSE STATUS DESCRIPTION ACTION_TYPE TRIGGER_BODY CROSSEDITION BEFORE_STATEMENT BEFORE_ROW AFTER_ROW AFTER_STATEMENT INSTEAD_OF_ROW FIRE_ONCE APPLY_SERVER_ONLY
------------------------------ ---------------- -------------------------------------------------------------------------------- ------------------------------ ---------------- ------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------- -------------------------------------------------------------------------------- ----------- -------------------------------------------------------------------------------- ------------ ---------------- ---------- --------- --------------- -------------- --------- -----------------
TRACE_DDL AFTER EVENT LOGON SYS DATABASE REFERENCING NEW AS NEW OLD AS OLD ENABLED sys.trace_ddl PL/SQL begin NO NO NO NO NO NO NO NO
--還有另一種途徑，alert日誌也可以看到觸發器無效
Errors in file d:\oracle11g_64bit\diag\rdbms\orcl\orcl\trace\orcl_j000_14864.trc:
ORA-04098: 觸發器 'SYS.TRACE_DDL' 無效且未透過重新驗證
Sun Mar 17 19:41:12 2013
create or replace trigger trace_ddl
after logon on database
begin
if user like 'SCOTT' then
ex
Sun Mar 17 19:41:40 2013
Errors in file d:\oracle11g_64bit\diag\rdbms\orcl\orcl\trace\orcl_j000_15248.trc:
ORA-04098: 觸發器 'SYS.TRACE_DDL' 無效且未透過重新驗證
Sun Mar 17 19:49:47 2013
Errors in file d:\oracle11g_64bit\diag\rdbms\orcl\orcl\trace\orcl_j000_13720.trc:
ORA-04098: 觸發器 'SYS.TRACE_DDL' 無效且未透過重新驗證
Sun Mar 17 19:54:02 2013
drop table t_ddl purge
Sun Mar 17 19:56:02 2013
create table t_other(a int)
---忽略一點,在sql developer的sql視窗建立觸發器成功不代表編譯會成功,一編譯：報錯pls-00103,與trace關鍵字有關
---報錯的語句
execute immediate 'alter session set events '''trace [SQL_DDL]'''';
--為產生的trace新增標識便於定位
SQL> alter session set tracefile_identifier='zxy';
--開啟sql trace ddl功能
SQL> alter session set events 'trace [SQL_DDL]';
Session altered.
SQL>
SQL>
--刪除一個表
SQL> drop table t_ddl purge;
Table dropped.
--查詢當前會話
SQL> select sid from v$mystat where rownum=1;
SID
----------
95
--當前會話所在的機器
SQL> select machine from v$session where sid=95;
MACHINE
----------------------------------------------------
WORKGROUP\123-PC
--關閉sql_ddl trace 功能
SQL> alter session set events 'trace [SQL_DDL] off';
Session altered.
--這是產生的trace檔案
DDL begin in opiprs
session id 95 inc 7 pgadep 0 sqlid darh4qvn9cuwx oct 12 txn 0x000007FF09D66590 autocommit 1
----- Current SQL Statement for this session (sql_id=darh4qvn9cuwx) -----這是drop語句對應的會話
drop table t_ddl purge --這是trace到的drop語句
ktagetg_ddl sessionid 95 inc 7 pgadep 0 txn 0x000007FF09D66590 table 69650 mode 6 --txn為事務號,後續為表的物件號及lock mode
ktagetg_ddl sessionid 95 inc 7 pgadep 0 txn 0x000007FF09D66590 table 69650 mode 6
ktagetg_ddl sessionid 95 inc 7 pgadep 0 txn 0x000007FF09D66590 table 69650 mode 6
ktagetg_ddl sessionid 95 inc 7 pgadep 0 txn 0x000007FF09D66590 table 69650 mode 6
DCSTRC: Deleting from deferred_stg$ objn:69650 --這是oracle的系統呼叫語句，說明刪除自系統表deferred_stg$刪除了表物件的記錄
DDL end in opiexe
session id 95 inc 7 pgadep 0 sqlid darh4qvn9cuwx txn 0x000007FF09D66590 autocommit 1 commited 1
--結合這個sql trace功能,可以實現申計功能,結合dba_hist_active_session_history,有效定位到對於物件的ddl操作，保證了資料庫的安全
--當然也可以不用這個sql trace功能,利用userenv也可以實現此功能，只是方式不同
--如下說明execute immediate的語句是正確也可生成trace檔案
SQL> declare
2 begin
3 execute immediate 'alter session set tracefile_identifier="scott"';
4 end;
5 /
PL/SQL procedure successfully completed.
--那麼儲存過程執行正常嗎
create or replace procedure proc_track
is
begin
--execute immediate 'alter session set max_dump_file="umlimited"';
--為產生的trace檔案作標識，便於查詢定位
-- v_sql:=
execute immediate 'alter session set tracefile_identifier="scott"';--注：scott用引引號括起
--開啟oracle ddl跟蹤事件，即可trace ddl變更
execute immediate 'alter session set events "trace [SQL_DDL]"';
end proc_track;
---儲存過程執行報錯
SQL> exec proc_track;
BEGIN proc_track; END;
*
ERROR at line 1:
ORA-02246: missing EVENTS text
ORA-06512: at "SCOTT.PROC_TRACK", line 9
ORA-06512: at line 1
--屢次修正後如下
create or replace procedure proc_track
authid current_user is
begin
--execute immediate 'alter session set max_dump_file="umlimited"';
--為產生的trace檔案作標識，便於查詢定位
-- v_sql:=
execute immediate 'alter session set tracefile_identifier=''''''scott''''''';--注：scott用引引號括起
--開啟oracle ddl跟蹤事件，即可trace ddl變更
execute immediate 'alter session set events ''''''trace [SQL_DDL]''''''';
end proc_track;
--執行依舊報錯，不過錯誤與之前不同
SQL> exec proc_track;
BEGIN proc_track; END;
*
ERROR at line 1:
ORA-49100: Failed to process event statement ['trace [SQL_DDL]']
ORA-49108: Event Name [trace [SQL_DDL]] not found
ORA-06512: at "SCOTT.PROC_TRACK", line 9
ORA-06512: at line 1
---最終版儲存過程如下：
create or replace procedure proc_track
authid current_user is
v_sql varchar2(4000);
begin
--execute immediate 'alter session set max_dump_file="umlimited"';
--為產生的trace檔案作標識，便於查詢定位
-- v_sql:=
v_sql:='alter session set tracefile_identifier="sb"';--tracefile的=後面的值要用"雙引號括起來
execute immediate v_sql;--注：scott用引引號括起
--開啟oracle ddl跟蹤事件，即可trace ddl變更
v_sql:='alter session set events ''trace [SQL_DDL]''';---trace要用兩個''括起來，而非"雙引號括起來,不然報錯
execute immediate v_sql;
end proc_track;
--最終版觸發器,基於資料庫的觸發器必須位於sys使用者下,不然會報許可權不足
create or replace trigger trace_ddl
after logon on database
begin
if sys_context('userenv','session_user')='SCOTT' then
execute immediate 'alter session set events ''trace [SQL_DDL]''';
end if;
end;
---最終產生的戰利品trace，依次記錄了table及procedure的ddl操作
*** TRACE CONTINUED FROM FILE d:\oracle11g_64bit\diag\rdbms\orcl\orcl\trace\orcl_s001_17784_scott.trc ***
DDL begin in opiprs
session id 139 inc 50 pgadep 0 sqlid 2hwg0kgxxb3dq oct 1 txn 0x000007FF09D5C3C8 autocommit 1
----- Current SQL Statement for this session (sql_id=2hwg0kgxxb3dq) -----
create table t_sb(a int)
DCSTRC: Deferred Segment Creation Enabled.
*** 2013-03-17 21:44:30.321
DDL begin in opiprs
session id 139 inc 50 pgadep 0 sqlid cddctnx4dut6f oct 12 txn 0x000007FF09D5C3C8 autocommit 1
----- Current SQL Statement for this session (sql_id=cddctnx4dut6f) -----
drop table t_sb purge
ktagetg_ddl sessionid 139 inc 50 pgadep 0 txn 0x000007FF09D5C3C8 table 69662 mode 6
ktagetg_ddl sessionid 139 inc 50 pgadep 0 txn 0x000007FF09D5C3C8 table 69662 mode 6
ktagetg_ddl sessionid 139 inc 50 pgadep 0 txn 0x000007FF09D5C3C8 table 69662 mode 6
ktagetg_ddl sessionid 139 inc 50 pgadep 0 txn 0x000007FF09D5C3C8 table 69662 mode 6
DCSTRC: Deleting from deferred_stg$ objn:69662
DDL end in opiexe
session id 139 inc 50 pgadep 0 sqlid cddctnx4dut6f txn 0x000007FF09D5C3C8 autocommit 1 commited 1
*** 2013-03-17 21:44:31.745
DDL begin in opiprs
session id 139 inc 50 pgadep 0 sqlid 2hwg0kgxxb3dq oct 1 txn 0x000007FF09D5C3C8 autocommit 1
----- Current SQL Statement for this session (sql_id=2hwg0kgxxb3dq) -----
create table t_sb(a int)
DCSTRC: Deferred Segment Creation Enabled.
ctcdrv
session id 139 inc 50 pgadep 0 sqlid 2hwg0kgxxb3dq DDL on 69665 op-alter_table 0
DCSTRC: Inserting into deferred_stg$ objn:69665
stg:0x000007FF068B2E60 ts: objno:69665 dobjno:69665 pctfree:127 pctused:127 size:32767 initrans:2147483647 maxtrans:2147483647 initial:4294967295 next:4294967295 optimal:4294967295 minextents:2147483647 maxextents:2147483647 pctinc:2147483647 maxins:0 frlins:65535 tabno:0 NOCOMPRESS/
----- Current SQL Statement for this session (sql_id=2hwg0kgxxb3dq) -----
create table t_sb(a int)
*** WARNING: Unable to skip initial frames
DCSTRC: Data Segment for ObjNo: 69665 Not Created
DDL end in opiexe
session id 139 inc 50 pgadep 0 sqlid 2hwg0kgxxb3dq txn 0x000007FF09D5C3C8 autocommit 1 commited 1
*** 2013-03-17 21:45:08.856
DDL begin in opiprs
session id 139 inc 50 pgadep 0 sqlid b5n4df55tuaac oct 24 txn 0x000007FF09D87CE8 autocommit 1
----- Current SQL Statement for this session (sql_id=b5n4df55tuaac) -----
create or replace procedure proc_1_1
as
begin
null;
end;
DDL end in opiexe
session id 139 inc 50 pgadep 0 sqlid b5n4df55tuaac txn 0x000007FF09D87CE8 autocommit 1 commited 1
小結：1,execute immediate中如引用ddl，一定要注意ddl中的轉義符的使用,本例因此花費太多時間
2,oracle11g有很多新特性，值得研究
3,老外的blog相當不錯，有很多好東東.推薦一看
Errors in file d:\oracle11g_64bit\diag\rdbms\orcl\orcl\trace\orcl_ora_17620_scott.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-02248: invalid option for ALTER SESSION
ORA-06512: at line 3
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-756362/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 設定Event事件來追蹤Trace事件
- 轉摘_壇友_oracle trace event追蹤事件完全列表Oracle事件
- Laravel event 事件使用中 記錄的一個小問題Laravel事件
- 設定 oracle event traceOracle
- Javasript學習筆記-Event事件Java筆記事件
- oracle Database Event trace 設定OracleDatabase
- oracle library cache之trace小記Oracle
- c#窗體form(winform)事件event呼叫事件的簡小示例C#ORM事件
- mysql 事件 eventMySql事件
- event事件（1）事件
- Event 10046 - Enable SQL Statement TraceSQL
- oracle dump trace redo幾則方法小記Oracle
- 【TRACE】Oracle跟蹤事件Oracle事件
- 學習筆記之事件迴圈-Event loop筆記事件OOP
- JS中event事件JS事件
- Laravel使用event事件Laravel事件
- Tkinter (44) 事件 Event事件
- oracle Database Event trace 設定【Blog 搬家】OracleDatabase
- zt_dbanotes_Oracle Trace Level Event NumbersOracle
- Oracle11g安全審計–重要帳號的DDL語句操作記錄Oracle
- MySQL入門--EVENT（事件）MySql事件
- 事件迴圈（event loop）事件OOP
- js中事件物件eventJS事件物件
- Mysql event實現定時建表小記MySql
- sql_trace and 10046事件SQL事件
- EVENT:10228 trace application of redo by kcocbkAPP
- Database Triggers and event attributes--DDL/Client EventsDatabaseclient
- JS事件迴圈Event LoopJS事件OOP
- JS 事件機制 Event LoopJS事件OOP
- Javascript 事件迴圈event loopJavaScript事件OOP
- Spring Boot 之事件（Event）Spring Boot事件
- JavaScript事件迴圈(Event Loop)JavaScript事件OOP
- JavaScript基礎之事件eventJavaScript事件
- 瞭解下C# 事件（Event）C#事件
- JS 事件迴圈（Event Loop）JS事件OOP
- oracle wait event 等待事件OracleAI事件
- Oracle 跟蹤事件 set eventOracle事件
- Nodejs事件迴圈小記NodeJS事件