使用logmnr对其它用户的操作执行日志挖掘的四个对比实验

日志挖掘概念：

结论如下：执行日志挖掘操作的可以使用DBA用户或SYSDBA用户，不能使用普通用户。
日志挖掘可以查看到当前用户自己的操作，也可以查看到其它用户的操作。
其它用户已经执行但未提交的操作，也可以查到。
可以挖掘到其它DBA用户或SYSDBA用户的操作。
-----有些语句的返回提示如下面一句连接后的“Connected.”这种为节约篇幅，删了。不要质疑哈哈。
对于DDL操作 ：对于执行DDL前的操作无法挖掘，但是不报错。只显示DDL语句及DDL之后的DML操作。见： logmnr挖掘中间有DDL的操作示例
BYS@ bys001>conn scott/tiger
Connected.
SCOTT@ bys001>desc v$logmnr_contents
ERROR:
ORA-04043: object "SYS"."V_$LOGMNR_CONTENTS" does not exist
证明普通用户不能使用日志挖掘，连这个视图都看不到的。
##############################################

实验1：使用SYSDBA用户对SYS用户进行日志挖掘

在SYSDBA用户下建表插入数据进行日志挖掘。
SCOTT@ bys001>conn / as sysdba
Connected.
SYS@ bys001>select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
? ?? ?? ?? ?? ? 14390465
SYS@ bys001>create table t(a number);
SYS@ bys001>insert into t values(1);
SYS@ bys001>commit;
SYS@ bys001>select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------

? ?? ?? ?? ?? ? 14390508

可以直接使用这样一条语句：select a.group#,a.member,b.status from v$logfile a,v$log b where a.group#=b.group#;

SYS@ bys001>select group#,status from v$log;
? ? GROUP# STATUS
---------- ----------------
? ?? ?? ?1 INACTIVE
? ?? ?? ?2 INACTIVE
? ?? ?? ?3 CURRENT

SYS@ bys001>col member for a50

SYS@ bys001>select group#,member,type from v$logfile;

? ? GROUP# MEMBER? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ???TYPE
---------- -------------------------------------------------- -------
? ?? ?? ?3 /u01/app/oracle/oradata/bys001/redo03.log? ?? ?? ?ONLINE
? ?? ?? ?2 /u01/app/oracle/oradata/bys001/redo02.log? ?? ?? ?ONLINE
? ?? ?? ?1 /u01/app/oracle/oradata/bys001/redo01.log? ?? ?? ?ONLINE
? ?? ?? ?1 /u01/app/oracle/oradata/bys001/redo01a.log? ?? ???ONLINE
SYS@ bys001>execute dbms_logmnr.add_logfile(LogFileName => '/u01/app/oracle/oradata/bys001/redo03.log',Options => dbms_logmnr.new);
PL/SQL procedure successfully completed.
SYS@ bys001>execute dbms_logmnr.start_logmnr(options =>dbms_logmnr.dict_from_online_catalog,startscn =>14390465,endscn =>14390508);
PL/SQL procedure successfully completed.
SYS@ bys001>select operation,sql_redo,sql_undo from v$logmnr_contents where table_name='T';
OPERATION
--------------------------------
SQL_REDO
----------------------------------------------------------------------------------------------------
SQL_UNDO
----------------------------------------------------------------------------------------------------
DDL
create table t(a number);

INSERT
insert into "SYS"."T"("A") values ('1');
delete from "SYS"."T" where "A" = '1' and ROWID = 'AAASuWAABAAAVS5AAA';
#########################################################

实验二：使用SYSDBA用户对普通用户SCOTT下的操作进行挖掘

