Coskan’s Approach to Oracle

March 9, 2007

converting scn to a timestamp

Filed under: How To, PL/SQL — coskan @ 2:58 pm

I saw the nice function below while i was reading about locking and latches section of Mr Thomas Kytes Book (Expert Oracle Database Architecture 9i and 10g Programming Techniques and Solutions) and i said to myself i have to share it for the ones like me :)

If you know the SCN (system change number) you can get its timestamp value (within about +/–3 seconds) by the function scn_to_timestamp. After looking to the manual for more info i saw two other nice functions about scn. They are all under DBMS_FLASHBACK package and not available for the releases prior to 10g. I found these functions useful for dataguard issues recovery issues and flashback issues.

GET_SYSTEM_CHANGE_NUMBER: for getting the current system change number of the database.

SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from dual;

GET_SYSTEM_CHANGE_NUMBER
————————
884871

SCN_TO_TIMESTAMP: for converting given scn to timestamp value ;

SQL> select scn_to_timestamp(884871) as timestamp from dual;

TIMESTAMP
—————————————————————————
09/03/2007 14:52:02,000000000

TIMESTAMP_TO_SCN : For getting SCN equivalent of the given timestamp value. You must do to_timestamp convertion for the character value.

SQL> select timestamp_to_scn(to_timestamp(’08/03/2007 14:24:54′,’DD/MM/YYYY HH24:MI:SS’)) as scn from dual;

SCN
———-
845396

References Used:

Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solution Mr Thomas Kytes Book Pg 244

Oracle® Database PL/SQL Packages and Types Reference
10g Release 2 (10.2)

About these ads

6 Comments »

  1. Some related nice features are ORA_ROWSCN Pseudocolumn and AS OF Flashback Query;

    DROP TABLE tt PURGE ;
    CREATE TABLE tt (c1 NUMBER, c2 VARCHAR2(16), c3 DATE) ;
    INSERT INTO tt VALUES (1, ‘A’, SYSDATE);
    INSERT INTO tt VALUES (2, ‘B’, SYSDATE + 1);
    COMMIT;
    SELECT ORA_ROWSCN, a.* FROM tt a;

    ORA_ROWSCN C1 C2 C3
    ———- ———- —————- ———-
    1345542 1 A 09/03/2007
    1345542 2 B 10/03/2007

    UPDATE tt SET c1 = c1 + 1 WHERE c2 = ‘B’ ;
    COMMIT;
    SELECT ORA_ROWSCN, a.* FROM tt a;

    ORA_ROWSCN C1 C2 C3
    ———- ———- —————- ———-
    1345548 1 A 09/03/2007
    1345548 3 B 10/03/2007

    SELECT c1 FROM tt AS OF SCN 1345542 WHERE c2 = ‘B';

    C1
    ———-
    2

    DROP TABLE tt PURGE ;
    CREATE TABLE tt (c1 NUMBER, c2 VARCHAR2(16), c3 DATE)
    ROWDEPENDENCIES ;
    INSERT INTO tt VALUES (1, ‘A’, SYSDATE);
    INSERT INTO tt VALUES (2, ‘B’, SYSDATE + 1);
    COMMIT;
    SELECT ORA_ROWSCN, a.* FROM tt a;

    ORA_ROWSCN C1 C2 C3
    ———- ———- —————- ———-
    1345618 1 A 09/03/2007
    1345618 2 B 10/03/2007

    UPDATE tt SET c1 = c1 + 1 WHERE c2 = ‘B’ ;
    COMMIT;
    SELECT ORA_ROWSCN, a.* FROM tt a;

    ORA_ROWSCN C1 C2 C3
    ———- ———- —————- ———-
    1345618 1 A 09/03/2007
    1345625 3 B 10/03/2007

    Comment by H.Tonguç Yılmaz — March 9, 2007 @ 9:16 pm

  2. yeuvj uvqwcelt mnyfzc ocqd vjhyfn uomkpj jxar

    Comment by dlkr vdkemhoi — September 8, 2008 @ 8:53 am

  3. Any chance we can find SCN with respect to timestamp in 9i?

    Comment by jcnars — December 27, 2010 @ 5:12 pm

  4. Slightly flawed:-

    select timestamp_to_scn(to_timestamp(’11-09-08 16:54:58′,’YY-MM-DD HH24:MI:SS’))as scn from dual;

    SCN
    ——————
    9669359567320

    select scn_to_timestamp(9669359567320) as timestamp from dual;

    TIMESTAMP
    —————————————————————————
    08-SEP-11 04.51.53.000000000 PM

    Comment by AllanW — September 9, 2011 @ 12:03 pm

  5. Hi,

    Is there any limitation as to how far back the scn can be referred?
    SQL> select scn_to_timestamp() from dual;
    select scn_to_timestamp() from dual
    *
    ERROR at line 1:
    ORA-08181: specified number is not a valid system change number
    ORA-06512: at “SYS.SCN_TO_TIMESTAMP”, line 1

    I got the scn value from STANDBY_BECAME_PRIMARY_SCN column of v$database

    Comment by vijay — January 11, 2013 @ 4:09 pm

  6. Hi there! This post could not bbe written any better!
    Reading this post reminds me of my old room mate! He always kept chating aboout this.
    I will forward this post to him. Fairly certain he wwill have a good read.

    Thank you for sharing!

    Comment by ogłoszenia drobne — May 15, 2014 @ 1:09 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Silver is the New Black Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 202 other followers

%d bloggers like this: