How do you get the latest SCN in the database?
I am not so sure,
but pl query
select * from v$controlfile_record_section
I think another table is also there,,,
v$log_history or in alerts.log file too
select flashback.get_system_change_number from dual;
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');
SELECT c1 AS current_SCN FROM x1;
2. You can use the following query:
SELECT MAX(ktuxescnw * POWER(2, 32) + ktuxescnb) FROM x$ktuxe;
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]
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
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.
SQL> INSERT INTO x1(c1) VALUES (USERENV('COMMITSCN'));
1 row created.
SQL> SELECT c1 AS current_SCN FROM x1;
1 row selected.
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;
Click Here to Expand Forum to Full Width