当前位置：首页 -> 基础 -> 数据库编程

oracle 11g Flashback Data Archive(闪回数据归档)(二)

2014-11-24 12:32:59 · 作者: · 浏览: 1
标签: oracle 11g Flashback Data Archive 数据 归档
Performs partition or subpartition operations
Converts a LONG column to a LOB column
Includes an UPGRADE TABLE clause, with or without an INCLUDING DATA clause
2、 DROP TABLE
3、TRUNCATE TABLE (11GR2是可以的)
4、RENAME TABLE (11GR2也是可以的) 
gyj@MYDB> select * from v$version;


BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production


gyj@MYDB> alter table t1 drop column name;


Table altered.


gyj@MYDB> alter table t1 add(name varchar2(100));


Table altered.


gyj@MYDB> alter table t1 rename to t10;


Table altered.


gyj@MYDB> truncate table t10;


Table truncated.




gyj@MYDB> drop table t10;
drop table t10
           *
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table




gyj@MYDB> alter table t10 no flashback archive;


Table altered.


gyj@MYDB> drop table t10;


Table dropped.

五、监控认回数据归档
1、查哪些表已经启用了闪回数据归档
gyj@MYDB> select * from dba_flashback_archive_tables;
TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME STATUS
----------- ----------- ---------------------- ---------------- -------------
T1 GYJ FLASH1 SYS_FBA_HIST_17877 ENABLED
2、查数据库中所有的闪回数据归档
gyj@MYDB> select flashback_archive_name,retention_in_days from dba_flashback_archive;
FLASHBACK_ARCHIVE_NAME RETENTION_IN_DAYS
-------------------------------------------------- -----------------
FLASH1 1095
3、查有关闪回数据归档所使用的表空间的信息 
gyj@MYDB>  select flashback_archive_name,tablespace_name,quota_in_mb from dba_flashback_archive_ts;


FLASHBACK_ARCHIVE_NAME        TABLESPACE_NAME                QUOTA_IN_MB
---------------------------- ----------------------- -----------------
FLASH1                          FLASH_TBS1                     2048

六、使用闪回数据归档：例子 
gyj@MYDB> create table test_gyj (id int,name varchar2(10));


Table created.


gyj@MYDB> alter table test_gyj flashback archive flash1;


Table altered.


gyj@MYDB> begin
  2   for i in 1 .. 100 loop
  3    insert into test_gyj values(i,'gyj'||i);
  4    commit;
  5   end loop;
  6   end;
  7   /


PL/SQL procedure successfully completed.




gyj@MYDB> select count(*) from test_gyj;


  COUNT(*)
----------
       100




gyj@MYDB> col FLASHBACK_ARCHIVE_NAME for a10
gyj@MYDB> col TABLE_NAME for a10
gyj@MYDB> col ARCHIVE_TABLE_NAME for a20
gyj@MYDB> col OWNER_NAME for a5
gyj@MYDB> select * from dba_flashback_archive_tables;


TABLE_NAME OWNER FLASHBACK_ ARCHIVE_TABLE_NAME   STATUS
---------- ----- ---------- -------------------- -------------
T1         GYJ   FLASH1     SYS_FBA_HIST_17890   ENABLED
T10        GYJ   FLASH1     SYS_FBA_HIST_17898   ENABLED
TEST_GYJ   GYJ   FLASH1     SYS_FBA_HIST_17908   ENABLED


gyj@MYDB> select count(*) from SYS_FBA_HIST_17908;


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


gyj@MYDB> select current_scn from v$database;


CURRENT_SCN
-----------
    2353743


gyj@MYDB> delete from test_gyj;


100 rows deleted.


gyj@MYDB> commit;


Commit complete.


gyj@MYDB> select current_scn from v$database;


CURRENT_SCN
-----------
    2353790




gyj@MYDB>  select count(*) from test_gyj as of sc