RE: Finding who truncated the table

From: Mathias Zarick <Mathias.Zarick_at_trivadis.com>
Date: Wed, 1 Jul 2009 10:43:29 +0200
Message-ID: <370BF313301A024C962B05768686368301C2C758_at_MSXVS04.trivadis.com>

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_at_lnxzam12 (TNS V1-V3)

HTH Mathias  

---

From: Balakrishna Y [mailto:krishna000_at_gmail.com] Sent: Wednesday, July 01, 2009 10:32 AM
To: Mathias Zarick
Cc: karlarao_at_gmail.com; oracle-l_at_freelists.org 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_at_trivadis.com> 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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Karl Arao

	Sent: Wednesday, July 01, 2009 7:31 AM
	To: oracle-l_at_freelists.org
	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
	
	

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jul 01 2009 - 03:43:29 CDT