LogMiner continuous mode question...how to stop?

From: Dave Sisk <dsisk_at_nc.rr.com.0nospam0>
Date: Thu, 01 Apr 2004 01:57:46 GMT
Message-ID: <uGKac.38655$zy.358861@twister.southeast.rr.com>

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

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

Received on Wed Mar 31 2004 - 19:57:46 CST