3、对DDL 操作分析


SQL> conn scott/tiger


Connected.


SQL> select * from tb01;


ID


----------


1


2


3


SQL> drop table tb01 purge;


Table dropped.


SQL> create table tb01(id int) tablespace test;


Table created.


SQL> insert into tb01 values(1);


1 row created.


SQL> commit;


Commit complete.




（1）设置logmnr 参数，存放数据字典文件


[oracle@work ]$ mkdir /export/home/oracle/logmnr


SQL> show parameter utl


NAME                                 TYPE        VALUE


------------------------------------ ----------- ------------------------------


create_stored_outlines               string


utl_file_dir                         string


SQL> alter system set utl_file_dir='/export/home/oracle/logmnr' scope=spfile;


System altered.


05:11:48 SQL> startup force


ORACLE instance started.


Total System Global Area  314572800 bytes


Fixed Size                  1219184 bytes


Variable Size              79693200 bytes


Database Buffers          230686720 bytes


Redo Buffers                2973696 bytes


Database mounted.


Database opened.


SQL> show parameter utl


NAME                                 TYPE                              VALUE


------------------------------------ --------------------------------- ------------------------------


create_stored_outlines               string


utl_file_dir                         string                            /export/home/oracle/logmnr



（2）建立数据字典文件dict.ora


SQL> execute dbms_logmnr_d.build('dict.ora','/export/home/oracle/logmnr',dbms_logmnr_d.store_in_flat_file);


PL/SQL procedure successfully completed.


——查看日志信息


SQL> col name for a50


SQL> select name,sequence# from v$archived_log;


NAME                                                SEQUENCE#


-------------------------------------------------- ----------


/disk1/arch/anny/arch_1_3_778691360.log                     3


/disk1/arch/anny/arch_1_4_778691360.log                     4


/disk1/arch/anny/arch_1_5_778691360.log                     5


45 rows selected.


SQL> col status for a12


SQL> select * from v$log;


GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED  STATUS       FIRST_CHANGE# FIRST_TIME


---------- ---------- ---------- ---------- ---------- --------- ------------ ------------- -------------------


1          1          6  104857600          3 NO        CURRENT             851187 2012-03-23 15:43:36


4          1          4  104857600          3 YES       INACTIVE            851181 2012-03-23 15:43:30


3          1          3  104857600          3 YES       INACTIVE            851178 2012-03-23 15:43:29


2          1          5  104857600          3 YES       INACTIVE            851183 2012-03-23 15:43:31


4 rows selected.


SQL> col member for a50


SQL> select group#,member from v$logfile;


GROUP# MEMBER


---------- --------------------------------------------------


1 /disk3/oradata/anny/redo01a.log


3 /disk3/oradata/anny/redo03a.log


2 /disk3/oradata/anny/redo02a.log


4 /disk3/oradata/anny/redo04a.log


1 /disk1/oradata/anny/redo01b.log


2 /disk1/oradata/anny/redo02b.log


3 /disk1/oradata/anny/redo03b.log


4 /disk1/oradata/anny/redo04b.log


1 /disk2/oradata/anny/redo01c.log


2 /disk2/oradata/anny/redo02c.log


3 /disk2/oradata/anny/redo03c.log


4 /disk2/oradata/anny/redo04c.log


12 rows selected.



（3）添加日志分析


SQL> execute dbms_logmnr.add_logfile(logfilename=>'/disk3/oradata/anny/redo01a.log',options=>dbms_logmnr.new);


PL/SQL procedure successfully completed.


SQL> execute dbms_logmnr.add_logfile(logfilename=>'/disk1/arch/anny/arch_1_5_778691360.log',options=>dbms_logmnr.addfile);


PL/SQL procedure successfully completed.



（4）执行分析


SQL> execute dbms_logmnr.start_logmnr(dictfilename=>'/export/home/oracle/logmnr/dict.ora',options=>dbms_logmnr.ddl_dict_tracking);


PL/SQL procedure successfully completed.



（5）查看分析结果


05:23:33 SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';


Session altered.


SQL> col username for a10


SQL> col sql_redo for a50


SQL> select username,scn,timestamp,sql_redo from v$logmnr_contents


2  where username='SCOTT' and lower(sql_redo) like '%table%';


USERNAME          SCN TIMESTAMP           SQL_REDO


---------- ---------- ------------------- --------------------------------------------------


SCOTT          851229 2012-03-23 15:45:22 drop table tb01 purge;


SCOTT          851264 2012-03-23 15:45:45 create table tb01(id int) tablespace test;


2 rows selected.


SQL> execute dbms_logmnr.end_logmnr;


PL/SQL procedure successfully completed.


***********************************以上接案例2*****************************


oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html