Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2012
    Posts
    15

    Unanswered: Retrieve old value AFTER update

    Is it possible to get the old value which was updated to new value by using 'SELECT' statement?

    Consider if a value 'NO' in oracle table is updated to 'YES' by simple update statement. Can I get the original value 'No' by using select statement without modifying the original update statement?

  2. #2
    Join Date
    Jan 2002
    Posts
    165
    select ... as of timestamp...
    cheers

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    when all else fails, Read The Fine Manual
    UPDATE
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Feb 2012
    Posts
    44
    Quote Originally Posted by zkajfez View Post
    select ... as of timestamp...
    cheers
    works only if you enabled Flashback

    Enabling Flashback Database

    Follow these are the steps to enable Flashback Database.

    To enable flashback logging:

    Ensure the database instance is open or mounted. If the instance is mounted, then the database must be shut down cleanly unless it is a physical standby database. Other Oracle RAC instances can be in any mode.

    Optionally, set the DB_FLASHBACK_RETENTION_TARGET to the length of the desired flashback window in minutes:

    ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320; # 3 days

    By default DB_FLASHBACK_RETENTION_TARGET is set to 1 day (1440 minutes).

    Enable the Flashback Database feature for the whole database:

    ALTER DATABASE FLASHBACK ON;

    Optionally, disable flashback logging for specific tablespaces.

    By default, flashback logs are generated for all permanent tablespaces. You can reduce overhead by disabling flashback logging for specific tablespaces as in the following example:

    ALTER TABLESPACE tbs_3 FLASHBACK OFF;

    You can re-enable flashback logging for a tablespace later with this command:

    ALTER TABLESPACE tbs_3 FLASHBACK ON;

    If you disable Flashback Database for a tablespace, then you must take its datafiles offline before running FLASHBACK DATABASE.

    When you enable Flashback Database while the database is open, there is a very small chance the command may not be able to obtain the memory it needs. If the command fails because of that reason, retry the command after a while or retry after a shutdown and restart of the instance.

    When you enable Flashback Database on a physical standby database, you can flash back a standby database. Flashback Database of standby databases has some applications in the Data Guard environment. See Oracle Data Guard Concepts and Administration for details.
    Using Flashback Database and Restore Points

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •