2008 956120 blade c3000 cleaning count cs csUriQuery csUriStem database dedicated disposal filter fix flashback hosting IIS7 interval isapi KB keyword log lome managed manufacturing miva mpio oil oracle polystyrene QFE query recovery recycler recycling searchfeed select server software solvent Stem table thinner timestamp togo tomcat Uri waste web yahoo

Debugging with Oracle Flashback

Disambiguation 'SYSTIMESTAMP', 'SYSTIMESTAMPs'

Your search yielded multiple results, here are top results:
Debugging with Oracle Flashback


When developing complex applications that run with database backend, sometimes things do not work out as planned. Take an example of implicit record locking by flag. Suppose that large number of users are competing over work queues defined in queue table and flag for locked record is deployed. You will most likely write synchronization routine that atomically locks row for update to make sure that multiple members do not obtain record at the same time. But what if during processing somehow record is unlocked through some update. If your synchronization fails and you need to track what happened in past, Oracle can be of a great help with its flashback query.

Enabling flashback on Oracle

You may wonder what steps were taken before the whole locking logic unraveled. If your Oracle has flashback query enabled, you have struck gold here. Flashback query is not enabled by default and must be turned on in following sequence. We will set retention to 10 hours (600 minutes), set recovery size up to 2 GB in file "/recovery/flashback":

shutdown immediate;
startup mount;
alter database archivelog;
alter system set
DB_FLASHBACK_RETENTION_TARGET=600;
alter system set DB_RECOVERY_FILE_DEST_SIZE=2G;
alter system set DB_RECOVERY_FILE_DEST='/recovery/flashback';
alter database flashback on;
alter database open;What happened at 2:40:16 pm

If you had your flashback enabled and you need to trace back what happened, use AS OF query. In following example we will retrieve status of four records as of 2:40:16pm:

Scroll down for additional info labeled Digging back in time

More info: Digging back in time

permanent link to article http://freecash.hogger.net/debugging_with_oracle_flashback

SELECT * FROM MY_TABLE
AS OF TIMESTAMP (TRUNC(SYSTIMESTAMP)
+ INTERVAL '14' HOUR
+ INTERVAL '40' MINUTE
+ INTERVAL '16' SECOND)
WHERE PRIMARY_KEY_ID IN (177, 237, 422, 1556);
What changed

Similarly you may wonder when or what was deleted or modified. With flashback query you can simply check what has been deleted between 2 and 1 hours ago

(SELECT * FROM MY_TABLE AS OF TIMESTAMP(SYSTIMESTAMP - INTERVAL '2' HOUR))
MINUS
(SELECT * FROM MY_TABLE AS OF TIMESTAMP(SYSTIMESTAMP - INTERVAL '1' HOUR))
Hunting down the issue

Flashback query can help you hunt down an issue quickly and accurately by giving you a time window opportunity to look back and analyze initial conditions. Oracle offers full query log and rollback so you can pinpoint which query from what user caused the problem, but that is a topic for another article.

Re: Isolating FTP user with IIS Manager based credentials
Were you tricked by bluehost.com into hosting only to find your account suspended after you could no longer dispute the charge?
gmail has problems over and over - but don't give up on them - it's only the web interface
The truth about free credit repair services
Should I be concerned with secret GOOGLE technology?
HP Color LaserJet 2550n - How to reset toner count
The funniest support issue this year so far
presidential election polls