ITPub博客

首页 > 数据库 > Oracle > ora_rowscn伪列介绍和使用

ora_rowscn伪列介绍和使用

原创 Oracle 作者:liang573728 时间:2019-05-11 11:21:04 0 删除 编辑

下面一段英文是官方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.

[@more@]


主要是这么几点:
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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2005-10-15

  • 博文量
    133
  • 访问量
    97338