RE: Finding who truncated the table
- From: "Mathias Zarick" <Mathias.Zarick@xxxxxxxxxxxx>
- To: "Balakrishna Y" <krishna000@xxxxxxxxx>
- Date: Wed, 1 Jul 2009 10:43:29 +0200
Hi Bala,
for this v$logmnr_contents has the session_info column:
SQL> select session_info from v$logmnr_contents where username='SCOTT'
and upper(sql_redo) like '%TRUNCATE%';
SESSION_INFO
------------------------------------------------------------------------
------------------------------------------------------------------------
--------------------------------------------------------
login_username=SCOTT client_info= OS_username=oracle
Machine_name=lnxzam12 OS_terminal=pts/2 OS_process_id=3444
OS_program_name=sqlplus@lnxzam12 (TNS V1-V3)
HTH Mathias
________________________________
From: Balakrishna Y [mailto:krishna000@xxxxxxxxx]
Sent: Wednesday, July 01, 2009 10:32 AM
To: Mathias Zarick
Cc: karlarao@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: Re: Finding who truncated the table
Hi,
But what incase if the scott user credentials is used by 10 more people
and some one from them had truncated the table.
I hope this is the question Mr.Karl is asking .
Regards
Bala
On Wed, Jul 1, 2009 at 1:56 PM, Mathias Zarick
<Mathias.Zarick@xxxxxxxxxxxx> wrote:
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:
