RE: Finding who truncated the table
- From: "Mathias Zarick" <Mathias.Zarick@xxxxxxxxxxxx>
- To: <karlarao@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 1 Jul 2009 10:26:21 +0200
Hi Karl,
i could not believe ...
ML Note is wrong for current releases.
In my short tests in 10.2.0.4.0 I could see the truncate table
statement:
See following example:
SQL> create user scott identified by tiger quota unlimited on users;
User created.
SQL> grant create table, create session to scott;
Grant succeeded.
SQL> conn scott/tiger
Connected.
SQL> create table x ( y char(1), z number);
Table created.
SQL> insert into x values ( 'a',1);
1 row created.
SQL> commit;
Commit complete.
SQL> truncate table x;
Table truncated.
SQL> conn / as sysdba
Connected.
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME =>
'+DG1/saturn_site1/onlinelog/group_1.258.688746983');
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME =>
'+DG1/saturn_site1/onlinelog/group_2.259.688746983',OPTIONS =>
DBMS_LOGMNR.ADDFILE);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME =>
'+DG1/saturn_site1/onlinelog/group_3.260.688746985',OPTIONS =>
DBMS_LOGMNR.ADDFILE);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS =>
DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
PL/SQL procedure successfully completed.
SQL> set lines 100
SQL> column sql_redo format a45
SQL> column sql_undo format a45
SQL> select sql_redo, username, timestamp from v$logmnr_contents where
username='SCOTT' and upper(sql_redo) like '%TRUNCATE%';
SQL_REDO USERNAME
TIMESTAMP
---------------------------------------------
------------------------------ ---------
truncate table x; SCOTT
01-JUL-09
HTH Mathias
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Karl Arao
Sent: Wednesday, July 01, 2009 7:31 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Finding who truncated the table
Hi Guys,
One of the production tables of my client was truncated, they just found
out this morning when the application users are complaining.
Unfortunately, the auditing for their critical tables is turned off.
They want to know who invoked that DDL statement.
I was hoping I could use Log Miner for investigation, but I saw this
Metalink Note.
Truncate Statement is not Detected by Log Miner
Doc ID: 168738.1 Type: PROBLEM
Modified Date : 19-FEB-2002
Do you have other ideas?
- Karl Arao
http://karlarao.wordpress.com
Other related posts:
