LOGMINER的使用
1。 准备工作：
检查是否存在可写目录：
SQL> show parameter utl
NAME TYPE VALUE
------------------------------------ ----------- ---------------------
utl_file_dir string
可见，目前的参数文件没有指定一个可写目录，需要先SHUTDOWN IMMEDIATE 数据库，
然后，在参数文件中加入UTL_FILE_DIR='C:/temp' 参数，这个参数是静态参数，所以
必须关闭数据库进行修改，如果使用ALTER SYSTEM SET 命令进行修改就会报错：
SQL> ALTER SYSTEM SET UTL_FILE_DIR='C:/temp';
ALTER SYSTEM SET UTL_FILE_DIR='C:/temp'
*
ERROR 位于第 1 行:
ORA-02095: 无法修改指定的初始化参数
修改了参数文件后，使用
STARTUP PFILE='%ORACLE_HOME/pfile/init.ora' 启动数据库
然后，为了以后数据库可以按照缺省方式启动，即STARTUP，需要修改SPFILE 文件：
CREATE SPFILE FROM PFILE='%ORACLE_HOME/pfile/init.ora';
通常还需要做一个SPFILE 的备份：
CREATE SPFILE='%ORACLE_HOME/pfile/spinit.ora'; FROM PFILE='%ORACLE_HOME/pfile/init.ora';
现在就修改好了，可以通过下面的查寻看出这个参数已经启用了：
SQL> show parameter UTL
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir string C:/TEMP
SQL> show parameter SPFILE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string %ORACLE_HOME%/DATABASE/SPFILE%
ORACLE_SID%.ORA
下面，如果使用SYS 用户登陆，检验是否存在下面的包：
SQL> DESC SYS.DBMS_LOGMNR_D
Element Type
------------------ ---------
STORE_IN_FLAT_FILE CONSTANT
STORE_IN_REDO_LOGS CONSTANT
BUILD PROCEDURE
SET_TABLESPACE PROCEDURE
看来是存在的，一般缺省安装就会有了，呵呵，如果没有，需要
运行$ORACLE_HOME/rdbms/admin/dbmslmd.sql
SQL> desc sys.dbms_logmnr
Element Type
------------------------ ---------
NEW CONSTANT
REMOVEFILE CONSTANT
ADDFILE CONSTANT
NO_DICT_RESET_ONSELECT CONSTANT
COMMITTED_DATA_ONLY CONSTANT
SKIP_CORRUPTION CONSTANT
DDL_DICT_TRACKING CONSTANT
DICT_FROM_ONLINE_CATALOG CONSTANT
DICT_FROM_REDO_LOGS CONSTANT
NO_SQL_DELIMITER CONSTANT
PRINT_PRETTY_SQL CONSTANT
CONTINUOUS_MINE CONSTANT
LENGTH TYPE
THREADID TYPE
VALID_SQL CONSTANT
INVALID_SQL CONSTANT
HOLE_IN_LOGSTREAM CONSTANT
CORRUPTED_BLK_IN_REDO CONSTANT
LOGFILENAMETEMPLATE VARIABLE
LOGFILENAME TYPE
LOGFILEDESCTEMPLATE VARIABLE
LOGFILEDESCRIPTION TYPE
START_LOGMNR PROCEDURE
ADD_LOGFILE PROCEDURE
END_LOGMNR PROCEDURE
COLUMN_PRESENT FUNCTION
MINE_VALUE FUNCTION
SQL>
看来是存在的，一般缺省安装就会有了，呵呵，如果没有，需要
运行$ORACLE_HOME/rdbms/admin/dbmslm.sql
然后，可以直接使用下面的命令提取数据字典到一个指定的平面文件：
SQL> exec dbms_logmnr_d.build('data_dicitonary.ora','c:/temp', options => DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);
PL/SQL procedure successfully completed
SQL>
如果不是SYS用户，需要显示的给使用LOGMINER 的用户授予一个权限和一个角色：
SQL> grant execute on SYS.DBMS_LOGMNR TO LUNAR;
授权成功。
SQL> GRANT EXECUTE_CATALOG_ROLE TO LUNAR;
授权成功。
SQL>
SQL> conn lunar/lunar
Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0
Connected as lunar
SQL> show user
User is "lunar"
SQL> exec sys.dbms_logmnr_d.build('data_dicitonary_lunar.ora','c:/temp', options => sys.DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);
PL/SQL procedure successfully completed
SQL>
好了，现在就可以分析了。
2。 创建分析日志列表
要创建可用的重作分析日至，需要用NEW 选项运行DBMS_LOGMNR.ADD_LOGFILE:
SQL> EXECUTE sys.DBMS_LOGMNR.ADD_LOGFILE(LogFileName=> 'E:/oracle/admin/test/arch/ARCH2.ARC', options=>sys.dbms_logmnr.new);
PL/SQL procedure successfully completed
SQL>
在做点数据来测试以下，呵呵：
F:/>sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on 星期日 12月 29 17:14:58 2002
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn lunar/lunar
已连接。
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST TABLE
SQL> select * from test;
LOGONDATE USERNAME
---------- ------------------------------
aaaaa
SQL> alter table test modify logondate varchar2(30);
表已更改。
SQL> insert into test values
2 (to_char(sysdate,'yyyy-mm-dd hh24:mm:ss'),'lunar');
已创建 1 行。
SQL> commit;
提交完成。
SQL> select * from test;
LOGONDATE USERNAME
------------------------------ ------------------------------
aaaaa
2002-12-29 17:12:12 lunar
SQL>
下面把这些改变归档看看，呵呵（必须用SYS 登陆才能执行ARCHIVE LOG LIST）：
SQL> alter system archive log current;
系统已更改。
SQL> conn lunar/lunar as sysdba
已连接。
SQL> show user
USER 为"SYS"
SQL> archive log list
数据库日志模式 存档模式
自动存档 启用
存档终点 E:/oracle/admin/test/arch
最早的概要日志序列 4
下一个存档日志序列 5
当前日志序列 5
SQL>
现在刚刚归档的是日至4，把日至4加入到日志分析列表中：
SQL> conn lunar/lunar
Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0
Connected as lunar
SQL> show user
User is "lunar"
SQL>
SQL> EXECUTE sys.DBMS_LOGMNR.ADD_LOGFILE(LogFileName=> 'E:/oracle/admin/test/arch/ARCH4.ARC');
PL/SQL procedure successfully completed
SQL>
查寻以下，看看是不是刚才放到日志分析列表中的两个日至都在了：
SQL> select * from v$logmnr_logs;
LOG_ID FILENAME LOW_TIME HIGH_TIME DB_ID DB_NAME RESET_SCN RESET_SCN_TIME THREAD_ID THREAD_SQN LOW_SCN NEXT_SCN DICTIONARY_BEGIN DICTIONARY_END INFO STATUS
---------- -------------------------------------------------------------------------------- ----------- ----------- ---------- -------- ---------- -------------- ---------- ---------- ---------- ---------- ---------------- -------------- -------------------------------- ----------
2 E:/oracle/admin/test/arch/ARCH2.ARC 2002-12-26 2002-12-29 1683316539 TEST 94238 2002-12-26 22: 1 2 114529 134735 NO NO 8
4 E:/oracle/admin/test/arch/ARCH4.ARC 2002-12-29 2002-12-29 1683316539 TEST 94238 2002-12-26 22: 1 4 142746 142965 NO NO 8
SQL>
3。 开始分析日至
首先设定日期时间的格式：
SQL> alter session set nls_date_format='YYYY-MM-DD:HH24:MI:SS;';
Session altered
SQL>
SQL> execute sys.dbms_logmnr.start_logmnr(DictFileName=>'C:/temp/data_dicitonary_lunar.ora');
PL/SQL procedure successfully completed
SQL>
现在，就可以查询v$logmnr_contents 数据字典，分析数据库系统都做了什么改变了。
SQL> desc v$logmnr_contents
TIMESTAMP=SQL Statement执行的时间
USERNAME=执行此SQL Statement的User
SEG_NAME=SQL Statement所参考到的segment名字
OPERATION=SQL的指令类型(start,commit,insert,delete,update,internal)
SCN= SQL Statement执行的SCN(system change number)
SQL_REDO=曾经执行过的SQL Statement
SQL_UNDO=相对sql_redo的反向SQL Statement(由Oracle产生)
可以用这种下面的语句确定谁作了什么：
select to_char(timestamp,'yyyy-mm-dd hh24:mm:ss'),
operation,
sql_redo
from v$logmnr_contents
where username='LUNAR';
如：
SQL> select to_char(timestamp,'yyyy-mm-dd hh24:mm:ss'),
2 operation,
3 sql_redo
4 from v$logmnr_contents
5 where username='LUNAR';
TO_CHAR(TIMESTAMP,'YYYY-MM-DDH OPERATION SQL_REDO
------------------------------ -------------------------------- --------------------------------------------------------------------------------
2002-12-29 17:12:34 START set transaction read write;
2002-12-29 17:12:34 UNSUPPORTED Unsupported
2002-12-29 17:12:35 COMMIT commit;
2002-12-29 17:12:31 START set transaction read write;
2002-12-29 17:12:31 DELETE delete from "SYS"."IDL_UB1$" where "OBJ#" = '6337' and "PART" = '0' and "VERSION
2002-12-29 17:12:31 INTERNAL
2002-12-29 17:12:31 DELETE delete from "SYS"."IDL_CHAR$" where "OBJ#" = '6337' and "PART" = '0' and "VERSIO
2002-12-29 17:12:31 INTERNAL
2002-12-29 17:12:31 DELETE delete from "SYS"."IDL_UB2$" where "OBJ#" = '6337' and "PART" = '0' and "VERSION
2002-12-29 17:12:31 INTERNAL
2002-12-29 17:12:31 DELETE delete from "SYS"."IDL_UB2$" where "OBJ#" = '6337' and "PART" = '0' and "VERSION
2002-12-29 17:12:31 INTERNAL
2002-12-29 17:12:31 DELETE delete from "SYS"."IDL_SB4$" where "OBJ#" = '6337' and "PART" = '0' and "VERSION
2002-12-29 17:12:31 INTERNAL
2002-12-29 17:12:31 DELETE delete from "SYS"."IDL_SB4$" where "OBJ#" = '6337' and "PART" = '0' and "VERSION
2002-12-29 17:12:31 INTERNAL
2002-12-29 17:12:31 DDL alter table test modify logondate varchar2(30);
2002-12-29 17:12:31 UPDATE update "SYS"."TAB$" set "DATAOBJ#" = '6337', "TS#" = '6', "FILE#" = '6', "BLOCK#
2002-12-29 17:12:31 UPDATE update "SYS"."COL$" set "COL#" = '1', "SEGCOL#" = '1', "SEGCOLLENGTH" = '30', "T
2002-12-29 17:12:31 UPDATE update "SYS"."OBJ$" set "OBJ#" = '6337', "DATAOBJ#" = '6337', "TYPE#" = '2', "CT
TO_CHAR(TIMESTAMP,'YYYY-MM-DDH OPERATION SQL_REDO
------------------------------ -------------------------------- --------------------------------------------------------------------------------
2002-12-29 17:12:31 COMMIT commit;
2002-12-29 17:12:12 START set transaction read write;
2002-12-29 17:12:12 INSERT insert into "LUNAR"."TEST"("LOGONDATE","USERNAME") values (TO_DATE('5052-48-50:4
2002-12-29 17:12:16 COMMIT commit;
24 rows selected
SQL>
结合sql_redo及username域看到底是谁在做什么。
SQL> select sql_redo,sql_undo from v$logmnr_contents;
SQL_REDO SQL_UNDO
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
set transaction read write;
update "SYS"."UNDO$" set "NAME" = '_SYSSMU1$', "USER#" = '1', "FILE#" = '2', "BL update "SYS"."UNDO$" set "NAME" = '_SYSSMU1$', "USER#" = '1', "FILE#" = '2', "BL
commit;
set transaction read write;
update "SYS"."UNDO$" set "NAME" = '_SYSSMU2$', "USER#" = '1', "FILE#" = '2', "BL update "SYS"."UNDO$" set "NAME" = '_SYSSMU2$', "USER#" = '1', "FILE#" = '2', "BL
commit;
set transaction read write;
update "SYS"."UNDO$" set "NAME" = '_SYSSMU3$', "USER#" = '1', "FILE#" = '2', "BL update "SYS"."UNDO$" set "NAME" = '_SYSSMU3$', "USER#" = '1', "FILE#" = '2', "BL
commit;
set transaction read write;
update "SYS"."UNDO$" set "NAME" = '_SYSSMU4$', "USER#" = '1', "FILE#" = '2', "BL update "SYS"."UNDO$" set "NAME" = '_SYSSMU4$', "USER#" = '1', "FILE#" = '2', "BL
commit;
set transaction read write;
update "SYS"."UNDO$" set "NAME" = '_SYSSMU5$', "USER#" = '1', "FILE#" = '2', "BL update "SYS"."UNDO$" set "NAME" = '_SYSSMU5$', "USER#" = '1', "FILE#" = '2', "BL
commit;
set transaction read write;
update "SYS"."UNDO$" set "NAME" = '_SYSSMU6$', "USER#" = '1', "FILE#" = '2', "BL update "SYS"."UNDO$" set "NAME" = '_SYSSMU6$', "USER#" = '1', "FILE#" = '2', "BL
commit;
set transaction read write;
update "SYS"."UNDO$" set "NAME" = '_SYSSMU7$', "USER#" = '1', "FILE#" = '2', "BL update "SYS"."UNDO$" set "NAME" = '_SYSSMU7$', "USER#" = '1', "FILE#" = '2', "BL
SQL_REDO SQL_UNDO
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
commit;
set transaction read write;
update "SYS"."UNDO$" set "NAME" = '_SYSSMU8$', "USER#" = '1', "FILE#" = '2', "BL update "SYS"."UNDO$" set "NAME" = '_SYSSMU8$', "USER#" = '1', "FILE#" = '2', "BL
commit;
set transaction read write;
update "SYS"."UNDO$" set "NAME" = '_SYSSMU9$', "USER#" = '1', "FILE#" = '2', "BL update "SYS"."UNDO$" set "NAME" = '_SYSSMU9$', "USER#" = '1', "FILE#" = '2', "BL
commit;
set transaction read write;
update "SYS"."UNDO$" set "NAME" = '_SYSSMU10$', "USER#" = '1', "FILE#" = '2', "B update "SYS"."UNDO$" set "NAME" = '_SYSSMU10$', "USER#" = '1', "FILE#" = '2', "B
commit;
set transaction read write;
update "SYS"."UNDO$" set "NAME" = '_SYSSMU1$', "USER#" = '1', "FILE#" = '2', "BL update "SYS"."UNDO$" set "NAME" = '_SYSSMU1$', "USER#" = '1', "FILE#" = '2', "BL
commit;
set transaction read write;
update "SYS"."UNDO$" set "NAME" = '_SYSSMU1$', "USER#" = '1', "FILE#" = '2', "BL update "SYS"."UNDO$" set "NAME" = '_SYSSMU1$', "USER#" = '1', "FILE#" = '2', "BL
commit;
set transaction read write;
update "SYS"."UNDO$" set "NAME" = '_SYSSMU2$', "USER#" = '1', "FILE#" = '2', "BL update "SYS"."UNDO$" set "NAME" = '_SYSSMU2$', "USER#" = '1', "FILE#" = '2', "BL
commit;
set transaction read write;
update "SYS"."UNDO$" set "NAME" = '_SYSSMU2$', "USER#" = '1', "FILE#" = '2', "BL update "SYS"."UNDO$" set "NAME" = '_SYSSMU2$', "USER#" = '1', "FILE#" = '2', "BL
SQL_REDO SQL_UNDO
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
commit;
set transaction read write;
update "SYS"."UNDO$" set "NAME" = '_SYSSMU3$', "USER#" = '1', "FILE#" = '2', "BL update "SYS"."UNDO$" set "NAME" = '_SYSSMU3$', "USER#" = '1', "FILE#" = '2', "BL
commit;
set transaction read write;
update "SYS"."UNDO$" set "NAME" = '_SYSSMU3$', "USER#" = '1', "FILE#" = '2', "BL update "SYS"."UNDO$" set "NAME" = '_SYSSMU3$', "USER#" = '1', "FILE#" = '2', "BL
commit;
set transaction read write;
update "SYS"."UNDO$" set "NAME" = '_SYSSMU4$', "USER#" = '1', "FILE#" = '2', "BL update "SYS"."UNDO$" set "NAME" = '_SYSSMU4$', "USER#" = '1', "FILE#" = '2', "BL
commit;
set transaction read write;
update "SYS"."UNDO$" set "NAME" = '_SYSSMU4$', "USER#" = '1', "FILE#" = '2', "BL update "SYS"."UNDO$" set "NAME" = '_SYSSMU4$', "USER#" = '1', "FILE#" = '2', "BL
commit;
set transaction read write;
update "SYS"."UNDO$" set "NAME" = '_SYSSMU5$', "USER#" = '1', "FILE#" = '2', "BL update "SYS"."UNDO$" set "NAME" = '_SYSSMU5$', "USER#" = '1', "FILE#" = '2', "BL
commit;
set transaction read write;
update "SYS"."UNDO$" set "NAME" = '_SYSSMU5$', "USER#" = '1', "FILE#" = '2', "BL update "SYS"."UNDO$" set "NAME" = '_SYSSMU5$', "USER#" = '1', "FILE#" = '2', "BL
commit;
set transaction read write;
update "SYS"."UNDO$" set "NAME" = '_SYSSMU6$', "USER#" = '1', "FILE#" = '2', "BL update "SYS"."UNDO$" set "NAME" = '_SYSSMU6$', "USER#" = '1', "FILE#" = '2', "BL
SQL_REDO SQL_UNDO
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
commit;
set transaction read write;
update "SYS"."UNDO$" set "NAME" = '_SYSSMU6$', "USER#" = '1', "FILE#" = '2', "BL update "SYS"."UNDO$" set "NAME" = '_SYSSMU6$', "USER#" = '1', "FILE#" = '2', "BL
commit;
set transaction read write;
update "SYS"."UNDO$" set "NAME" = '_SYSSMU7$', "USER#" = '1', "FILE#" = '2', "BL update "SYS"."UNDO$" set "NAME" = '_SYSSMU7$', "USER#" = '1', "FILE#" = '2', "BL
commit;
set transaction read write;
update "SYS"."UNDO$" set "NAME" = '_SYSSMU7$', "USER#" = '1', "FILE#" = '2', "BL update "SYS"."UNDO$" set "NAME" = '_SYSSMU7$', "USER#" = '1', "FILE#" = '2', "BL
commit;
set transaction read write;
update "SYS"."UNDO$" set "NAME" = '_SYSSMU8$', "USER#" = '1', "FILE#" = '2', "BL update "SYS"."UNDO$" set "NAME" = '_SYSSMU8$', "USER#" = '1', "FILE#" = '2', "BL
commit;
set transaction read write;
update "SYS"."UNDO$" set "NAME" = '_SYSSMU8$', "USER#" = '1', "FILE#" = '2', "BL update "SYS"."UNDO$" set "NAME" = '_SYSSMU8$', "USER#" = '1', "FILE#" = '2', "BL
commit;
set transaction read write;
update "SYS"."UNDO$" set "NAME" = '_SYSSMU9$', "USER#" = '1', "FILE#" = '2', "BL update "SYS"."UNDO$" set "NAME" = '_SYSSMU9$', "USER#" = '1', "FILE#" = '2', "BL
commit;
set transaction read write;
update "SYS"."UNDO$" set "NAME" = '_SYSSMU9$', "USER#" = '1', "FILE#" = '2', "BL update "SYS"."UNDO$" set "NAME" = '_SYSSMU9$', "USER#" = '1', "FILE#" = '2', "BL
SQL_REDO SQL_UNDO
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
commit;
set transaction read write;
update "SYS"."UNDO$" set "NAME" = '_SYSSMU10$', "USER#" = '1', "FILE#" = '2', "B update "SYS"."UNDO$" set "NAME" = '_SYSSMU10$', "USER#" = '1', "FILE#" = '2', "B
commit;
set transaction read write;
update "SYS"."UNDO$" set "NAME" = '_SYSSMU10$', "USER#" = '1', "FILE#" = '2', "B update "SYS"."UNDO$" set "NAME" = '_SYSSMU10$', "USER#" = '1', "FILE#" = '2', "B
commit;
set transaction read write;
Unsupported Unsupported
commit;
set transaction read write;
commit;
set transaction read write;
commit;
set transaction read write;
commit;
set transaction read write;
commit;
set transaction read write;
commit;
set transaction read write;
SQL_REDO SQL_UNDO
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
commit;
set transaction read write;
commit;
set transaction read write;
commit;
set transaction read write;
commit;
set transaction read write;
commit;
set transaction read write;
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '173' and "INTCOL#" update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '173' and "INTCOL#"
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '178' and "INTCOL#" update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '178' and "INTCOL#"
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '230' and "INTCOL#" update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '230' and "INTCOL#"
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '234' and "INTCOL#" update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '234' and "INTCOL#"
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '240' and "INTCOL#" update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '240' and "INTCOL#"
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '245' and "INTCOL#" update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '245' and "INTCOL#"
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '249' and "INTCOL#" update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '249' and "INTCOL#"
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '253' and "INTCOL#" update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '253' and "INTCOL#"
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '264' and "INTCOL#" update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '264' and "INTCOL#"
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '321' and "INTCOL#" update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '321' and "INTCOL#"
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '326' and "INTCOL#" update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '326' and "INTCOL#"
SQL_REDO SQL_UNDO
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '331' and "INTCOL#" update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '331' and "INTCOL#"
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '336' and "INTCOL#" update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '336' and "INTCOL#"
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '341' and "INTCOL#" update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '341' and "INTCOL#"
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '346' and "INTCOL#" update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '346' and "INTCOL#"
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '460' and "INTCOL#" update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '460' and "INTCOL#"
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '463' and "INTCOL#" update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '463' and "INTCOL#"
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '463' and "INTCOL#" update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '463' and "INTCOL#"
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '496' and "INTCOL#" update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '496' and "INTCOL#"
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '507' and "INTCOL#" update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '507' and "INTCOL#"
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '519' and "INTCOL#" update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '519' and "INTCOL#"
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '555' and "INTCOL#" update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '555' and "INTCOL#"
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '555' and "INTCOL#" update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '555' and "INTCOL#"
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '555' and "INTCOL#" update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '555' and "INTCOL#"
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '2941' and "INTCOL# update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '2941' and "INTCOL#
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '2948' and "INTCOL# update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '2948' and "INTCOL#
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '3477' and "INTCOL# update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '3477' and "INTCOL#
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '3517' and "INTCOL# update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '3517' and "INTCOL#
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '3517' and "INTCOL# update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '3517' and "INTCOL#
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '3637' and "INTCOL# update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '3637' and "INTCOL#
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '3803' and "INTCOL# update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '3803' and "INTCOL#
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '3861' and "INTCOL# update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '3861' and "INTCOL#
SQL_REDO SQL_UNDO
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '3871' and "INTCOL# update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '3871' and "INTCOL#
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '3889' and "INTCOL# update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '3889' and "INTCOL#
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '3889' and "INTCOL# update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '3889' and "INTCOL#
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '3889' and "INTCOL# update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '3889' and "INTCOL#
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '3898' and "INTCOL# update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '3898' and "INTCOL#
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '3898' and "INTCOL# update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '3898' and "INTCOL#
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '3898' and "INTCOL# update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '3898' and "INTCOL#
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '4438' and "INTCOL# update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '4438' and "INTCOL#
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '4442' and "INTCOL# update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '4442' and "INTCOL#
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '4446' and "INTCOL# update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '4446' and "INTCOL#
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '4450' and "INTCOL# update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '4450' and "INTCOL#
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '5435' and "INTCOL# update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '5435' and "INTCOL#
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '5448' and "INTCOL# update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '5448' and "INTCOL#
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '5463' and "INTCOL# update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '5463' and "INTCOL#
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '5489' and "INTCOL# update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '5489' and "INTCOL#
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '5500' and "INTCOL# update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '5500' and "INTCOL#
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '5507' and "INTCOL# update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '5507' and "INTCOL#
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '6004' and "INTCOL# update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '6004' and "INTCOL#
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '6030' and "INTCOL# update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '6030' and "INTCOL#
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '6288' and "INTCOL# update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '6288' and "INTCOL#
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '6288' and "INTCOL# update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '6288' and "INTCOL#
SQL_REDO SQL_UNDO
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '6293' and "INTCOL# update "SYS"."LOB$" set "RETENTION" = '10800' where "OBJ#" = '6293' and "INTCOL#
commit;
set transaction read write;
alter database open;
commit;
set transaction read write;
Unsupported Unsupported
commit;
set transaction read write;
Unsupported Unsupported
commit;
set transaction read write;
Unsupported Unsupported
commit;
set transaction read write;
delete from "SYS"."IDL_UB1$" where "OBJ#" = '6337' and "PART" = '0' and "VERSION insert into "SYS"."IDL_UB1$"("OBJ#","PART","VERSION","PIECE#","LENGTH","PIECE")
delete from "SYS"."IDL_CHAR$" where "OBJ#" = '6337' and "PART" = '0' and "VERSIO insert into "SYS"."IDL_CHAR$"("OBJ#","PART","VERSION","PIECE#","LENGTH","PIECE")
delete from "SYS"."IDL_UB2$" where "OBJ#" = '6337' and "PART" = '0' and "VERSION insert into "SYS"."IDL_UB2$"("OBJ#","PART","VERSION","PIECE#","LENGTH","PIECE")
SQL_REDO SQL_UNDO
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
delete from "SYS"."IDL_UB2$" where "OBJ#" = '6337' and "PART" = '0' and "VERSION insert into "SYS"."IDL_UB2$"("OBJ#","PART","VERSION","PIECE#","LENGTH","PIECE")
delete from "SYS"."IDL_SB4$" where "OBJ#" = '6337' and "PART" = '0' and "VERSION insert into "SYS"."IDL_SB4$"("OBJ#","PART","VERSION","PIECE#","LENGTH","PIECE")
delete from "SYS"."IDL_SB4$" where "OBJ#" = '6337' and "PART" = '0' and "VERSION insert into "SYS"."IDL_SB4$"("OBJ#","PART","VERSION","PIECE#","LENGTH","PIECE")
alter table test modify logondate varchar2(30);
update "SYS"."TAB$" set "DATAOBJ#" = '6337', "TS#" = '6', "FILE#" = '6', "BLOCK# update "SYS"."TAB$" set "DATAOBJ#" = '6337', "TS#" = '6', "FILE#" = '6', "BLOCK#
update "SYS"."COL$" set "COL#" = '1', "SEGCOL#" = '1', "SEGCOLLENGTH" = '30', "T update "SYS"."COL$" set "COL#" = '1', "SEGCOL#" = '1', "SEGCOLLENGTH" = '7', "TY
update "SYS"."OBJ$" set "OBJ#" = '6337', "DATAOBJ#" = '6337', "TYPE#" = '2', "CT update "SYS"."OBJ$" set "OBJ#" = '6337', "DATAOBJ#" = '6337', "TYPE#" = '2', "CT
commit;
set transaction read write;
insert into "LUNAR"."TEST"("LOGONDATE","USERNAME") values (TO_DATE('5052-48-50:4 delete from "LUNAR"."TEST" where "LOGONDATE" = TO_DATE('5052-48-50:44:48:49;', '
commit;
set transaction read write;
Unsupported Unsupported
commit;
205 rows selected
SQL>
结束分析:
SQL>execute DBMS_LOGMNR.END_LOGMNR
这时v$logmnr_logs及v$logmnr_contents的内容就全都清空了
总结
1 、The first step in analyzing log files is to create the dictionary file.
dbms_logmnr_d.build('orc1dict.ora','/oracle/database');
2、Create a new list and specify first file:
Specify additional files to be analyzed:
dbms_logmnr.add_logfile(’/oracle/database/ORC1/log1orc1.ora’, dbms_logmnr.NEW);
dbms_logmnr.add_logfile(’/oracle/database/ORC1/log2orc1.ora’, dbms_logmnr.ADDFILE);
3、开始分析
dbms_logmnr.start_logmnr(dictfilename=>’/oracle/database/orc1dict.ora’,
starttime=>to_date(’01/01/98:08AM’,’DD/MM/YY:HHAM’),
endtime=>to_date(’03/01/1998:09AM’,’DD/MM/YYYY:HHAM’));
4、释放资源
dbms_logmnr.end_logmnr
扫一扫
2239
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
