-- -------------------------------------------
PROMPT Begin test script LMTEST1.SQL
-- -------------------------------------------
-- Caleb Small, December, 2007
--
-- This script steps through the log mining scenarios created by LMTEST1a.sql

CONNECT lmuser/oracle@orcl
set pagesize 1000
set recsepchar .

-- -------------------------------------------
PROMPT Begin a logminer session
-- -------------------------------------------
set linesize 120
select * from lm_log;

CREATE OR REPLACE VIEW miner AS
   SELECT scn, timestamp, tx_name, seg_name, seg_type, sql_redo
   FROM v$logmnr_contents
   WHERE seg_owner='LMUSER'
   ORDER BY scn;

PROMPT .
PROMPT Enter start and end SCNs for the entire test period

@bminer

PROMPT The mining session will show that DDL is reconstructed both before and
PROMPT after the dictionary write, but that DML is only reconstructed when table
PROMPT structure is consistent with the dictionary prior to a write, or any time
PROMPT after a dictionary write. DDL executed after the dictionary
PROMPT write does not pevent reconstruction of subsequent DML because we have
PROMPT used the DDL_DICT_TRACKING option.
PROMPT .
PROMPT Press ENTER to begin mining...
ACCEPT x

SELECT * FROM miner;

EXEC SYS.DBMS_LOGMNR.END_LOGMNR( );

PROMPT Press ENTER to continue...
ACCEPT x

select * from lm_log;

PROMPT Enter start SCN as "end dict", and end SCN as "end test"

@bminer

PROMPT The mining session can not be started if it does not include a
PROMPT copy of the dictionary!
PROMPT .
PROMPT Press ENTER to continue...
ACCEPT x

EXEC SYS.DBMS_LOGMNR.END_LOGMNR( );

select * from lm_log;

PROMPT Now, enter the start SCN as "begin dict", and end SCN as "end dict"

@bminer

PROMPT At this point, the dictionary has been read but no analysis performed.
PROMPT .
PROMPT Without ending the log miner session, restart the session and enter
PROMPT the start SCN as "begin DDL 1" and the end SCN as "log switch 3"

@bminer

PROMPT Press ENTER to begin mining...
ACCEPT x 

select * from miner;

PROMPT First, we read the dictionary, Then, we restarted the log miner session
PROMPT with an earlier time period that was consistent with that copy of the 
PROMPT dictionary.  Transactions were reconstructed for that time period only.
PROMPT Furthermore, the dictionary still remains active, we can still use it for
PROMPT yet another analysis.
PROMPT .
PROMPT Press ENTER to continue...
ACCEPT x

select * from lm_log;

PROMPT Without ending the current log miner session, restart again and enter
PROMPT the start SCN as "log switch 5" and the end SCN as "end test"

@bminer

PROMPT Press ENTER to begin mining...
ACCEPT x 

select * from miner;

EXEC SYS.DBMS_LOGMNR.END_LOGMNR( );

PROMPT Again, using the previously read dictionary, and tracking DDL changes 
PROMPT that occurred since (even though they were not in the analysis period)
PROMPT transactions were reconstructed for the desired period.
PROMPT .