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]
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?