10g:ora_rowscn伪列介绍和使用

10g里为表新增加了一个伪列ora_rowscn
下面一段英文是官方SQL Reference里的说明：

 

For each row,ORA_ROWSCNreturns the conservative upper bound system change number (SCN) of the most recent change to the row. This pseudocolumn is useful for determining approximately when a row was last updated. It is not absolutely precise, because Oracle tracks SCNs by transaction committed for the block in which the row resides. You can obtain a more fine-grained approximation of the SCN by creating your tables with row-level dependency tracking.

Please refer toCREATE TABLE ... NOROWDEPENDENCIES | ROWDEPENDENCIESfor more information on row-level dependency tracking.

You cannot use this pseudocolumn in a query to a view. However, you can use it to refer to the underlying table when creating a view. You can also use this pseudocolumn in the WHERE clause of anUPDATE or DELETEstatement.

ORA_ROWSCNis not supported for Flashback Query. Instead, use the version query pseudocolumns,
which are provided explicitly for Flashback Query.
Please refer to the SELECT ... flashback_query_clause for information on Flashback Query and
"Version Query Pseudocolumns" for additional information on those pseudocolumns.

 

主要是这么几点：
1.默认情况，ora_rowscn记录的scn并不准确，记录的是block的scn。
2.创建表的时候ROWDEPENDENCIES可以来使ora_rowscn真正记录行一级的scn。
3.不能用在查询view时。
4.不能用于flashback query。

可以用来：
1.确认行所在块最后一次修改的scn
2.确认行所在块最后一次修改的大概时间
3.大部分的时候可以确定出两行记录创建的先后顺序(没在同一个块上)
4.如果CREATE TABLE ... ROWDEPENDENCIES 可以精确到行

下面的实验涉及：
1.验证缺省情况Ora_rowscn只记录block级
2.通过scn_to_timestamp 来查询update/insert时间
3.CREATE TABLE ... ROWDEPENDENCIES让ora_rowscn精确到行一级

1.验证缺省情况Ora_rowscn只记录block级
1.1 创建表
SQL> create table emptt as select empno,ename from emp where empno>7700;

表已创建。

1.2 查询ora_rowscn：
SQL> select ora_rowscn,empno,ename from emptt;

ORA_ROWSCN      EMPNO ENAME
---------------------- ---------- ----------
    794054              7782      CLARK
    794054               7788       SCOTT
    794054              7839      KING
    794054              7844       TURNER
    794054              7876       ADAMS
    794054              7900       JAMES
    794054             7902       FORD
    794054              7934       MILLER

已选择8行。

1.3 修改一行：
SQL> update emptt set ename='CLARKCLARK' where empno=7782;

已更新 1 行。

1.4 发现所有行的ora_rowscn都相同，并且rowid显示这些行都在同一个块：
SQL> select rowid,ora_rowscn,empno,ename from emptt;

ROWID                               ORA_ROWSCN EMPNO ENAME
--------------------------------- ----------              ---------- ----------
AAAM2dAAEAAAAG8AAA     794099            7782     CLARKCLARK
AAAM2dAAEAAAAG8AAB     794099            7788     SCOTT
AAAM2dAAEAAAAG8AAC     794099            7839     KING
AAAM2dAAEAAAAG8AAD     794099            7844    TURNER
AAAM2dAAEAAAAG8AAE     794099           7876      ADAMS
AAAM2dAAEAAAAG8AAF     794099            7900     JAMES
AAAM2dAAEAAAAG8AAG     794099          7902      FORD
AAAM2dAAEAAAAG8AAH     794099           7934      MILLER

已选择8行。

1.5 再向表里插入一些数据：
SQL> insert into emptt
2 select empno,ename from emp;

已创建14行。

SQL> commit;

提交完成。

1.6 现在表在两个块上，不同块上的行ora_rowscn分别不同：
SQL> select rowid,ora_rowscn,empno,ename from emptt;

