Oracle's Flashback Query - TIMESTAMP or SCN?

June 18, 2009

Oracle’s Flashback Query(SQL-driven) makes use of both TIMESTAMP and SCN--but which should you use? It just may be a matter of preference but requires some thoughtful considerations.

In order to take advantage of Oracle’s flashback query feature, for SQL-driven flashback, the SELECT statement makes use of the AS OF or VERSIONS BETWEEN clause to retrieve data from the past from tables, views, or materialized views.

SELECT ... FROM ...
VERSIONS BETWEEN { SCN | TIMESTAMP } { expr | MINVALUE } 
	AND { expr | MAXVALUE } ]
-or-
AS OF { SCN | TIMESTAMP } expr

Both the VERSIONS BETWEEN and the AS OF clause requires the user to code in the SQL an SCN or TIMESTAMP for proper retrieval of past information. The question often becomes should I use an SCN or should I use a TIMESTAMP? The answer is not always clear but I’ll investigate both here to help you grapple with your next decision.

First, it makes complete sense to define what a TIMESTAMP and an SCN is:

TIMESTAMP is an extension of the DATE datatype, which stores all the information that the DATE datatype stores (month, day, year, hour, minute, second) but also includes fractional seconds. Personally, I find this a bit confusing since date, by the strictest definition, is JUST a particular month, day, and year when an event happened. Regardless, since TIMESTAMP is the datatype required for a SQL-driven the flashback query, the following SQL conversions could come in handy:

Just as there is a call to SYSDATE to get the current system date and time, there is a call to get the current system TIMESTAMP.

SQL> SELECT SYSTIMESTAMP FROM dual;
SYSTIMESTAMP
-------------------------------------
11-JUN-09 10.17.52.622699 AM -06:00

IF you're interested in the current session TIMSTAMP in relation to the session time zone (SESSIONTIMEZONE) use the CURRENT_TIMESTAMP function call.

SQL> SELECT CURRENT_TIMESTAMP FROM dual;
CURRENT_TIMESTAMP
-------------------------------------------
11-JUN-09 10.18.02.510724 AM -06:00

Moreover, the old standby is to convert character datatypes for a known timestamp to a TIMESTAMP datatype:

SQL> SELECT TO_TIMESTAMP ('20090611 11:05:00', 'YYYYMMDD HH24:MI:SS') FROM dual;
TO_TIMESTAMP('2009061111:05:00','YYYYMMDDHH24:MI:SS')
-----------------------------------------------------
11-JUN-09 11.05.00.000000000 AM

If you must use sysdate, which isn't the best option, you would have to do:

SQL> SELECT TO_TIMESTAMP(TO_CHAR(sysdate,'YYYYMMDD HH24:MI:SS'),'YYYYMMDD HH24:MI:SS') FROM dual;
TO_TIMESTAMP(TO_CHAR(SYSDATE,'YYYYMMDDHH24:MI:SS'),'YYYYMMDDHH24:MI:SS')
---------------------------------------------------------------------------
11-JUN-09 10.01.18.000000000 AM

The use of TIMESTAMPs become very important if you know a particular point in time or want to look for something within a time period as shown in the following two SQL statements.

To look at what a specific value was at a point in time; say 10 minutes ago:

SELECT * 
  FROM flash_test 
    AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' MINUTE);

To look at what values were during a particular time period; say between 10 minutes ago and now:

SELECT * 
  FROM flash_test 
  VERSIONS BETWEEN TIMESTAMP
    SYSTIMESTAMP - INTERVAL '10' MINUTE AND SYSTIMESTAMP;

SCN (System Change Number) is nothing more than a stamp (number) that defines a committed version of a database at a point in time. This means that every committed transaction is assigned a unique SCN. Worthy to note, Oracle will also perform internal work that generates SCNs. I think TIMESTAMP has probably overtaken the use of SCN mostly because many just don't know where to extract a valid SCN from. Here are a few areas that could help you in this endeavor.

Current SCN of the database.

SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() FROM DUAL;
DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()
-----------------------------------------
                                  2249659

SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-----------
    2249662

Just remember that there are literally 100's of tables and views within Oracle that maintain some form of an SCN. Use the following SQL statement to find which ones you might want to use for your next flashback query.

SELECT owner, table_name, column_name
  FROM dba_tab_columns
 WHERE column_name LIKE '%SCN%'

