--oracle11g有个功能，可以trace ddl,具体可见：
http://oraclue.com/2009/08/20/tracking-ddl-changes-in-11g/
--构建一个针对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/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-756362/
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
