ora_rowscn伪列介绍和使用

最新推荐文章于 2021-04-10 12:30:02 发布

cuanyangpa3685

最新推荐文章于 2021-04-10 12:30:02 发布

阅读量213

点赞数

文章标签：数据库

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

已选择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;

已选择7行。

没有commit之前，scn没有显示

SQL> commit;

提交完成。

SQL> select a.*, ora_rowscn from dept2 a;

已选择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/

cuanyangpa3685

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
ora_rowscn伪列介绍和使用

下面一段英文是官方SQL Reference里的说明：For each row,ORA_ROWSCNreturns the conservative upper bound system change number (SCN) ...
复制链接

扫一扫