ORACLE 多版本控制与读一致性的理解

**

ORACLE 多版本控制与读一致性的理解

一、通过闪回删除数据理解多版本控制

**
总体实验步骤:
1.创建测试表
create table t as select username,created from dba_users ;

2.打开游标
set autoprint off
variable x refcursor;
begin
open :x for select * from t;
end;
/

3.创建自动事务删除数据
declare
pragma autonomous_transaction;
begin
delete from t;
commit;
end;
/

print x

注:打开游标时，Oracle不会复制任何数据，只有你获取数据时才从表中读取数据
删除前已将数据放入了undo段中。

实验一:
SQL> create table t as select username,created from dba_users ;

Table created.

SQL> select * from t;

USERNAME                       CREATED
------------------------------ ------------
SYS                                24-AUG-13
SYSTEM                         24-AUG-13
SCOTT                          24-AUG-13
OGG                            26-FEB-19
GD                             23-MAR-19
OUTLN                          24-AUG-13
MGMT_VIEW                      24-AUG-13
FLOWS_FILES                    24-AUG-13
MDSYS                          24-AUG-13
ORDSYS                         24-AUG-13
EXFSYS                         24-AUG-13

USERNAME                       CREATED
------------------------------ ------------
DBSNMP                         24-AUG-13
WMSYS                          24-AUG-13
APPQOSSYS                      24-AUG-13
APEX_030200                    24-AUG-13
OWBSYS_AUDIT                   24-AUG-13
ORDDATA                        24-AUG-13
CTXSYS                         24-AUG-13
ANONYMOUS                      24-AUG-13
SYSMAN                         24-AUG-13
XDB                            24-AUG-13
ORDPLUGINS                     24-AUG-13

USERNAME                       CREATED
------------------------------ ------------
OWBSYS                         24-AUG-13
SI_INFORMTN_SCHEMA             24-AUG-13
OLAPSYS                        24-AUG-13
ORACLE_OCM                     24-AUG-13
XS$NULL                        24-AUG-13
BI                             25-FEB-19
PM                             25-FEB-19
MDDATA                         24-AUG-13
IX                             25-FEB-19
SH                             25-FEB-19
DIP                            24-AUG-13

USERNAME                       CREATED
------------------------------ ------------
OE                             25-FEB-19
APEX_PUBLIC_USER               24-AUG-13
HR                             25-FEB-19
SPATIAL_CSW_ADMIN_USR          24-AUG-13
SPATIAL_WFS_ADMIN_USR          24-AUG-13

38 rows selected.

SQL>
SQL>
SQL> set autoprint off
SQL> variable x refcursor;
SQL> begin
2 open :x for select * from t;
3 end;
4 /

PL/SQL procedure successfully completed.

SQL>
SQL> declare
2 pragma autonomous_transaction;
3 begin
4 delete from t;
5 commit;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL>
SQL> select * from t;

no rows selected

SQL> print x

二、通过scn闪回理解读一致性

SCN(System Change Number),Oracle的内部时钟，每次发生提交时，这个时钟开始递增。
获取scn
总体实验步骤:
variable scn number
exec :scn :=dbms_flashback.get_system_change_number;

print scn

接着以后我们可以通过这个scn获取到这个时间点的内容
select count(*) from t;

delete from t;
select count() from t;
commit;
select count(),:scn then_scn,dbms_flashback.get_system_change_number now_scn from t as of scn :scn;

闪回表：
打开该表的行移动:
alter table t enable row movement;

flashback table t to scn :scn;

select cnt_now,cnt_then,:scn then_scn,dbms_flashback.get_system_change_number now_scn from (select count() cnt_now from t),(select count() cnt_then from t as of scn :scn)
/

实验二:
SQL> create table t as select username,created from dba_users ;

Table created.

SQL>
SQL> variable scn number
SQL> exec :scn :=dbms_flashback.get_system_change_number;

print scn
PL/SQL procedure successfully completed.

SQL> SQL>

       SCN
----------
   1310606

SQL>
SQL>
SQL> set autoprint off
SQL> variable x refcursor;
SQL> begin
2 open :x for select * from t;
3 end;
4 /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> declare
2 pragma autonomous_transaction;
3 begin
4 delete from t;
5 commit;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> select count(*) from t;

  COUNT(*)
----------
         0

SQL>
SQL> select count(*),:scn then_scn,dbms_flashback.get_system_change_number now_scn from t as of scn :scn;

  COUNT(*)   THEN_SCN    NOW_SCN
---------- ---------- ----------
        38    1310606    1310632

SQL> alter table t enable row movement;

Table altered.

SQL>
SQL> flashback table t to scn :scn;

Flashback complete.

SQL>
SQL> select cnt_now,cnt_then,:scn then_scn,dbms_flashback.get_system_change_number now_scn from (select count() cnt_now from t),(select count() cnt_then from t as of scn :scn)
2 /

SQL> select count(*) from t;

COUNT(*)
   ----------
           38

获取scn
select current_scn from v$database;
或者
SELECT dbms_flashback.get_system_change_number FROM dual;