How do you get the latest SCN in the database?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: How do you get the latest SCN in the database?

  1. #1
    Join Date
    Mar 2001
    Posts
    286
    How do you get the latest SCN in the database?

    Thank you!

  2. #2
    Join Date
    Feb 2001
    Posts
    290
    I am not so sure,
    but pl query
    select * from v$controlfile_record_section

    Thanks,
    Madhu

  3. #3
    Join Date
    Feb 2001
    Posts
    290
    I think another table is also there,,,

    v$log_history or in alerts.log file too

  4. #4
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    9i:

    Code:
    select flashback.get_system_change_number from dual;

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    For pre-9i database you have two options:

    1. Use undocumented function USERENV('COMMITSCN').
    The little quirk with this functon is that you can use it in only restricted ways. For example, you can't simply use it in selects like:
    SELECT USERENV('COMMITSCN') FROM dual; -- You'll get ORA-1725

    But you can insert the value it returns in a number type column of a table, e.g.:

    CREATE TABLE x1 (c1 NUMBER);
    INSERT INTO x1(c1) VALUES USERENV('COMMITSCN');
    COMMIT;
    SELECT c1 AS current_SCN FROM x1;

    2. You can use the following query:
    SELECT MAX(ktuxescnw * POWER(2, 32) + ktuxescnb) FROM x$ktuxe;

    P.S.
    Neither of the suggestions of mrvajrala gets you the latest SCN of the database - in v$controlfile_record_section there is no trace of any information about SCN (even if it was, it could only have been the SCN at the latest checkpoint time), while in v$log_history there are only SCNs at log switches.

    [Edited by jmodic on 02-14-2002 at 04:53 PM]
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Dec 2001
    Location
    SAN FRANCISCO, CA
    Posts
    306
    jurij

    Ur in depth of knowledge in Oracle is really great.

    And above that ur way of explaining things to the questions posted in this forum is very appreciable.

    and honestly i am a very big fan of ur and jeff.

    Pravin

  7. #7
    Join Date
    Mar 2001
    Posts
    286

    Thank you!

    Thank you!

    SQL> INSERT INTO x1(c1) VALUES (USERENV('COMMITSCN'));

    1 row created.

    SQL> SELECT c1 AS current_SCN FROM x1;

    CURRENT_SCN
    -----------
    554635

    1 row selected.

  8. #8
    Join Date
    Feb 2001
    Posts
    49

    Hi,

    You can also find this way

    $ sqlplus internal

    SQL > col name format a40

    SQL>select file#,name,to_char(checkpoint_time, 'HH24:Mm:SS') "Last CheckPoint" from v$datafile_header;


    HTH

  9. #9
    Join Date
    Mar 2001
    Posts
    286
    No.

Posting Permissions

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



Click Here to Expand Forum to Full Width