Oracle Database Discussions

DBMS_LOGMNR or AUDIT; but answer keeps changing

Hi,
you can check in DBA_TAB_MODIFICATIONS view



1* select table_owner,table_name,inserts,updates,deletes from dba_tab_modifications where table_name='emp' and TABLE_OWNER='scott'
SQL> /

TABLE_OWNER TABLE_NAME INSERTS UPDATES DELETES
------------------------------ ------------------------------ ---------- ---------- ----------
scott emp 212 198 177594

Edited by: CKPT on Jul 27, 2010 7:03 AM

when all else fails Read The Fine Manual

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_4149.htm#sthref1901

"This view is populated only for tables with the MONITORING attribute. It is intended for statistics collection over a long period of time. For performance reasons, the Oracle Database does not populate this view immediately when the actual modifications occur. "

Hi,

Thanks for your fast response.
I can see last DDL on a table using TOAD Editor, I just need to see a last DML.
On view DBA_TAB_MODIFICATIONS not all table are listed, especially my targeted table.
Is any setting / configuration for this?

Somethings happen to our table, so I need to see what / who using it last time.
Audit trail is not active anyway, so we got to find some way to find it.

Thanks.

Hi,

SQL> create table baskar (x int);

Table created.

SQL> insert into baskar values (1);

1 row created.

SQL> insert into baskar values (2);

1 row created.

SQL> delete from baskar where x=1;

1 row deleted.

SQL> update baskar set x=3;

1 row updated.

SQL> commit;

Commit complete.

SQL> select INSERTS,UPDATES,DELETES from sys.dba_tab_modifications where TABLE_NAME='BASKAR';

no rows selected

SQL> exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL procedure successfully completed.

SQL> select INSERTS,UPDATES,DELETES from sys.dba_tab_modifications where TABLE_NAME='BASKAR';

   INSERTS    UPDATES    DELETES
---------- ---------- ----------
         2          1          1

1 row selected.

thanks,
baskar.l

DBMS_LOGMNR!!!!!!

Hi,

What do you mean about DBMS_LOGMNR? There is a procedur, did we need to exec that procedure or something to find last DML on table?

Thanks.

http://www.lmgtfy.com/?q=oracle+DBMS_LOGMNR

> I see. It looked like audit table, but it need to initialize.
But our problem is need to see recent changes to the table, if we do so I guess I cannot see recent changes on the past before we initialize? It that correct?

Thanks.

Please quantify "recent".

DBMS_LOGMNR operates against archived redo log file(s).
After you force a LOG SWITCH, DBMS_LOGMNR could be invoked against newest archived redo logfile immediately.
Is this recent enough for you?

Edited by: sb92075 on Jul 26, 2010 8:16 PM

AFAIK, there is no such auditing which would show only the last dml done on the table. You would get it all or nothing at all! Would you explain please that why using auditing won't serve your purpose?

Aman....

To Answer this question i searched on google and in this forum and found below sum-ups of different links/threads :

1. The best and easy way is auditing;

AUDIT_TRAIL=TRUE
AUDIT INSERT ON <TABLE_NAME> BY ACCESS;

2.SELECT SCN_TO_TIMESTAMP(MAX(ORA_ROWSCN)) FROM your_table; -- but you shouldn't trust it 100% of time.

3.You could look at V$SEGMENT_STATISTICS. This view won't provide an actual time, but it will provide the logical reads and physical reads/writes against a particular table segment. If you are just looking to monitor whether a table is being utilized or not, this view may help.

4.
Alter Table Tabname Monitoring;

then Select * from User_Tab_Modifications;

If you want all tables to be Checked, Run the script like this
Spool Monitor.sql
Select 'Alter table '||Table_Name||' Monitoring ;' From User_Tables;
Spool Off;
@Monitor.sql

exec dbms_stats.flush_database_monitoring_info;

5. I use the following way to find out. Majority of time I get what I required.

I search for update/delete/insert statement on that particular table in v$sql view order by last_active_time.

It is something like why a transportation company (oracle database) will keep data of passengers (name, age, destination place) (dml), because transportation company's (Oracle) business is to provide bus services (sqlplus, RMAN, data guard). If transportation company (oracle dba) is supposed to maintain the "abnormal" data then only it will do the same.

Regards
Girish Sharma

Edited by: Girish Sharma on Jul 27, 2010 10:38 AM