LOGMNR包挖掘在线日志(6)

SQL> create table zlm.t2 as select * from zlm.t1;
create table zlm.t2 as select * from zlm.t1
                                        *
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace WEB

由于之前zlm用户给的默认表空间WEB空间比较小，只有10M，因此报空间不足了

SQL> select d.file#,d.ts#,d.bytes/1024/1024 as "Size(M)",t.name from v$datafile d,v$tablespace t where d.ts#=t.ts#;

    FILE#        TS#    Size(M) NAME
---------- ---------- ---------- ---------------------------------------------
        1          0        560 SYSTEM
        2          1        165 UNDOTBS1
        3          2        270 SYSAUX
        4          4        40 USERS
        5          6        100 EXAMPLE
        6          7        50 ZLM
        7        18        20 INDX2
        8        19        10 WEB

8 rows selected.

--修改zlm缺省表空间为zlm

SQL> alter user zlm default tablespace zlm;

User altered.

SQL> create table zlm.t2 as select * from zlm.t1;

Table created.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS          FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ----------
        1          1        11  52428800          1 NO  CURRENT                1405472 2014-12-26
        2          1          9  52428800          1 YES INACTIVE              1402823 2014-12-24
        3          1        10  52428800          1 YES INACTIVE              1405357 2014-12-26

由于之前创建t1表的操作会产生一定的redo,并且是一个DDL操作，会自动commit，当前日志已经切换到group 1了
 
SQL> select scn,timestamp,log_id,seg_owner,seg_type,table_space,data_blk#,data_obj#,data_objd#,session#,serial#,username,session_info,sql_redo,sql_undo 
2 from v$logmnr_contents t
3 where t.sql_redo like 'create%';

      SCN TIMESTAMP      LOG_ID SEG_OWNER                          SEG_TYPE TABLE_SPACE                      DATA_BLK#
---------- ---------- ---------- -------------------------------- ---------- -------------------------------- ----------
 DATA_OBJ# DATA_OBJD#  SESSION#    SERIAL# USERNAME
---------- ---------- ---------- ---------- ------------------------------
SESSION_INFO
----------------------------------------------------------------------------------------------------------------------------------
SQL_REDO
----------------------------------------------------------------------------------------------------------------------------------
SQL_UNDO
----------------------------------------------------------------------------------------------------------------------------------
  1404138 2014-12-26          9 ZLM                                      2                                        0
    53863          0          0          0

create table zlm.t1 as select * from dba_objects;

既然当前日志是group 1的日志文件，而之前挖掘对象是group 2的日志文件，所以查询v$logmgr_contetns视图发现，第2条建表语句并没有被记录下来

--重新对group 1的redo01.log日志进行挖掘

SQL> exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/ora10g/redo01.log');

PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed.

--查看LOGMNR内容表