Experimenting with the continous mining

cbruhn2's picture
Submitted by cbruhn2 on Wed, 2007-01-17 10:38
articles: 
RDBMS Server

Today I have been experimenting with the new feature of continous mining with logmnr.

My setup

  • Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options
  • ALTER DATABASE ADD SUPPLEMENTAL LOG DATA or we don't see the funny things in the redologfiles
I now starts the logminer session in one window with :
BEGIN
  dbms_Logmnr.Start_Logmnr(StartTime => SYSDATE - 1 / 24, Options => dbms_Logmnr.dict_From_OnLine_Catalog + dbms_Logmnr.Continuous_Mine);
END;
/

SELECT *
FROM   v$Logmnr_Logs;

    LOG_ID
----------
FILENAME
--------------------------------------------------------------------------------LOW_TIME             HIGH_TIME                 DB_ID DB_NAME   RESET_SCN
-------------------- -------------------- ---------- -------- ----------
RESET_SCN_TIME        THREAD_ID THREAD_SQN    LOW_SCN   NEXT_SCN DIC DIC TYPE
-------------------- ---------- ---------- ---------- ---------- --- --- ------- BLOCKSIZE   FILESIZE INFO                                 STATUS
---------- ---------- -------------------------------- ----------
         3
/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/ORCL/archivelog/2007_01_17/o1_mf_1_3_2tw9318q_.arc
17-JAN-2007 13:46:42 17-JAN-2007 14:28:01 1132534540 ORCL         852561
12-JAN-2007 15:07:16          1          3     911167     913265 NO  NO  ARCHIVE       512    1640448                                           1

So now we are ready to mine.
In this session I now startst looking for a user named CRB doing something in the base. At first i tried without the set arrraysize 1; but then nothing happened before at lot of alterations had been done.
set arraysize 1;  <--- we want to see results as fast as possible. 

SELECT Table_Name,
       TIMESTAMP
FROM   v$Logmnr_Contents
WHERE  UserName = 'CRB'
/

In another window I now run something as user crb :
CREATE TABLE a
 AS
  SELECT *
  FROM   All_Users
  WHERE  ROWNUM < 100;

and immediatly receives this in the first window.
 
TABLE_NAME                       TIMESTAMP
-------------------------------- --------------------
                                 17-JAN-2007 18:31:52
                                 17-JAN-2007 18:31:52
OBJ$                             17-JAN-2007 18:31:52
                                 17-JAN-2007 18:31:52
CON$                             17-JAN-2007 18:31:52
                                 17-JAN-2007 18:31:52
                                 17-JAN-2007 18:31:52
                                 17-JAN-2007 18:31:52
CON$                             17-JAN-2007 18:31:52
                                 17-JAN-2007 18:31:52
                                 17-JAN-2007 18:31:52
                                 17-JAN-2007 18:31:52
CON$                             17-JAN-2007 18:31:52
                                 17-JAN-2007 18:31:52
                                 17-JAN-2007 18:31:52
.......... --- and many more lines.
CDEF$                            17-JAN-2007 18:31:52
CCOL$                            17-JAN-2007 18:31:52
CDEF$                            17-JAN-2007 18:31:52
SEG$                             17-JAN-2007 18:31:52
CON$                             17-JAN-2007 18:31:52
CON$                             17-JAN-2007 18:31:52
CON$                             17-JAN-2007 18:31:52
                                 17-JAN-2007 18:31:52

now every time I do DML something in my second window as user CRB, I get a new line in window one.
Second window.
18:31:52 ORCL> DELETE FROM a  WHERE ROWNUM < 4;

 
3 rows deleted.
 
Elapsed: 00:00:00.09
18:37:36 ORCL>

results in this on first window
A                                17-JAN-2007 18:37:37
A                                17-JAN-2007 18:37:37
A                                17-JAN-2007 18:37:37

This is pretty cool I think, but for the moment I don't know how I could use it but.... :-)



»