RE: Finding who truncated the table

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: