Who deleted the records.

Hi,

I understand that....

select *
    from V$sqlarea
    where command_type = 7
    order by first_load_time desc;

...will allow one to see the DELETE-SQL statements issued on a particular table. My question is, is it possible that Oracle did not log the DELETE command issued (or possibly any command for that matter)? I asked because we did lose some records in a particular table but we were unable to determine the source or who made the deletion because it did not show up in the SQL statement above.

Thanks in advance.

kilroy
philippines

"Once a king, always a king. But being a knight is more than enough."

 

[stefanhei]

Hi,
Maybbe this can help:

SELECT sql_text, username, first_load_time
  FROM v$sqlarea s, dba_users u
 WHERE command_type = 7
   AND s.parsing_user_id = u.user_id
 ORDER BY first_load_time desc

 

[LKBrwnDBA]

Try logminer.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[majlumbo]

Do you have any CASCADE DELETE defined on the FK?

 

[ggriffit]

was there a TRUNCATE as that has a different Command_Type (

http://mwidlake.wordpress.com/2010/01/08/more-on-command_type-values/)

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006

http://www.greggriffiths.org/livelink/

 

[torturedmind]

Thanks for all the replies. Sorry for my late feedback.

Anyway, I'm a software engr. and not really the DBA. Our DBA was on a vacation at the time when the problem happened. Interestingly, the problem in my original post happened again the next day. Again, no DELETE (and TRUNCATE) for that particular table. Actually we do not physically delete records. We just have a field in each table called MRK_DEL, numeric, field length of 1, and will contain 1 if it is "deleted", or 0 otherwise. Then we just use views to filter records that suit the users' requirements. We don't use the DELETE command in any of our front-end apps, thus, no CASCADE option either. That's why we were wondering how the records have gone missing. I was able to get the physically deleted records using the UNDO tablespace.

select * 
  from mytable 
  as of timestamp to_timestamp('<date & time of possible retrieval>','dd/mm/yyyy hh24:mi');

kilroy
philippines

"Once a king, always a king. But being a knight is more than enough."