如何利用DBMS_LOGMNR包挖掘在线日志(四)

--通过抓取关键字"create"，来获得刚才的建表语句

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;

可以看到，刚才的DDL建表语句已经被记录到v$logmnr_contents表中了

--结束日志挖掘

SQL> exec dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.

一旦结束日志挖掘后，就无法再对v$logmgr_contents视图进行查询，会报错：

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%';
from v$logmnr_contents t
? ? *
ERROR at line 2:
ORA-01306: dbms_logmnr.start_logmnr() must be invoked before selecting from v$logmnr_contents

并且要重新指定挖掘的日志对象，否则依然报错：

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

*
ERROR at line 1:
ORA-01292: no log file has been specified for the current LogMiner session
ORA-06512: at "SYS.DBMS_LOGMNR", line 58
ORA-06512: at line 1

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
---------- ---------- --