SEO

Hi folks: Here's a LogMiner exercise, with two issues.

1) Even after starting LogMiner, nothing ever seems to show up in most of
the ancillary LogMiner views (like v$logmnr_parameters, v$logmnr_stats,
etc.) Minor issue, just curious....haven't read through the details on this
one, but was wondering if anyone knows a quick answer.

2) Here's a more annoying issue. After starting LogMiner, I do this query:
SELECT seg_owner, seg_name, sql_redo, sql_undo FROM v$logmnr_contents where
seg_owner='some_owner';

I get results back as they show up in the redo log, which is exactly what I
want of course. However, this query never actually returns...it just
continues to block waiting on more change records to show up. I actually
like this behavior, but I haven't found in the documents how to "end" this
query without actually just killing the session executing it. Has anyone
found the right way to get it to return? (Oracle9.2.0.1) I can't stop the
LogMiner session the right way since this query never returns. If anyone
has any suggestions, please post and/or email me.

Steps below:
----------------------------------------------------------------------------
----------------------------------------------------------------------------
-----

-- Check/enable supplemental ID logging...a one-time thing for the database
unless it's changed.
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX)
COLUMNS;

select log_mode,
supplemental_log_data_min min,
supplemental_log_data_pk pk,
supplemental_log_data_ui ui
from v$database;


-- Set tablespace for LOGMNR tables:
EXEC SYS.DBMS_LOGMNR_D.SET_TABLESPACE('TOOLS');


-- Pick a logfile (any online or archived logfile will work if using
continuous mining)
EXEC
SYS.DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME=>'/oracle/u02/oradata/d1/redo01a.log
');



-- Start LogMiner w/ online catalog as dictionary, committed data only, and
continuous mine options
EXEC SYS.DBMS_LOGMNR.START_LOGMNR(OPTIONS =>
SYS.DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -

SYS.DBMS_LOGMNR.COMMITTED_DATA_ONLY + SYS.DBMS_LOGMNR.CONTINUOUS_MINE);


-- LogMiner data dictionary views:
V$LOGMNR_CALLBACK
V$LOGMNR_CONTENTS
V$LOGMNR_DICTIONARY
V$LOGMNR_LOGFILE
V$LOGMNR_LOGS
V$LOGMNR_PARAMETERS
V$LOGMNR_PROCESS
V$LOGMNR_REGION
V$LOGMNR_SESSION
V$LOGMNR_STATS
V$LOGMNR_TRANSACTION



SELECT * FROM v$logmnr_parameters;
SELECT * FROM v$logmnr_dictionary;
SELECT * FROM v$logmnr_logs;
SELECT * FROM v$logmnr_logfile;

SELECT seg_owner, seg_name, sql_redo, sql_undo FROM v$logmnr_contents where
seg_owner='some_owner';

DECLARE
v_count NUMBER(3,0);
BEGIN
v_count := 1;
WHILE v_count <=10 LOOP
insert into junk values(to_char(v_count));
commit;
v_count := v_count + 1;
END LOOP;
END;
/

-- Stop LogMiner
EXEC SYS.DBMS_LOGMNR.END_LOGMNR;