Is CHECKPOINT_CHANGE# in V$DATABASE ...

[JMac]

the current SCN? If it isn't where can I find it?

(I should know this, shouldn't I? Be gentle, it is Friday ...)

[fraze]

yep, you're right, thats the current scn#

[SMarkelenkov]

Originally posted by fraze
yep, you're right, thats the current scn#

Are you sure?

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
           2078477

SQL> create table t (n number);

Table created.

SQL> insert into t values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
           2078477

SQL>

To JMac

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 2079493

SQL>

[rajabalachandra]

hi,

how do i take current scn from oracle 8i?

-Raja

[SMarkelenkov]

Originally posted by rajabalachandra
hi,

how do i take current scn from oracle 8i?

-Raja

Hi! Try this:

SQL> create global temporary table t ( x number );

Table created.

SQL> create or replace function get_scn return number
  2  as
  3      pragma autonomous_transaction;
  4      l_scn number;
  5  begin
  6      insert into t values ( userenv('commitscn') ) returning x into l_scn;
  7      commit;
  8      return l_scn;
  9  end;
 10  /

Function created.

SQL> select get_scn from dual;

   GET_SCN
----------
   2086229

SQL>

[rajabalachandra]

hi,
this is the response i get.
what shall i do?

SQL> create global temporary table t ( x number );

Table created.

SQL>
SQL>
SQL> create or replace function get_scn return number
2 as
3 pragma autonomous_transaction;
4 l_scn number;
5 begin
6 insert into t values ( userenv('commitscn') ) returning x into l_scn;
7 commit;
8 return l_scn;
9 end;
10
11 /

Function created.

SQL> select get_scn from dual;
select get_scn from dual
*
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "SYS.GET_SCN", line 7
ORA-06512: at line 1

[kris109]

You can get the SCN of the most recently committed (local) transaction with the following query:

select max(ktuxescnw * power(2, 32) + ktuxescnb) from x$ktuxe;

[Thomasps]

------------

[abhaysk]

Thomasps :

I think SMarkelenkov has pointed out that...!!

[rajabalachandra]

select max(ktuxescnw * power(2, 32) + ktuxescnb) from x$ktuxe;

it works.

thank you kris!

-Raja