ROWID                                ORA_ROWSCN EMPNO ENAME
--------------------------------- ---------------        ---------- ----------
AAAM2dAAEAAAAG8AAA     794099            7782     CLARKCLARK
AAAM2dAAEAAAAG8AAB     794099            7788     SCOTT
AAAM2dAAEAAAAG8AAC     794099           7839     KING
AAAM2dAAEAAAAG8AAD     794099           7844     TURNER
AAAM2dAAEAAAAG8AAE     794099           7876     ADAMS
AAAM2dAAEAAAAG8AAF     794099           7900     JAMES
AAAM2dAAEAAAAG8AAG     794099           7902     FORD
AAAM2dAAEAAAAG8AAH     794099           7934     MILLER
AAAM2dAAEAAAAG9AAA     794170           7369     SMITH
AAAM2dAAEAAAAG9AAB     794170           7499     ALLEN
AAAM2dAAEAAAAG9AAC     794170           7521     WARD
AAAM2dAAEAAAAG9AAD     794170           7566    JONES
AAAM2dAAEAAAAG9AAE     794170           7654    MARTIN
AAAM2dAAEAAAAG9AAF     794170           7698    BLAKE
AAAM2dAAEAAAAG9AAG     794170           7782    CLARK
AAAM2dAAEAAAAG9AAH     794170           7788    SCOTT
AAAM2dAAEAAAAG9AAI       794170           7839    KING
AAAM2dAAEAAAAG9AAJ     794170            7844    TURNER
AAAM2dAAEAAAAG9AAK     794170           7876    ADAMS
AAAM2dAAEAAAAG9AAL     794170           7900    JAMES
AAAM2dAAEAAAAG9AAM     794170           7902    FORD
AAAM2dAAEAAAAG9AAN     794170           7934    MILLER

已选择22行。

1.7 更新一行再次验证：
SQL> update emptt set ename='CLARKtt' where ename='CLARKCLARK';

已更新 1 行。

SQL> commit;

提交完成。

SQL> select rowid,ora_rowscn,empno,ename from emptt;

ROWID                                ORA_ROWSCN   EMPNO ENAME
----------------------------------   ----------            ---------- ----------
AAAM2dAAEAAAAG8AAA     794199           7782        CLARKtt
AAAM2dAAEAAAAG8AAB     794199           7788        SCOTT
AAAM2dAAEAAAAG8AAC     794199           7839        KING
AAAM2dAAEAAAAG8AAD     794199           7844        TURNER
AAAM2dAAEAAAAG8AAE     794199            7876        ADAMS
AAAM2dAAEAAAAG8AAF     794199           7900        JAMES
AAAM2dAAEAAAAG8AAG     794199          7902         FORD
AAAM2dAAEAAAAG8AAH     794199           7934        MILLER
AAAM2dAAEAAAAG9AAA     794170           7369        SMITH
AAAM2dAAEAAAAG9AAB     794170           7499        ALLEN
AAAM2dAAEAAAAG9AAC     794170           7521        WARD
AAAM2dAAEAAAAG9AAD     794170          7566           JONES
AAAM2dAAEAAAAG9AAE     794170          7654           MARTIN
AAAM2dAAEAAAAG9AAF     794170          7698           BLAKE
AAAM2dAAEAAAAG9AAG     794170          7782          CLARK
AAAM2dAAEAAAAG9AAH     794170          7788          SCOTT
AAAM2dAAEAAAAG9AAI      794170           7839          KING
AAAM2dAAEAAAAG9AAJ     794170           7844          TURNER
AAAM2dAAEAAAAG9AAK     794170          7876          ADAMS
AAAM2dAAEAAAAG9AAL     794170          7900          JAMES
AAAM2dAAEAAAAG9AAM     794170         7902          FORD
AAAM2dAAEAAAAG9AAN     794170         7934          MILLER

已选择22行。

2. 通过scn_to_timestamp 来查询update/insert时间

此功能很有用。
SQL> col update_time format a40
SQL> select scn_to_timestamp(ora_rowscn) update_time,empno,ename from emptt;

