mysql的rowscn_基于行跟蹤的ROWDEPENDENCIES ORA_ROWSCN資訊

在Oracle 10g中的引入了ORA_ROWSCN僞列新特性。基于此種僞列所提供的資訊，我們可以友善地找出某個資料塊或某一個行最近被修改

的時間戳。在預設情況下，10g下表會以非行依賴性(NOROWDEPENDENCIES)的屬性建立，這意味着我們可觀察的ORA_ROWSCN資訊是以塊級跟蹤的，無法分辨同一塊内的多行間不同的修改時間。為了達到行級粒度的跟蹤我們需要在建表時指定基于行依賴性的ROWDEPENDENCIES字句。如：

SQL> select * from global_name;

GLOBAL_NAME

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

www.oracledatabase12g.com

SQL> select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

PL/SQL Release 10.2.0.4.0 - Production

CORE 10.2.0.4.0 Production

TNS for Linux: Version 10.2.0.4.0 - Production

NLSRTL Version 10.2.0.4.0 - Production

SQL> drop table maclean;

Table dropped.

SQL> create table maclean(t1 int,t2 timestamp) ;

Table created.

SQL> insert into maclean values(1,systimestamp);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into maclean values(2,systimestamp);

1 row created.

SQL> commit;

Commit complete.

SQL> alter session set nls_timestamp_format='hh24:mi:ss';

Session altered.

SQL> col t2 for a35

SQL> col orscn for a35

SQL> select scn_to_timestamp(ora_rowscn) orscn,t2 from maclean;

ORSCN T2

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

20:30:11 20:29:56

20:30:11 20:30:10

create table maclean_rd(t1 int,t2 timestamp) rowdependencies;

Table created.

SQL> select table_name,dependencies from user_tables where dependencies!='DISABLED';

TABLE_NAME DEPENDEN

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

MACLEAN_RD ENABLED

SQL> insert into maclean_rd values(1,systimestamp);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into maclean_rd values(2,systimestamp);

1 row created.

SQL> commit;

Commit complete.

SQL> select scn_to_timestamp(ora_rowscn) orscn,t2 from maclean_rd;

ORSCN T2

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

20:31:26 20:31:25

20:31:35 20:31:37

SQL> select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from maclean_rd;

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)

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

1 94122

1 94122

SQL> alter system dump datafile '/s01/10gdb/oradata/CLINICA/datafile/o1_mf_system_6fp8d3f0_.dbf' block 94122;

System altered.

SQL> oradebug setmypid;

Statement processed.

SQL> oradebug tracefile_name;

/s01/10gdb/admin/clinica/udump/clinica_ora_12934.trc

block_row_dump:

tab 0, row 0, @0x1f88

tl: 24 fb: --H-FL-- lb: 0x0 cc: 2

dscn 0x0000.0351b8bd

col 0: [ 2] c1 02

col 1: [11] 78 6f 01 02 15 20 1a 21 d8 52 68

tab 0, row 1, @0x1f70

tl: 24 fb: --H-FL-- lb: 0x0 cc: 2

dscn 0x0000.0351b8c4

col 0: [ 2] c1 03

col 1: [11] 78 6f 01 02 15 20 26 02 ab c2 f8

SQL> alter table maclean move tablespace users ROWDEPENDENCIES;

alter table maclean move tablespace users ROWDEPENDENCIES

*

ERROR at line 1:

ORA-14133: ALTER TABLE MOVE cannot be combined with other operations