[bbk5115] 第42集 - 第四章 Flashback Database 06
查看包的结构内容
[oracle@arcerzhang ~]$ sqlplus /nolog SQL*Plus: Release 11.2.0.1.0 Production on Sun May 19 20:15:32 2013 Copyright (c) 1982, 2009, Oracle. All rights reserved. SQL> conn /as sysdba Connected. SQL> desc dbms_flashback_archive; PROCEDURE DISASSOCIATE_FBA Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- OWNER_NAME VARCHAR2 IN TABLE_NAME VARCHAR2 IN PROCEDURE REASSOCIATE_FBA Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- OWNER_NAME VARCHAR2 IN TABLE_NAME VARCHAR2 IN
[oracle@arcerzhang ~]$ sqlplus ARCER/ARCER SQL*Plus: Release 11.2.0.1.0 Production on Sun May 19 20:18:27 2013 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> SELECT * FROM TAB; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- BIN$pfwr1Jtji5zgQPrc4kAI7Q==$0 TABLE COILS_ASSETS_DEPRECIATION TABLE COILS_CODE_ASSESS TABLE COILS_COMPANY_LICENSE TABLE COILS_EMP TABLE COILS_LOG_ASSESS TABLE COILS_STORES TABLE COMPANY_ANNOUNCEMENT TABLE CRM_CUSTOMER TABLE EMP TABLE FGPS_EMPLOYEE TABLE TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- FGPS_FINANCEUNIT TABLE FGPS_GROUP TABLE FGPS_GROUP_VS_MENU TABLE FGPS_MENU TABLE FGPS_MYRECEIVER TABLE FGPS_PEOPLE TABLE FGPS_SYSTEMUSER TABLE FGPS_USER TABLE FGPS_USERGRANTACCOUNT TABLE FGPS_VOUCHER TABLE FRX_DEV_SUGGEST TABLE TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- FRX_OA_ATTENDANCE TABLE FRX_OA_DICT TABLE FRX_OA_LOG_TEMPLATE TABLE FRX_OA_WORKLOG TABLE NETSTORE_INCOMEEXPENDITURELIST TABLE RECHARGE_SUM_VW VIEW SYSTEM_GROUP TABLE SYSTEM_MENU TABLE SYS_CATEGORY TABLE SYS_FBA_DDL_COLMAP_80489 TABLE SYS_FBA_DDL_COLMAP_80587 TABLE TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- SYS_FBA_HIST_80489 TABLE SYS_FBA_HIST_80587 TABLE SYS_FBA_TCRV_80489 TABLE SYS_FBA_TCRV_80587 TABLE SYS_GROUP_MENU_CORRESPONDENCE TABLE T TABLE WAITTINGHANDLER TABLE WAITTING_HANDLER_DETAIL TABLE 41 rows selected. SQL> DESC SYS_FBA_HIST_80587; Name Null? Type ----------------------------------------- -------- ---------------------------- RID VARCHAR2(4000) STARTSCN NUMBER ENDSCN NUMBER XID RAW(8) OPERATION VARCHAR2(1) OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(30) SUBOBJECT_NAME VARCHAR2(30) OBJECT_ID NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(19) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(19) D_4497907_STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) NAMESPACE NUMBER EDITION_NAME VARCHAR2(30) 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) SQL> drop table SYS_FBA_HIST_80587; drop table SYS_FBA_HIST_80587 * ERROR at line 1: ORA-55622: DML, ALTER and CREATE UNIQUE INDEX operations are not allowed on table "ARCER"."SYS_FBA_HIST_80587" SQL> drop table t; drop table t * ERROR at line 1: ORA-55610: Invalid DDL statement on history-tracked table
上述代码阐述:表T是加入了闪回归档区的表，在当前SCHEMA下，会产生历史表等相关记录信息.使用常规手段，直接删除表T本身，或者删除其历史表都是无法正常完成的,因为二者是存在着关联关系的.
SQL> create table t2 2 as 3 select * from SYS_FBA_HIST_80587; Table created.
开始真正的实验
SQL> execute dbms_flashback_archive.DISASSOCIATE_FBA('ARCER','T'); PL/SQL procedure successfully completed.
SQL> drop table SYS_FBA_HIST_80587; Table dropped.
SQL> execute dbms_flashback_archive.REASSOCIATE_FBA('ARCER','T'); BEGIN dbms_flashback_archive.REASSOCIATE_FBA('ARCER','T'); END; * ERROR at line 1: ORA-00600: internal error code, arguments: [ktfa_get_hist_objn], [], [], [], [], [], [], [], [], [], [], [] ORA-06512: at "SYS.DBMS_FLASHBACK_ARCHIVE", line 17 ORA-06512: at line 1
SQL> rename t2 to SYS_FBA_HIST_80587; Table renamed.
SQL> execute dbms_flashback_archive.REASSOCIATE_FBA('ARCER','T'); PL/SQL procedure successfully completed.
SQL> drop table t; drop table t * ERROR at line 1: ORA-55610: Invalid DDL statement on history-tracked table SQL> drop table SYS_FBA_HIST_80587; drop table SYS_FBA_HIST_80587 * ERROR at line 1: ORA-55622: DML, ALTER and CREATE UNIQUE INDEX operations are not allowed on table "ARCER"."SYS_FBA_HIST_80587"
Restrictions
- You cannot enable Total Recall for base tables with Hybrid Columnar compression.
- If disassociate is used,immutablity of history is no longer guaranteed(but you could always purage history previously anway with the right privilege).
- There is no transportability of history tables.
Guidelines
- Use SCN for precise queries or
- Use Flashback technology for your convenience.
- Flashback uses current system settings.
- Ensure database consistency with a COMMIT or ROLLBACK operation before querying past data.
- You cannot retrieve past data from a dynamic performance(V$)view,They contain current data.
- However,you can perform queries on past data in static data dictionary views,such as *_TABLES.
Quiz
- You cannot drop,but you can truncate,a table that is tracked by Oracle Total Recall.
True
Flase
- Select all correct statements about Oracle Total Recall:
Oracle Total Recall is enabled by default.
A Flashback Data Archive provides the ability to track and store all transactional changes to a "tracked" table over its lifetime.
Dropping a column in a table enabled for Flashback Data Archive causes an error.
Flashback processing always uses the settings that were in effect at the time of being queried.
Flashback uses the current session settings,such as national language and character set.