UPDATE_TIME                                            EMPNO    ENAME
------------------------------------------------       ----------    ----------
08-5月 -08 10.45.12.000000000 上午             7782   CLARKtt
08-5月 -08 10.45.12.000000000 上午             7788   SCOTT
08-5月 -08 10.45.12.000000000 上午             7839   KING
08-5月 -08 10.45.12.000000000 上午             7844   TURNER
08-5月 -08 10.45.12.000000000 上午             7876   ADAMS
08-5月 -08 10.45.12.000000000 上午             7900   JAMES
08-5月 -08 10.45.12.000000000 上午             7902   FORD
08-5月 -08 10.45.12.000000000 上午             7934 MILLER
08-5月 -08 10.44.09.000000000 上午             7369   SMITH
08-5月 -08 10.44.09.000000000 上午             7499   ALLEN
08-5月 -08 10.44.09.000000000 上午             7521   WARD
08-5月 -08 10.44.09.000000000 上午             7566   JONES
08-5月 -08 10.44.09.000000000 上午             7654   MARTIN
08-5月 -08 10.44.09.000000000 上午             7698   BLAKE
08-5月 -08 10.44.09.000000000 上午             7782 CLARK
08-5月 -08 10.44.09.000000000 上午             7788   SCOTT
08-5月 -08 10.44.09.000000000 上午             7839   KING
08-5月 -08 10.44.09.000000000 上午             7844   TURNER
08-5月 -08 10.44.09.000000000 上午             7876   ADAMS
08-5月 -08 10.44.09.000000000 上午             7900   JAMES
08-5月 -08 10.44.09.000000000 上午             7902 FORD
08-5月 -08 10.44.09.000000000 上午             7934   MILLER

已选择22行。

 

3.CREATE TABLE ... ROWDEPENDENCIES让ora_rowscn精确到行一级

3.1 创建表
SQL> create table empaa rowdependencies as select empno,ename from emp where empno>7700;

表已创建。

3.2 查询
SQL> select rowid,ora_rowscn,empno,ename from empaa;

ROWID                      ORA_ROWSCN      EMPNO ENAME
------------------------------     ----------      ----------    ----------
AAAM3qAAEAAAAJFAAA     799250       7782       CLARK
AAAM3qAAEAAAAJFAAB     799250       7788       SCOTT
AAAM3qAAEAAAAJFAAC     799250       7839       KING
AAAM3qAAEAAAAJFAAD     799250       7844       TURNER
AAAM3qAAEAAAAJFAAE     799250       7876       ADAMS
AAAM3qAAEAAAAJFAAF     799250       7900       JAMES
AAAM3qAAEAAAAJFAAG     799250       7902       FORD
AAAM3qAAEAAAAJFAAH     799250       7934       MILLER

已选择8行。

3.3更新一行并查看结果
SQL> update empaa set ename='CLARKCLARK' where empno=7782;

已更新 1 行。

没有commit的时候ora_rowscn为空
SQL> select rowid,ora_rowscn,empno,ename from empaa;

ROWID                                  ORA_ROWSCN   EMPNO ENAME
-------------------------------      ----------             ---------- ----------
AAAM3qAAEAAAAJFAAA                               7782       CLARKCLARK
AAAM3qAAEAAAAJFAAB     799250             7788       SCOTT
AAAM3qAAEAAAAJFAAC     799250             7839       KING
AAAM3qAAEAAAAJFAAD     799250             7844       TURNER
AAAM3qAAEAAAAJFAAE     799250             7876       ADAMS
AAAM3qAAEAAAAJFAAF     799250             7900       JAMES
AAAM3qAAEAAAAJFAAG     799250             7902       FORD
AAAM3qAAEAAAAJFAAH     799250             7934       MILLER

已选择8行。

SQL> commit;

提交完成。

Commit之后同一块上的ora_rowscn也能对应到独立行:

SQL> select rowid,ora_rowscn,empno,ename from empaa;

