This discussion is archived
11 Replies Latest reply: Nov 21, 2012 7:14 AM by TexasApexDeveloper RSS

Rollback in my workspace

Bernardo Newbie
Currently Being Moderated
I have a big big problem...

One field (closure date) in one of my tables modified itself and I can't find the reason. 174 records modified the field with this value: 19-NOV-2012 15:54:39.

I have this trigger to set the closure date value when the status is closed:
create or replace TRIGGER "SD_INCIDENTS_TIME2"
BEFORE
update on "PROBLEMS"
for each row
begin
    IF (:new.STATUS='CLOSED') THEN
    :new.CDATE :=SYSTIMESTAMP AT TIME ZONE '-3:00';
    END IF;
end;
I have been doing tests to determine if this trigger did the problem but not....

The other thing that I want to know is where can I find the logs to see who did those changes or what triggered that..

Thanks, Bernardo

Regards
  • 1. Re: Rollback in my workspace
    603257 Journeyer
    Currently Being Moderated
    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):

    creation_date
    created_by
    last_update_date
    last_updated_by
    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.
  • 2. Re: Rollback in my workspace
    Bernardo Newbie
    Currently Being Moderated
    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
  • 3. Re: Rollback in my workspace
    603257 Journeyer
    Currently Being Moderated
    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.
  • 4. Re: Rollback in my workspace
    Vite DBA Pro
    Currently Being Moderated
    Hi Bernardo,

    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.

    Regards
    Andre
  • 5. Re: Rollback in my workspace
    jkallman Employee ACE
    Currently Being Moderated
    Hi Bernardo,

    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.

    Joel
  • 6. Re: Rollback in my workspace
    Bernardo Newbie
    Currently Being Moderated
    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....

    Thanks
  • 7. Re: Rollback in my workspace
    jkallman Employee ACE
    Currently Being Moderated
    Bernardo,

    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).

    Joel
  • 8. Re: Rollback in my workspace
    Bernardo Newbie
    Currently Being Moderated
    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...
  • 9. Re: Rollback in my workspace
    jkallman Employee ACE
    Currently Being Moderated
    Hi Bernardo,

    +>> "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.

    Joel
  • 10. Re: Rollback in my workspace
    Bernardo Newbie
    Currently Being Moderated
    Yeah exactly... and the only thing that I want is find a way to know who did it...... or howww
  • 11. Re: Rollback in my workspace
    TexasApexDeveloper Guru
    Currently Being Moderated
    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..

    Thank you,

    Tony Miller
    Ruckersville, VA

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points