This content has been marked as final. Show 11 replies
Assuming you mean flashback? If so then you can only flashback for as long as the undo information in available (depends on a whole multitude of things).
As for checking who/what did it? You could try "select * from v$sql" however things only stay in there for a very short amount of time.
I'd recommend taking the EBS approach and adding the following mandatory columns to your table(s):
last_update_login (represents the users session id and is nullable)
Then any time you do an update, you always set those. Sure, if there are two successive changes then (disregarding flashback) you might struggle. But if you're required to be able to trace to that level then you should be triggering all inserts/updates/deletes off into an audit table with columns similar to the above.
Thank you for your answer but that does not respond my question.. I mean, totally...
We tried the flashback feature, but it does not work... The only thing that I care about is who did those changes...
Is there any log on log of every table?
Thanks in advance
Other than v$sql so see the statement which updated it, not that I'm aware of. And you'll be way too far gone to get info from there now.
My suggestion of adding audit columns was to provide this information in the future, not to resolve your current problem. I don't think you'll be able to do that now.
there are various auditing options and add ons in the Oracle database, all of which you can read about in the Oracle documentation and simply just by googling. The problem you face is that most of these are not turned on by default due to possible performance overheads, and in your case, there is no use shutting the gate after the horse has bolted. On top of the standard features, you can also specifically target auditing with the use of extra columns and tables and triggers to maintain them. Once again, this has to be thought about in advance.
1) There are no logs on every table, unless you wrote these logs yourself.
2) You say "We tried the flashback feature, but it does not work.", but what does "does not work" mean? You can only go so far back via a flashback, and it's a function of the size of the undo tablespace. But you should have been able to go back at least a day or so. That's why the "does not work" statement is ambiguous.
3) With all respect, I find it highly unlikely that someone outside your workspace (on this shared instance) decided to go in and manually update these 174 rows for no good reason. I think it is more plausible that someone within your workspace or via some logic in your application issued this update.
I hope this helps.
What I meant with "we tried flashback" is we could not go back what we want... we wanted go back 4 days.
About point 3... I'm thinking the same thing... that's why i would like to find a way to see who did it....
It seems odd that you wanted to go back 4 days, no? You reported that these rows were updated at 19-NOV-2012 15:54:39, and your first post on this thread was also on 19-NOV. If you wanted to get back to the state of these rows prior to the update, why not go back to 18-NOV (which probably would have been possible at the time, but probably not now, on 21-NOV).
That was because those updates were with this value: 15-NOV-2012 15:32 that's why we tried that first... go back 4 days...
+>> "those updates were with this value: 15-NOV-2012 15:32"+
This seems to contradict your original statement of "174 records modified the field with this value: 19-NOV-2012 15:54:39."
But it seems like you have this problem understood. It wasn't a problem with flashback query, per se. It's just that you couldn't go back that far in time.
Yeah exactly... and the only thing that I want is find a way to know who did it...... or howww
Might be a little too late to figure out the who or how.. Might I suggest you follow the suggestion and add the auditing columns to your table and a trigger to populate the audit columns or enable auditing in your instance to track FUTURE issues..