Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login
Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Tek-Tips LinkedIn® Group

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site
Partner Button
(Download This Button Today!)

Feedback

"...I just wanted to say that you guys RULE, a million thank you's to whoever created, and/or manages this site. KEEP UP THE GOOD WORK..."
More...

Geography

Where in the world do Tek-Tips members come from?
Click Here To Find Out!

Tek-Tips Shirts!

Get Tek-Tips Swag
Get your
Tek-Tips Forums
SWAG here!
Home > Forums > Programmers > DBMS Packages > Oracle: Oracle release 10g Forum

Who deleted the records.

thread1177-1685874
Read More Threads Like This One
torturedmind (Programmer)
15 Jun 12 4:00
Hi,

I understand that....

CODE

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 knight
philippines

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

stefanhei (TechnicalUser)
15 Jun 12 6:21
Hi,
Maybbe this can help:

CODE

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 (MIS)
15 Jun 12 9:00


Try logminer .
3eyes

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
majlumbo (Programmer)
15 Jun 12 10:18
Do you have any CASCADE DELETE defined on the FK?
ggriffit (Programmer)
18 Jun 12 10:17
was there a TRUNCATE as that has a different Command_Type (http://mwidlake.wordpress.com/2010/01/08/more-on-c...)

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006
http://www.greggriffiths.org/livelink/

torturedmind (Programmer)
20 Jun 12 22:32
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.

CODE

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

kilroy knight
philippines

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

Read More Threads Like This One

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To Oracle: Oracle release 10g

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close