How do you get the latest SCN in the database?

[dba_admin]

How do you get the latest SCN in the database?

Thank you!

[mrvajrala]

I am not so sure,
but pl query
select * from v$controlfile_record_section

Thanks,
Madhu

[mrvajrala]

I think another table is also there,,,

v$log_history or in alerts.log file too

[julian]

9i:

select flashback.get_system_change_number from dual;

[jmodic]

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]

[pravin]

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

[dba_admin]

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.

[murugappan]

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

[dba_admin]

No.