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!