system change number

system change number(scn)就好比oracle内部时钟,它以增加的方式变动着,DB所有的变动都以它为时间基准,它在整个系统中也是唯一的;

Connected to Oracle8i Enterprise Edition Release 8.1.7.3.0

SQL> create table scn_tab ( x number primary key ) ;

Table created

SQL> insert into scn_tab values (0);

1 row inserted

SQL> commit;

Commit complete

SQL>
SQL> create or replace function get_scn return number as
2 pragma autonomous_transaction;
3 l_scn number;
4 --create table scn_tab ( x number primary key ) ;
5 -- insert into scn_tab values (0);
6 --commit;
7 -- get system change number on oracle8i
8 begin
9 update scn_tab set x = userenv('COMMITSCN') returning x into l_scn;
10 commit;
11 return l_scn;
12 end;
13 /

Function created

SQL> select get_scn from dual;

GET_SCN
----------
9129014553

SQL>

Connected to Oracle9i Enterprise Edition Release 9.2.0.7.0

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
4155792

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/45259/viewspace-134882/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/45259/viewspace-134882/