下面一段英文是官方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级
SQL> create table dept3 as select * from dept where deptno = 10;
表已创建。
SQL> commit;
提交完成。
SQL> select * from dept3;
DEPTNO DNAME LOC
---------- -------------------- --------------------
10 Sales Virginia,USA
10 Sales Virginia,USA
10 Sales Virginia,USA
10 Sales Virginia,USA
10 Sales Virginia,USA
SQL> insert into dept3 values('20','HR','China');
已创建 1 行。
SQL> insert into dept3 values('30','Fin','China');
已创建 1 行。
SQL> commit;
提交完成。
SQL> select a.*, ora_rowscn from dept3 a;
DEPTNO DNAME LOC ORA_ROWSCN
---------- -------------------- -------------------- ----------
10 Sales Virginia,USA 23701744
10 Sales Virginia,USA 23701744
10 Sales Virginia,USA 23701744
10 Sales Virginia,USA 23701744
10 Sales Virginia,USA 23701744
20 HR China 23702029
30 Fin China 23702029
已选择7行。
SQL>
SQL> update dept3 set dname = 'IS' where deptno = 30;
已更新 1 行。
SQL> commit;
提交完成。
SQL> select a.*, ora_rowscn from dept3 a;
DEPTNO DNAME LOC ORA_ROWSCN
---------- -------------------- -------------------- ----------
10 Sales Virginia,USA 23701744
10 Sales Virginia,USA 23701744
10 Sales Virginia,USA 23701744
10 Sales Virginia,USA 23701744
10 Sales Virginia,USA 23701744
20 HR China 23702158
30 IS China 23702158
已选择7行。
SQL>
相同块的scn被一起更新了。
2 CREATE TABLE ... ROWDEPENDENCIES让ora_rowscn精确到行一级
SQL>
SQL> create table dept2 rowdependencies as select * from dept3;
表已创建。
SQL> commit;
提交完成。
SQL> select a.*, ora_rowscn from dept2 a;
DEPTNO DNAME LOC ORA_ROWSCN
---------- -------------------- -------------------- ----------
10 Sales Virginia,USA 23702365
10 Sales Virginia,USA 23702365
10 Sales Virginia,USA 23702365
10 Sales Virginia,USA 23702365
10 Sales Virginia,USA 23702365
20 HR China 23702365
30 IS China 23702365
已选择7行。
SQL>
所有reocrd都在同一个block上
SQL> select a.*, ora_rowscn from dept2 a;
DEPTNO DNAME LOC ORA_ROWSCN
---------- -------------------- -------------------- ----------
10 Sales Virginia,USA 23702365
10 Sales Virginia,USA 23702365
10 Sales Virginia,USA 23702365
10 Sales Virginia,USA 23702365
10 Sales Virginia,USA 23702365
20 HR China 23702365
30 SN China
已选择7行。
没有commit之前,scn没有显示
SQL> commit;
提交完成。
SQL> select a.*, ora_rowscn from dept2 a;
DEPTNO DNAME LOC ORA_ROWSCN
---------- -------------------- -------------------- ----------
10 Sales Virginia,USA 23702365
10 Sales Virginia,USA 23702365
10 Sales Virginia,USA 23702365
10 Sales Virginia,USA 23702365
10 Sales Virginia,USA 23702365
20 HR China 23702365
30 SN China 23702469
已选择7行。
SQL>
commit后,deptno=30的reocrd的scn出现变化
3 通过scn_to_timestamp 来查询update/insert时间
SQL> col ts format a40
SQL> select a.deptno,a.dname, ora_rowscn, scn_to_timestamp(ora_rowscn) ts from dept2 a;
DEPTNO DNAME ORA_ROWSCN TS
---------- ---------- ---------- ----------------------------------------
10 Sales 23702365 24-3月 -11 03.09.32.000000000 下午
10 Sales 23702365 24-3月 -11 03.09.32.000000000 下午
10 Sales 23702365 24-3月 -11 03.09.32.000000000 下午
10 Sales 23702365 24-3月 -11 03.09.32.000000000 下午
10 Sales 23702365 24-3月 -11 03.09.32.000000000 下午
20 HR 23702365 24-3月 -11 03.09.32.000000000 下午
30 SN 23702469 24-3月 -11 03.12.26.000000000 下午
已选择7行。
SQL>
4 附:一个使用ora_rowscn辅助闪回的例子
Sql代码
--创建测试表
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
--或者
insert into test select * from test as of timestamp scn_to_timestamp(8249896);
--查看数据
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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/640706/viewspace-1047687/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/640706/viewspace-1047687/