SYS@ bys001>conn scott/tiger
Connected.
SCOTT@ bys001>create table test(a number);
SCOTT@ bys001>select dbms_flashback.get_system_change_number from dual;
select dbms_flashback.get_system_change_number from dual
? ?? ? *
ERROR at line 1:
ORA-00904: : invalid identifier??? ---普通用户不能查询当前SCN
SCOTT@ bys001>conn / as sysdba
Connected.
SYS@ bys001>select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
? ?? ?? ?? ?? ? 14390661
开始插入一条数据。
SYS@ bys001>conn scott/tiger
Connected.
SCOTT@ bys001>insert into test values(3);
SCOTT@ bys001>commit;
SCOTT@ bys001>conn / as sysdba
Connected.
SYS@ bys001>select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
? ?? ?? ?? ?? ? 14390688
SYS@ bys001>execute dbms_logmnr.add_logfile(LogFileName => '/u01/app/oracle/oradata/bys001/redo03.log',Options => dbms_logmnr.new);
PL/SQL procedure successfully completed.
SYS@ bys001>execute dbms_logmnr.start_logmnr(options =>dbms_logmnr.dict_from_online_catalog,startscn =>14390661,endscn =>14390688);
PL/SQL procedure successfully completed.
SYS@ bys001>select operation,sql_undo from v$logmnr_contents where table_name='TEST';
OPERATION
--------------------------------
SQL_REDO
----------------------------------------------------------------------------------------------------
SQL_UNDO
----------------------------------------------------------------------------------------------------
INSERT
insert into "SCOTT"."TEST"("A") values ('3');
delete from "SCOTT"."TEST" where "A" = '3' and ROWID = 'AAASuXAAEAAAAlGAAA';
####################################################

实验三：使用SYSDBA，SCOTT用户插入数据不提交，可以挖掘到相应日志————也证明了commit和写日志的无关性。

这里需要使用两个会话，因为在同一个SQLPLUS会话下，切换用户会引起COMMIT。-----实验得出
会话一：查询出当前SCN
SYS@ bys001>select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
? ?? ?? ?? ?? ? 14391177
会话二：使用SCOTT用户插入一条记录
SCOTT@ bys001>insert into test values(99);
SCOTT@ bys001>select * from test;
? ?? ?? ?A
----------
? ?? ?? ?3
? ?? ? 333
? ?? ???99
会话一：记录当前SCN
SYS@ bys001>select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
? ?? ?? ?? ?? ? 14391194
SYS@ bys001>execute dbms_logmnr.add_logfile(LogFileName => '/u01/app/oracle/oradata/bys001/redo03.log',startscn =>14391177,endscn =>14391194);
PL/SQL procedure successfully completed.
SYS@ bys001>select operation,sql_undo from v$logmnr_contents where table_name='TEST';
OPERATION
--------------------------------
SQL_REDO
----------------------------------------------------------------------------------------------------
SQL_UNDO
----------------------------------------------------------------------------------------------------
INSERT
insert into "SCOTT"."TEST"("A") values ('99');
delete from "SCOTT"."TEST" where "A" = '99' and ROWID = 'AAASuXAAEAAAAlGAAC';

会话二：回滚之前的插入，证明之前的插入确实是未提交的。
SCOTT@ bys001>rollback;
Rollback complete.
SCOTT@ bys001>select * from test;
? ?? ?? ?A
----------
? ?? ?? ?3
? ?? ? 333
##########################################################

实验四：使用普通DBA用户可以挖掘出SYS用户的操作

SYS@ bys001>conn bys/bys
Connected.
BYS@ bys001>select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
? ?? ?? ?? ?? ? 14391461
BYS@ bys001>conn / as sysdba
SYS@ bys001>insert into t values(9);
SYS@ bys001>commit;
SYS@ bys001>conn bys/bys
BYS@ bys001>select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
? ?? ?? ?? ?? ? 14391482
BYS@ bys001>execute dbms_logmnr.add_logfile (LogFileName => '/u01/app/oracle/oradata/bys001/redo03.log',Options => dbms_logmnr.new);
PL/SQL procedure successfully completed.
BYS@ bys001>execute dbms_logmnr.start_logmnr(options =>dbms_logmnr.dict_from_online_catalog ,startscn =>14391461,endscn =>14391482);
PL/SQL procedure successfully completed.
BYS@ bys001>select operation,sql_redo,sql_undo from v$logmnr_contents where table_name='T';
OPERATION
--------------------------------
SQL_REDO
----------------------------------------------------------------------------------------------------
SQL_UNDO
----------------------------------------------------------------------------------------------------
INSERT
insert into "SYS"."T"("A") values ('9');
delete from "SYS"."T" where "A" = '9' and ROWID = 'AAASuWAABAAAVS5AAB';