**
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;