ROWID                                  ORA_ROWSCN      EMPNO ENAME
---------------------------------   ----------             ---------- ----------
AAAM3qAAEAAAAJFAAA     799302             7782        CLARKCLARK
AAAM3qAAEAAAAJFAAB     799250             7788        SCOTT
AAAM3qAAEAAAAJFAAC     799250             7839       KING
AAAM3qAAEAAAAJFAAD     799250             7844        TURNER
AAAM3qAAEAAAAJFAAE     799250             7876        ADAMS
AAAM3qAAEAAAAJFAAF     799250             7900        JAMES
AAAM3qAAEAAAAJFAAG     799250             7902        FORD
AAAM3qAAEAAAAJFAAH     799250             7934        MILLER

已选择8行。

 

 

附：一个使用ora_rowscn辅助闪回的例子

--创建测试表
create table test (id int,name varchar2(20)) rowdependencies;
--插入测试数据
insert into test values(1,'a');
insert into test values(2,'b');
insert into test values(3,'c');
insert into test values(4,'d');
insert into test values(5,'e');
insert into test values(6,'f');
insert into test values(7,'g');
insert into test values(8,'h');

commit;
--查看数据
select * from test;

/*ID  NAME
-- -------
1   a
2   b
3   c
4   d
5   e
6   f
7   g
8   h*/

--查看当前ora_rowscn
select id,name,ora_rowscn,dbms_rowid.rowid_block_number(rowid) blockno from test;

/*ID NAME ORA_ROWSCN BLOCKNO
1 a 8249759 288
2 b 8249759 288
3 c 8249759 288
4 d 8249759 288
5 e 8249759 288
6 f 8249759 288
7 g 8249759 288
8 h 8249759 288*/

--执行更新
update test set name='8' where id=8;

commit

--查看更新后的数据
select * from test;

/*ID  NAME
-- -------
1   a
2   b
3   c
4   d
5   e
6   f
7   g
8   8*/

--查看更新后的ora_rowscn
select id,name,ora_rowscn,dbms_rowid.rowid_block_number(rowid) blockno from test;

/*ID NAME ORA_ROWSCN BLOCKNO
1 a 8249759 288
2 b 8249759 288
3 c 8249759 288
4 d 8249759 288
5 e 8249759 288
6 f 8249759 288
7 g 8249759 288
8 8 8249896 288*/

--现在可以通过闪回查询恢复这条记录
select id,name,ora_rowscn,to_char(scn_to_timestamp(ora_rowscn),'yyyy-mm-dd hh24:mi:ss') time,dbms_rowid.rowid_block_number(rowid) blockno from test;
/*ID NAME ORA_ROWSCN TIME BLOCKNO
1 a 8249759 2008-08-01 10:04:19 288
2 b 8249759 2008-08-01 10:04:19 288
3 c 8249759 2008-08-01 10:04:19 288
4 d 8249759 2008-08-01 10:04:19 288
5 e 8249759 2008-08-01 10:04:19 288
6 f 8249759 2008-08-01 10:04:19 288
7 g 8249759 2008-08-01 10:04:19 288
8 8 8249896 2008-08-01 10:08:22 288
*/
select * from test as of timestamp to_date('2008-08-01 10:08:22','yyyy-mm-dd hh24:mi:ss')

--然后删除这条错误记录
delete from test where id=8
--将闪回查询的数据插入原表
insert into test select * from test as of timestamp to_date('2008-08-01 10:08:22','yyyy-mm-dd hh24:mi:ss') where id=8
--查看数据
select * from test

--至此借助ora_rowscn恢复完毕

--如果这个这个表包含LONG数据类型，就采用EXP/IMP方式进行恢复

C:\>exp test/test@acf file=test.dmp tables=test flashback_scn=8249896 query=\"where id=8\"

--然后删除这条错误记录
delete from test where id=8;

--最后倒入这条记录完成恢复
C:\>imp test/test@acf file=test.dmp ignore=y