Forgot your password?    
+ Reply to Thread
Results 1 to 4 of 4

Thread: Information about SCN

  1. #1
    Oracle DBA
    Join Date
    Oct 2011
    Posts
    17

    Smile Information about SCN

    SCN, the System Change Number

    The scn is an ever-increasing number. It can be used to determine the "age" of the database and its component datafiles.
    The current system SCN can be queried using dbms_flashback.get_system_change_number.
    The SCN of the last checkpoint can be found in v$database.checkpoint_change#.
    The SCN is needed in a flashback table .. to scn ... statement.

    Is database in a consistent state?
    The SCN plays an important role to determine if the database is in a consistent state when the database is brought online. SMON checks the SCN in all datafile headers when the database is started. Everything is OK if all of these SCNs matches the SCN found in the controlfile. If the SCNs don't match, the database is in an inconsistent state.

    Converting a time to scn
    smon_scn_time allows to roughly find out which SCN was current for a specific time in the last five days.

    Incrementing SCN
    The SCN is incremented whenever a transaction commits. However, this is not the only source of increments. In a seemingly idle database, the SCN gets incremented also through AQ, SMON, job queues...
    The role of the SCN for consistent reads
    The SCN plays a vital role for providing consistent reads.
    Basically, it works as follows: The query reads a db block. This block has as an attribute the SCN when it was last changed. If this SCN is greater than the SCN that was in place when (our) query began, it means that the block was changed after we have started our query. So we have to find an older version of the block. If this block is found in the rollback segments, we use it for our query.

    Misc
    The v$ views use change# to report SCN values. Hence, it can be argued that SCN means System Change Number, not System Commit Number.
    When exporting, an SCN can be specified with the exp_flashback_scn export parameter.
    With Oracle 10g, it is possible to go back to a specific SCN on a table with flashback table to scn.
    See also the ora_rowscn pseudo column.
    scn_to_timestamp

  2. #2
    Oracle DBA
    Join Date
    Oct 2011
    Posts
    11
    It's really useful, thanks buddy keep it up.

  3. #3
    Oracle Administrator
    Join Date
    Oct 2011
    Location
    Venice
    Posts
    40
    Thanks for sharing Harsh. Its nice knowledge base !
    Cheers !
    pk.ora

  4. #4
    Oracle Administrator
    Join Date
    Dec 2011
    Posts
    92
    Hi harsh nice article.

    I would like to share a bit of information.

    As you said the current scn number can be found by querying the dbms_flashback.get_system_change_number,

    Ex: select dbms_flashback.get_system_change_number FROM dual;

    but you can also see the current scn from v$database.

    Ex: select current_scn from v$database;

+ Reply to Thread

Similar Threads

  1. Replies: 0
    Last Post: 07-27-2012, 06:04 AM
  2. Maintain Snapshot Information to update and create Snapshots
    By Hemant in forum AD Utilities , Autoconfig , FND Utilities
    Replies: 0
    Last Post: 10-30-2011, 10:21 AM

Posting Permissions

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

DBA Lounge (P) Ltd. deals in Oracle Technologies on Consulting, Resourcing, Corporate Training


Online and corporate training available on Oracle Database 11g, Oracle 11g Real Application Cluster (RAC), Oracle Applications 11i/R12, Oracle Fusion Middleware 11g, Oracle Identity Management-OIM, Oracle Internet Directory 11g-OID, Oracle Business Intelligence Enterprise Edition-OBIEE, Oracle Golden Gate, Oracle Access Management-OAM, Oracle Internet Directory-ODS, Oracle Identity Analytics Architecture-OIA Statistics