闪回版本查询

发布时间：2020-12-15 17:59:02 所属栏目：百科来源：网络整理

导读：目录 1闪回版本查询 2 ORA_ROWSCN伪列 ? ?2.1 ora_rowscn例子 ? ?2.2 创建占用多个数据块大表 ? ?2.3? rowdependencies参数? ?1 闪回版本查询 ?flashback query只查到某个时间点的数据，版本查询可以查看指定时间段内记录的不同版本(只包括被提交的记录)。

1闪回版本查询

2 ORA_ROWSCN伪列

? ?2.1 ora_rowscn例子

? ?2.2 创建占用多个数据块大表

? ?2.3? rowdependencies参数?

?1 闪回版本查询

?flashback query只查到某个时间点的数据，版本查询可以查看指定时间段内记录的不同版本(只包括被提交的记录)。

?相关伪列：

伪列名	说明
VERSIONS_STARTSCN	记录操作时的SCN或时间，为null表示在查询范围外创建
VERSIONS_STARTTIME	?
VERSIONS_ENDSCN	?
VERSIONS_ENDTIME	失效的SCN或时间，如果为空，同时VERSIONS_OPERATION也为空，表示记录在该时间段内无操作
VERSIONS_XID	?该操作的事务ID，用于闪回事务查询
VERSIONS_OPERATION	I表示insert,U表示UPDATE,D表示DELETE,对于索引键的update操作，会显示成delete和insert两个操作
ORA_ROWSCN?	?记录的是最后一次被修改时的SCN，注意是被提交的修改。如果没有提交，这个伪列不会发生变化

SQL> select timestamp_to_scn(sysdate) from dual;

TIMESTAMP_TO_SCN(SYSDATE)

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

?????????? 11357590586157

SQL> select * from flash_table;

??????? ID TABLE_NAME

---------- --------------------?

??????? 14 GJ_SQ_USE_LIST

??????? 15 LOGS_SEQ_ERR

??????? 16 LOG_GEN_CONV_TAB

??????? 17 LOGS_UNIT_ERROR

??????? 18 LOGS_REPAIR_ID

??????? 19 LOGS_CONV_PARALLEL

6 rows selected

SQL> update flash_table set id=id+100 where id<16;

2 rows updated

SQL> commit;

Commit complete

SQL> delete? flash_table where id=19;

1 row deleted

SQL> commit;

Commit complete

SQL> insert into flash_table values(21,'T');

1 row inserted

SQL> insert into flash_table values(22,'T1');

1 row inserted

SQL> commit;

Commit complete

SQL> select timestamp_to_scn(sysdate) from dual;

TIMESTAMP_TO_SCN(SYSDATE)

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

?????????? 11357590586339

SQL>select id,table_name,versions_startscn,versions_endscn,versions_operation,versions_xid from flash_table versions between scn 11357590586157 and 11357590586339;

id ?table_name ? ? ? ? ?versions_startscn ? ? versions_endscn ? versions_operation ?versions_xid

-------------------- --------------------- ----------------- ----------------- ? ------------------

19??? LOGS_CONV_PARALLEL?? 11357590586269 ? ? ? ? ? ? ?? ? ?? ?? ? ? ? ? ???D ? ???003300050004E6C0

115?? LOGS_SEQ_ERR???????? 11357590586211 ? ? ? ? ? ? ??? ? ??? ? ??? ? ? ??U????? 01CE000D0002024A

114?? GJ_SQ_USE_LIST?????? 11357590586211 ? ? ? ? ? ? ? ? ??? ? ?? ? ?? ? ??U????? 01CE000D0002024A

14??? GJ_SQ_USE_LIST ? ? ? ? ? ? ? ? ? ? ? ? ?11357590586211

15??? LOGS_SEQ_ERR ? ? ? ? ? ? ? ? ? ? ? ? ? ?11357590586211

16??? LOG_GEN_CONV_TAB ? ? ? ? ? ? ? ? ? ? --表示没有操作 versions_endscn和versions_operation都为null

17??? LOGS_UNIT_ERROR

18??? LOGS_REPAIR_ID?

19??? LOGS_CONV_PARALLEL ? ? ? ? ? ? ? ? ? ? ?11357590586269

22??? T1?????????????????? 11357590586294 ? ? ? ? ? ?? ? ???? ? ?? ? ?? ? ?? I????? 0080001400070537

21??? T??????????????????? 11357590586294 ? ? ? ? ? ? ?? ? ?? ? ?? ? ?? ? ?? I????? 0080001400070537

刚好和之前的操作对应，两条insert,两条update,一条delete。

获取更多的历史信息

SQL> Select versions_xid,

? 2???? DECODE(versions_operation,'I','Insert','U','Update','D','Delete','Original') "Operation",id from t versions between scn minvalue and maxvalue;

VERSIONS_XID???? VERSIONS_STARTSCN VERSIONS_ENDSCN Operation???????? ID

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

0B0015003BB90A00??? 13442272855949???????????????? Insert???????????? 2

?????????????????????????????????????????????????? Original?????????? 1

? 2 ORA_ROWSCN伪列

? ? For each row,?ORA_ROWSCN?returns the conservative upper bound system change number (SCN) of the most recent change to the row in the current session. 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. Refer to?CREATE?TABLE?...?NOROWDEPENDENCIES | ROWDEPENDENCIES?for 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 an?UPDATE?or?DELETE?statement.

ORA_ROWSCN?is not supported for Flashback Query. Instead,use the version query pseudocolumns,which are provided explicitly for Flashback Query. Refer to theSELECT?...?flashback_query_clause?for information on Flashback Query and?"Version Query Pseudocolumns"?for additional information on those pseudocolumns.

Restriction on ORA_ROWSCN:?This pseudocolumn is not supported for external tables.

? ORA_ROWSCN 是Oracle 10g 新增的，它记录的是最后一次被修改时的SCN，注意是被提交的修改。如果没有提交，这个伪列不会发生变化。，暂且把它看作是记录最后一次被修改时的SCN。 ?

? ? ? ?先来简单理解一下ORA_ROWSCN的实现原理。我们知道，每个Block在头部是记录了该block最近事务的SCN的，所以默认情况下，只需要从block头部直接获取这个值就可以了，不需要其他任何的开销。但是这是不精确的，一个block中会有很多行记录，每次事务不可能影响到整个block中所有的行，所以这是一个不精准的估算值，同一个block的所有记录ORA_ROWSCN都会是相同的，基本上没有多大的使用价值，

主要有以下几点：

1.默认情况，ora_rowscn记录的scn并不准确，记录的是block的scn。

2.创建表的时候ROWDEPENDENCIES可以来使ora_rowscn真正记录行一级的scn。

3.不能用在查询view时。

4.不能用于flashback query。

可以用来：

1.确认行所在块最后一次修改的scn

2.确认行所在块最后一次修改的大概时间

3.大部分的时候可以确定出两行记录创建的先后顺序(没在同一个块上)

4.如果CREATE TABLE ... ROWDEPENDENCIES 可以精确到行

2.1ora_rowscn例子

SQL> select * from t;

??????? ID NAME

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

???????? 1 philip

SQL> insert into t values(2,'lin');

1 row inserted

SQL> insert into t values(3,'jyu');

1 row inserted

SQL> commit;

Commit complete

SQL> col ORA_ROWSCN? for a30

SQL> select ORA_ROWSCN,id,name from t;

??????????????????? ORA_ROWSCN???????? ID NAME

------------------------------ ---------- -------?

??????????????? 13442272857645????????? 1 philip

??????????????? 13442272857645????????? 2 lin

??????????????? 13442272857645????????? 3 jyu

SQL> update t set name='lili' where id=2;

1 row updated

SQL> select ORA_ROWSCN,name from t;

??????????????????? ORA_ROWSCN???????? ID NAME

------------------------------ ---------- --------?

??????????????? 13442272857645????????? 1 philip

??????????????? 13442272857645????????? 2 lili

??????????????? 13442272857645????????? 3 jyu

?--不提交，ora_rowscn不会变.

SQL> commit;

Commit complete

SQL> select ORA_ROWSCN,name from t;

??????????????????? ORA_ROWSCN???????? ID NAME

------------------------------ ---------- ---------?