OWNER TABLE_NAME                     COLUMN_NAME
----- ------------------------------ ------------------------------
SYS   V_$DATABASE                    CURRENT_SCN
SYS   V_$DATABASE                    STANDBY_BECAME_PRIMARY_SCN
SYS   V_$DATAFILE                    FIRST_NONLOGGED_SCN
SYS   V_$FILESPACE_USAGE             CHANGESCN_BASE
SYS   V_$FILESPACE_USAGE             CHANGESCN_WRAP
SYS   V_$FLASHBACK_DATABASE_LOG      OLDEST_FLASHBACK_SCN
SYS   V_$KCCDI                       DICKP_SCN
SYS   V_$KCCDI                       DICUR_SCN
SYS   V_$KCCDI                       DISSC_SCN
SYS   V_$KCCDI                       DISCN
SYS   V_$KCCFE                       FECRC_SCN
SYS   V_$KCCFE                       FEONC_SCN
SYS   V_$KCCFE                       FEPLG_SCN
SYS   V_$LOGMNR_CONTENTS             CSCN
SYS   V_$LOGMNR_CONTENTS             SAFE_RESUME_SCN
SYS   V_$LOGMNR_CONTENTS             SCN
SYS   V_$LOGMNR_CONTENTS             START_SCN
SYS   V_$LOGMNR_CONTENTS             COMMIT_SCN
SYS   V_$LOGMNR_DICTIONARY           RESET_SCN_TIME
SYS   V_$LOGMNR_DICTIONARY           DB_TXN_SCN
SYS   V_$LOGMNR_DICTIONARY           RESET_SCN
SYS   V_$LOGMNR_DICTIONARY           DICTIONARY_SCN
SYS   V_$LOGMNR_LOGFILE              LOW_SCNBAS
SYS   V_$LOGMNR_LOGFILE              RESET_SCNWRP
SYS   V_$LOGMNR_LOGFILE              RESET_SCNBAS
SYS   V_$LOGMNR_LOGFILE              RESET_SCN_TIME
SYS   V_$LOGMNR_LOGFILE              NEXT_SCNWRP
SYS   V_$LOGMNR_LOGFILE              NEXT_SCNBAS
SYS   V_$LOGMNR_LOGFILE              LOW_SCNWRP
SYS   V_$LOGMNR_LOGS                 NEXT_SCN
SYS   V_$LOGMNR_LOGS                 LOW_SCN
SYS   V_$LOGMNR_LOGS                 RESET_SCN_TIME
SYS   V_$LOGMNR_LOGS                 RESET_SCN
SYS   V_$LOGMNR_PARAMETERS           START_SCN
SYS   V_$LOGMNR_PARAMETERS           END_SCN
SYS   V_$LOGMNR_PARAMETERS           REQUIRED_START_SCN
SYS   V_$LOGMNR_SESSION              END_SCN
SYS   V_$LOGMNR_SESSION              READ_SCN
SYS   V_$LOGMNR_SESSION              RESET_SCN
SYS   V_$LOGMNR_SESSION              LOW_MARK_SCN
SYS   V_$LOGMNR_SESSION              CONSUMED_SCN
SYS   V_$LOGMNR_SESSION              PROCESSED_SCN
SYS   V_$LOGMNR_SESSION              START_SCN
SYS   V_$LOGMNR_SESSION              SPILL_SCN
SYS   V_$LOGMNR_SESSION              PREPARED_SCN
...
And MANY MORE..

Use of an SCN in SQL-driven flashback queries isn’t much different than using a TIMESTAMP. Just plug in your SCN number.

SELECT * 
  FROM flash_test 
    AS OF SCN 2249100;

SELECT * 
  FROM flash_test 
  VERSIONS BETWEEN SCN 2249600 AND 2249659;

You can also play around with converting from a TIMESTAMP to an SCN and an SCN to a TIMESTAMP. These come in handy if you want to standardize on a particular method or help you in pinpointing which is better to use during recovery scenarios.

Convert SCN to TIMESTAMP.

SQL> SELECT SCN_TO_TIMESTAMP(2249659) FROM dual;

SCN_TO_TIMESTAMP(2249659)
------------------------------------------------
11-JUN-09 10.30.09.000000000 AM

Convert TIMESTAMP to SCN
SQL> SELECT TIMESTAMP_TO_SCN('11-JUN-09 10.30.09.000000000 AM') FROM dual;

TIMESTAMP_TO_SCN('11-JUN-0910.30.09.000000000AM')
-------------------------------------------------
                                          2249659

Probably worth mentioning, since flashback query isn't "supposed" to work across database bounces, is the following query to extract the initial TIMESTAMP and SCN from instance startup.

SQL> SELECT TO_TIMESTAMP(TO_CHAR(startup_time,'YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS') TIMESTAMP,
  2         TIMESTAMP_TO_SCN(TO_TIMESTAMP(TO_CHAR(startup_time,'YYYYMMDDHH24MISS'),'YYYYMMDDHH24MISS') ) SCN
  3    FROM v$instance;

TIMESTAMP                                  SCN
----------------------------------- ----------
11-JUN-09 06.49.04.000000000 AM        2237632

Oftentimes, but not always, flashback queries that attempt to SELECT data before these times will generate the following error:

SQL> SELECT *
  2    FROM flash_test
  3      AS OF SCN 1000000;
  FROM flash_test
       *
ERROR at line 2:
ORA-01466: unable to read data - table definition has changed

Determining to use an SCN or TIMESTAMP for flashback queries seems to be a matter of comfort. I think it really comes down to whether you’re comfortable working with times or numbers. Worthy to note, since there are various human factors that come into play when discussing time (TIMEZONE and client-side wall clock or system time to name just a couple) it may be better to always convert to a real database server side TIMESTAMP and then an SCN. Moreover, this sounds much more professional, can help obfuscate the issue, and make you sound like you’re remaining technical; always a plus!

» See All Articles by Columnist James Koopmann