[bbk5110] 第41集 - 第四章 Flashback Database 05
闪回数据版本查询->闪回数据归档查询，前者不可以跨越DDL操作，后者可以跨越.
SQL> l 1 select versions_xid,to_char(versions_starttime,'yyyy-mm-dd hh24:mi:ss') versions_starttime,versions_endtime,empno,sal 2 from emp 3 versions between timestamp to_timestamp('2013-05-18 14:13:07','yyyy-mm-dd hh24:mi:ss') and maxvalue 4 where empno < 10 5* SQL> / VERSIONS_XID VERSIONS_STARTTIME VERSIONS_ENDTIME EMPNO SAL ---------------- ---------------------------------------- ---------------------------------------- ---------- ---------- 09000100EF080000 2013-05-18 14:13:50 1 1000 03001B0009090000 2013-05-18 14:13:59 2 1000 SQL> insert into emp(empno,ename,sal,deptno) values(3,'zhangsan3',2000,30); 1 row created. SQL> commit; Commit complete. select versions_xid,to_char(versions_starttime,'yyyy-mm-dd hh24:mi:ss') versions_starttime,versions_endtime,empno,sal from emp versions between timestamp to_timestamp('2013-05-18 14:13:07','yyyy-mm-dd hh24:mi:ss') and maxvalue 4 where empno < 10; VERSIONS_XID VERSIONS_STARTTIME VERSIONS_ENDTIME EMPNO SAL ---------------- ---------------------------------------- ---------------------------------------- ---------- ---------- 030000001E090000 2013-05-19 09:22:30 3 2000 09000100EF080000 2013-05-18 14:13:50 1 1000 03001B0009090000 2013-05-18 14:13:59
/**************************************************************************/
实验目的:在11gr2版本下，进行DDL操作(删除字段)之后，在闪回数据区中也会相应的自动改变.
实验环境:11gr2
/*************************************************************************/
SQL> create table t 2 as 3 select * from all_objects; Table created. SQL> alter table t flashback archive fbda_area_netstore; Table altered. SQL> set linesize 100; SQL> desc t; Name Null? Type ----------------------------------------------------- -------- ------------------------------------ OWNER NOT NULL VARCHAR2(30) OBJECT_NAME NOT NULL VARCHAR2(30) SUBOBJECT_NAME VARCHAR2(30) OBJECT_ID NOT NULL NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(19) CREATED NOT NULL DATE LAST_DDL_TIME NOT NULL DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) NAMESPACE NOT NULL NUMBER EDITION_NAME VARCHAR2(30)
SQL> ALTER TABLE T DROP COLUMN STATUS; Table altered. SQL> desc t; Name Null? Type ----------------------------------------------------- -------- ------------------------------------ OWNER NOT NULL VARCHAR2(30) OBJECT_NAME NOT NULL VARCHAR2(30) SUBOBJECT_NAME VARCHAR2(30) OBJECT_ID NOT NULL NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(19) CREATED NOT NULL DATE LAST_DDL_TIME NOT NULL DATE TIMESTAMP VARCHAR2(19) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) NAMESPACE NOT NULL NUMBER EDITION_NAME VARCHAR2(30)
Transaction Schema Evolution
- DDL support for:
- Add,drop,rename,and modify column
- Drop and truncate partition
- Rename and truncate table
- Flashback queries work across DDL changes.
- All other DDL is not automatically supported(seen nex slide).
Full Schema Evolution
Disassociate or associate procedures in the DBMS_FLASHBACK_ARCHIVE package:
- Disable Total Recall on specified tables and allow more complex DDL(upgrades,split tables,and so on).
- Enforce schema intergrity during assocation(Base talbe and history table must be the smae schema.)
Note:This function should be used with care and with the understanding that the archive can no longer be guaranteed to be immutable because the history could have been altered during the time of disassociation.
