logminer in Oracle 11g

Recently, some developers asked me if it was possible to find out if dml occured for a certain table. They already tried flashback query but this didn't do the trick.

I knew about the logminer-feature but I was not aware that this was so accessible in 11g. In order to make use of the Logmanager-functionality, the database has to be in archivelog-mode and supplemental logging has to be enabled.

SQL> select log_mode,supplemental_log_data_min from v$database;

LOG_MODE     SUPPLEME

------------ --------

ARCHIVELOG   YES

1 row selected.

SQL> alter database drop supplemental log data;

Database altered.

SQL> select log_mode,supplemental_log_data_min from v$database;

LOG_MODE     SUPPLEME

------------ --------

ARCHIVELOG   NO

1 row selected.

SQL> alter database add supplemental log data;

Database altered.

SQL> select log_mode,supplemental_log_data_min from v$database;

LOG_MODE     SUPPLEME

------------ --------

ARCHIVELOG   YES

1 row selected.

To make use of the logminer functionality, start up enterprise manager console and go to the Availability-tab. There you select ’View and Manage Transactions’.



Now we create a table and insert some data :

SQL> create table oracle.test_tab (a number);

Table created.

SQL> insert into oracle.test_tab values (1);

1 row created.

SQL> insert into oracle.test_tab values (1);

1 row created.

SQL> insert into oracle.test_tab values (1);

1 row created.

SQL> insert into oracle.test_tab values (1);

1 row created.

SQL> commit;

Commit complete.

In Enterprise Manager you can now retrieve all transactions or filter on schema/table/…
In this example we are only interested in the newly created table ORACLE.TEST_TAB.




If you click continue logminer will start retrieving the information :




You will get an overview of all transactions which have been executed :



You can perform a ‘drill-down’ if you click on the transaction id :