logmnr挖掘中间有DDL的操作示例-对于执行DDL前的操作无法挖掘
发布时间:2020-12-14 03:53:35 所属栏目:大数据 来源:网络整理
导读:对于执行DDL前的操作无法挖掘,但是不报错。只显示DDL语句及DDL之后的DML操作。 更多logmnr挖掘DML操作,详见: 使用logmnr使用logmnr对其它用户的操作执行日志挖掘的四个对比实验 BYS@ bys3select dbms_flashback.get_system_change_number from dual; GET_
对于执行DDL前的操作无法挖掘,但是不报错。只显示DDL语句及DDL之后的DML操作。
更多logmnr挖掘DML操作,详见: 使用logmnr使用logmnr对其它用户的操作执行日志挖掘的四个对比实验 BYS@ bys3>select dbms_flashback.get_system_change_number from dual; GET_SYSTEM_CHANGE_NUMBER ------------------------ ???????????????? 3517212 BYS@ bys3>select * from test; OBJECT_NAME? STATUS ------------ ------- 11?????????? 22 10?????????? 15 10?????????? 15 BYS@ bys3 >delete test where status=22; 1 row deleted. BYS@ bys3>commit; Commit complete. BYS@ bys3> truncate table test; Table truncated. BYS@ bys3> insert into test values(9,888); 1 row created. BYS@ bys3>commit; Commit complete. BYS@ bys3>select * from test; OBJECT_NAME? STATUS ------------ ------- 9??????????? 888 BYS@ bys3>select dbms_flashback.get_system_change_number from dual; GET_SYSTEM_CHANGE_NUMBER ------------------------ ???????????????? 3517265 ############# LOGMNR进行挖掘:--只看到DDL语句及之后的DML。 BYS@ bys3>col member for a30 BYS@ bys3>select a.group#,a.member,b.status from v$logfile a,v$log b where a.group#=b.group#; ??? GROUP# MEMBER???????????????????????? STATUS ---------- ------------------------------ ---------------- ???????? 1 /u01/oradata/bys3/redo01.log?? INACTIVE ???????? 2 /u01/oradata/bys3/redo02.log?? CURRENT ???????? 3 /u01/oradata/bys3/redo03.log?? INACTIVE BYS@ bys3>execute dbms_logmnr.add_logfile(LogFileName => '/u01/oradata/bys3/redo02.log',Options => dbms_logmnr.new); PL/SQL procedure successfully completed. BYS@ bys3>execute dbms_logmnr.start_logmnr(options =>dbms_logmnr.dict_from_online_catalog,startscn =>3517212,endscn =>3517265); PL/SQL procedure successfully completed. BYS@ bys3>col sql_undo for a60 BYS@ bys3>col sql_redo for a60 BYS@ bys3>set linesize 200 BYS@ bys3>col operation for a15 BYS@ bys3>select operation,sql_redo,sql_undo from v$logmnr_contents where table_name='TEST'; OPERATION?????? SQL_REDO???????????????????????????????????????????????????? SQL_UNDO --------------- ------------------------------------------------------------ ------------------------------------------------------------ DDL???????????? truncate table test; INSERT????????? insert into "BYS"."TEST"("OBJECT_NAME","STATUS") values ('9' delete from "BYS"."TEST" where "OBJECT_NAME" = '9' and "STAT ???????????????,'888');???????????????????????????????????????????????????? US" = '888' and ROWID = 'AAAFdlAAEAAAAD1AAA'; (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |