-- -------------------------------------------
PROMPT Begin test script LMTEST1.SQL
-- -------------------------------------------
-- Caleb Small, December, 2007
--
-- This script creates a test scenario with named transactions, log switches,
-- and DDL changes.
set pagesize 1000
set linesize 100
set recsepchar .

-- Connect as LMUSER and switch to a fresh log file
CONNECT lmuser/oracle@orcl
alter system switch logfile;

-- Clean out the log table.  Don't do this on production system!
delete from lm_change_log;
commit;

-- -------------------------------------------
PROMPT Create test tables
-- -------------------------------------------
EXEC lm_log_dict('create tables');

drop table emp;
drop table dept;
create table emp as select * from scott.emp; 
create table dept as select * from scott.dept;
alter table emp add constraint emp_pk primary key (empno);
alter table dept add constraint dept_pk primary key (deptno);
alter table emp add constraint emp_dept_fk 
   foreign key (deptno) references dept(deptno);

-- Add some columns that we can modify/drop later to prove persistence
-- through DDL changes
alter table emp add 
   (t1 char(1), t2 char(1), t3 char(1), t4 char(1), t5 char(1), t6 char(1));
update emp set
   t1 = substr(ename,1,1),
   t2 = substr(ename,2,1),
   t3 = substr(ename,3,1),
   t4 = substr(ename,4,1),
   t5 = substr(ename,5,1),
   t6 = substr(ename,6,1);
commit;

-- Log switch
alter system archive log current;

-- This is ONLY for convenience while testing
EXEC lm_log_dict('log switch 1');

-- -------------------------------------------
PROMPT Begin by doing some DML
-- -------------------------------------------
-- This will not be reconstructed because there is no dictionary yet

set transaction name 'MOE';
insert into emp (empno, ename, job, hiredate, sal, deptno)
   values (1000, 'MOE', 'MANAGER', sysdate, 500, 10);
commit;

set transaction name 'LARRY';
insert into emp (empno, ename, job, hiredate, sal, deptno)
  values (1001, 'LARRY', 'ANALYST', sysdate, 600, 20);
commit;

set transaction name 'JOE';
insert into emp (empno, ename, job, hiredate, sal, deptno)
  values (1002, 'JOE', 'CLERK', sysdate, 700, 30);
commit;

set transaction name 'MOE';
delete from emp where empno=1000;
rollback;

-- Force some log switches along the way
alter system archive log current;

-- This is ONLY for convenience while testing
EXEC lm_log_dict('log switch 2');

set transaction name 'DELETEMAN';
delete from emp where empno=7934;
commit;

set transaction name 'MOE';
update emp set sal=800 where ename='MOE';
commit;

set transaction name 'LARRY';
update emp set sal=700 where ename='LARRY';
commit;

set transaction name 'JOE';
update emp set sal=600 where ename='JOE';
commit;

set transaction name 'LARRY';
delete from emp where empno=1001;
rollback;

-- -------------------------------------------
PROMPT Do some DDL and DCL
-- -------------------------------------------
-- DDL will be reconstructed prior to the dictionary write, in fact
-- even the table creation above will be reconstructed.

EXEC lm_log_dict('begin DDL 1');
set transaction name 'FRED';
alter table emp drop column t1;

-- DCL is not captured
set transaction name 'CALEB';
grant select on emp to public;

-- -------------------------------------------
PROMPT Do some more DML
-- -------------------------------------------
-- No dictionary yet, but table is consistent with dictionary
-- that will be written later.  This WILL be reconstructed!

set transaction name 'MOE';
update emp set sal=1500 where empno=1000;
commit;

set transaction name 'LARRY';
update emp set sal=1600 where empno=1001;
commit;

set transaction name 'JOE';
update emp set sal=1700 where empno=1002;
commit;

set transaction name 'JOE';
delete from emp where comm IS NOT NULL;
rollback;


-- Force some log switches along the way
alter system archive log current;

-- This is ONLY for convenience while testing
EXEC lm_log_dict('log switch 3');


set transaction name 'DELETEMAN';
delete from emp where empno=7902;
commit;

set transaction name 'MOE';
update emp set sal=1800 where empno=1000;
commit;

set transaction name 'LARRY';
update emp set sal=1700 where empno=1001;
commit;

set transaction name 'JOE';
update emp set sal=1600 where empno=1002;
commit;

-- -------------------------------------------
PROMPT Write the dictionary
-- -------------------------------------------
-- Everything following the dictionary will be reconstructed

EXEC lm_log_dict('begin dict');
EXECUTE SYS.DBMS_LOGMNR_D.BUILD( -
    OPTIONS=> SYS.DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
EXEC lm_log_dict('end dict');

-- -------------------------------------------
PROMPT Do some more DML
-- -------------------------------------------
set transaction name 'MOE';
update emp set sal=2500 where empno=1000;
commit;

set transaction name 'LARRY';
update emp set sal=2600 where empno=1001;
commit;

set transaction name 'JOE';
update emp set sal=2700 where empno=1002;
commit;

-- Force some log switches along the way
alter system archive log current;

-- This is ONLY for convenience while testing
EXEC lm_log_dict('log switch 4');

set transaction name 'DELETEMAN';
delete from emp where empno=7900;
commit;

set transaction name 'MOE';
update emp set sal=2800 where empno=1000;
commit;

set transaction name 'LARRY';
update emp set sal=2700 where empno=1001;
commit;

set transaction name 'JOE';
update emp set sal=2600 where empno=1002;
commit;

set transaction name 'DELETEMAN';
delete from emp where ename='KING';
rollback;

-- -------------------------------------------
PROMPT Do some more DDL and DCL
-- -------------------------------------------
-- Even after DDL changes, subsequent DML will be reconstructed

EXEC lm_log_dict('begin DDL 2');
set transaction name 'FRED';
alter table emp drop column t3;

-- DCL is not captured
set transaction name 'CALEB';
revoke select on emp from public;

-- -------------------------------------------
PROMPT Do some more DML
-- -------------------------------------------
set transaction name 'MOE';
update emp set sal=3500 where empno=1000;
commit;

set transaction name 'LARRY';
update emp set sal=3600 where empno=1001;
commit;

set transaction name 'JOE';
update emp set sal=3700 where empno=1002;
commit;

-- Force some log switches along the way
alter system archive log current;

-- This is ONLY for convenience while testing
EXEC lm_log_dict('log switch 5');

set transaction name 'MOE';
update emp set sal=3800 where empno=1000;
commit;

set transaction name 'LARRY';
update emp set sal=3700 where empno=1001;
commit;

set transaction name 'JOE';
update emp set sal=3600 where empno=1002;
commit;

-- So that we get the final SCN
EXEC lm_log_dict('end test.');

PROMPT Continue with script LMTEST1b.sql to begin log mining...