11G new Features--Flashback Data Archive (2)

Create Flashback Data Archive
The user needs to grant DBA or Flashback archive administer system privileges. Flashback archive administer system privileges include: Create Flashback Archive,alter Flashback Archive,drop Flashback Archive rights.

Sql>Select* fromDba_sys_privswherePrivilege like'%flash%'; GRANTEE PRIVILEGE ADM COM------------------------------ ---------------------------------------- --- ---SYS FLASHBACK any TABLE NO nodba FLASHBACK                            Any TABLE NO nosys FLASHBACK ARCHIVE administer no NODBA                       FLASHBACK ARCHIVE administer NO nomdsys FLASHBACK any TABLE NO NOSQL>Grant Flashback archive administer to HR; SQL> Create tablespace Flash_tbs datafile'/u01/app/oracle/oradata/yb/flash01.dbf'size 300m autoextend on next 30m maxsize 5g; SQL> Create Flashback archive flash1 tablespace Flash_tbs Retention4Year

Create Flashback Data Archive:

' /u01/app/oracle/oradata/dbt/tbs101.dbf ' size 2G; SQLdefault1 month;

Modify Flashback Data Archive:

#设置默认的flashback Data Archivesql> Alter FLASHBACK archive FLA1Set default; #flashback Data Archive add tablespace SQL>Alter FLASHBACK archive FLA1 add tablespace tbs2 quota1g; SQL>Alter FLASHBACK archive FLA1 add tablespace tbs3; #修改配额SQL>Alter FLASHBACK archive FLA1 Modify tablespace tbs3 quota 20G; #修改期限SQL> Alter FLASHBACK archive FLA1 Modify Retention2Year ; #移除一个表空间SQL>Alter FLASHBACK archive FLA1 remove tablespace tbs2; #purge历史数据SQL>alter FLASHBACK archive FLA1 purge all; SQL> Alter FLASHBACK archive FLA1 purge before timestamp (systimestamp-inteval'1'Day ); SQL> Alter FLASHBACK archive FLA1 purge before SCN123456;

Delete Flashback Data Archive:

Sql> Drop Flashback Archive fla1;

To open and cancel Flashback data archive:
Flashback Data Archive is not enabled by default on the table

Sql> CREATE table Test1 (name VARCHAR2 ( -), Address varchar2 ( -) ) Flashback archive Flash1; SQL>Select* fromDba_flashback_archive_tables;                                    TABLE_NAME Owner_name Flashback_archive_name Archive_table_name STATUS------------------------- ------------------------- ------------------------------ ------------------------------- ---------------------- -------------TEST1 HR FLASH1 sys_fba_hist_106929 Enabledsql> CREATE table test2 (name VARCHAR2 ( -), Address varchar2 ( -)) ; SQL>ALTER TABLE test2 Flashback archive; SQL>Select* fromDba_flashback_archive_tables;                                    TABLE_NAME Owner_name Flashback_archive_name Archive_table_name STATUS------------------------- ------------------------- ------------------------------ ------------------------------- ---------------------- -------------TEST1 HR FLASH1 sys_fba_hist_106929 ENABLEDTEST2 HR FLASH1 Sys_fba_hist_ 106930 Enabledsql>ALTER TABLE Test2 no flashback archive; SQL> Drop Flashback Archive flash1;

Flashback Data Archive opens support for DDL:
Only the following DDL statements are supported:
-alter table
-truncate table
-rename table
If you are using an unsupported DDL statement, you can use the DBMS_FLASHBACK_ARCHIVE.DISASSOCIATE_FBA procedure to detach the table from the Flashback data archive, using the DBMS_ after the DDL operation is complete. FLASHBACK_ARCHIVE.REASSOCIATE_FBA Re-association

Example:

Sql> CREATE TABLE test01 (IDint, Name VARCHAR2 ( -), Job varchar2 ( -) ) Flashback archive fla1; Table created. SQL> INSERT into test01 values (1,'Hello','Tec');1row created. SQL>commit; Commit complete. SQL>SelectTo_char (Systimestamp,'YYYY-MM-DD HH24:MI:SS') from dual; To_char (Systimestam------------------- -- One-Geneva Ten:Geneva:GenevaSQL> Update test01Setjob='Dev' whereId=1;1row updated. SQL>commit; Commit complete. SQL>SelectTo_char (Systimestamp,'YYYY-MM-DD HH24:MI:SS') from dual; To_char (Systimestam------------------- -- One-Geneva Ten: .:GenevaSQL> Update test01Setjob='DBA' whereId=1;1row updated. SQL>commit; Commit complete. SQL>SelectTo_char (Systimestamp,'YYYY-MM-DD HH24:MI:SS') from dual; To_char (Systimestam------------------- -- One-Geneva Ten: the: GenevaSQL>Select* fromtest01; ID NAME JOB---------- -------------------- --------------------1Hello Dbasql> SELECT * from test01 as of TIMESTAMP To_timestamp ('2015-11-03 10:06:04','YYYY-MM-DD HH24:MI:SS'); ID NAME JOB---------- -------------------- --------------------1Hello Devsql>

11G new Features--Flashback Data Archive (2)