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