??????????????? 13442272857797????????? 1 philip

??????????????? 13442272857797????????? 2 lili

??????????????? 13442272857797????????? 3 jyu

--只update一条id=2的记录，但ID为1和3的ORA_ROWSCN也改变了。

2.2 创建占用多个数据块大表? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??

? SQL >? create? table? t2 (? id? number? , name? char ( 2000? ),des? char (? 2000 ));

Table? created

SQL >? insert? into? t2? values? ( 1? , 'a' ,? 'a1' );

1? row? inserted

SQL >? insert? into? t2? values? ( 1? , 'b' ,? 'b1' );

1? row? inserted

SQL >? insert? into? t2? values? ( 1? , 'c' ,? 'c1' );

1? row? inserted

SQL >? insert? into? t2? values? ( 4? , 'd' ,? 'cd' );

1? row? inserted

SQL >? commit? ;

Commit? complete

SQL >?? select? ora_rowscn, id? from? t2;

??????????????????? ORA_ROWSCN????????? ID

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

???????????????? 13442272858097 ?????????? 1

???????????????? 13442272858097 ?????????? 4

? --查看ORA_ROWSCN的值都相同?

SQL> update t2 set name='aa' where id=4;

1 row updated

SQL>? select ora_rowscn,id from t2;

??????????????????? ORA_ROWSCN???????? ID

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

??????????????? 13442272858097????????? 1

??????????????? 13442272858097????????? 4

SQL> commit;

Commit complete

SQL>? select ora_rowscn,id from t2;

??????????????????? ORA_ROWSCN???????? ID

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

??????????????? 13442272858097????????? 1

??????????????? 13442272858125????????? 4

提交后，更新的行的ORA_ROWSCN的值发生改变，而其他的不变。???????????????????????????????????????????????????????????????

SQL> update t2 set name='bb' where id=1 and rownum=1;? ---再次更新一行

1 row updated?SQL> select ora_rowscn,id from t2;

??????????????????? ORA_ROWSCN???????? ID

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

??????????????? 13442272858097????????? 1

??????????????? 13442272858125????????? 4

SQL> commit;

Commit complete

SQL> select ora_rowscn,id from t2;

??????????????????? ORA_ROWSCN???????? ID

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

??????????????? 13442272858163????????? 1 ?--发生变化

??????????????? 13442272858097????????? 1

??????????????? 13442272858125????????? 4

SQL>? analyze table t2 compute statistics;

Table analyzed

SQL> select blocks from user_tables where table_name='T2';

??? BLOCKS

----------

???????? 4

2.3?rowdependencies参数?

SQL> create table t3 (id number,name varchar2(10)) rowdependencies;

Table created

? SQL> insert into t3 values (1,'lin');

1 row inserted

SQL> commit;

Commit complete

SQL> insert into t3 values (2,'lili');

1 row inserted

SQL> commit;

Commit complete

SQL> insert into t3 values (3,'yuyu');

1 row inserted

SQL> commit;

Commit complete

SQL> select ora_rowscn,id from t3;

??????????????????? ORA_ROWSCN???????? ID

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

??????????????? 13442272859095????????? 1

??????????????? 13442272859098????????? 2

??????????????? 13442272859101????????? 3

SQL> select scn_to_timestamp(ora_rowscn ),ora_rowscn,id from t3;

SCN_TO_TIMESTAMP(ORA_ROWSCN) ? ? ? ? ORA_ROWSCN ? ? ? ? ? ? ?ID ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??

--------------------------------- ----------------------- ---------?

12-8月 -13 04.41.53.000000000 下午 ? ? 13442272859126????????? 4 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?

12-8月 -13 04.40.50.000000000 下午 ? ? 13442272859095????????? 1 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?

12-8月 -13 04.40.56.000000000 下午 ? ? 13442272859098????????? 2 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?

12-8月 -13 04.41.02.000000000 下午 ? ? 13442272859101????????? 3 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?

----每一行的ORA_ROWSCN?的值都不一样。???????????????????????????????????????????????????

SQL>? analyze table t3 compute statistics;

Table analyzed

SQL> select blocks from user_tables where table_name='T3';

??? BLOCKS

----------

? ? ? ? ?1? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!