代码改变世界

11G新特性 -- flashback data archive(2)

  abce  阅读(357)  评论(0编辑  收藏  举报

创建Flashback Data Archive
用户需要授予dba或flashback archive administer系统特权。flashback archive administer系统特权包含:create flashback archive,alter flashback archive,drop flashback archive权利。

复制代码
SQL> select * from dba_sys_privs where privilege like '%FLASH%';

GRANTEE                        PRIVILEGE                                ADM COM
------------------------------ ---------------------------------------- --- ---
SYS                            FLASHBACK ANY TABLE                      NO  NO
DBA                            FLASHBACK ANY TABLE                      NO  NO
SYS                            FLASHBACK ARCHIVE ADMINISTER             NO  NO
DBA                            FLASHBACK ARCHIVE ADMINISTER             NO  NO
MDSYS                          FLASHBACK ANY TABLE                      NO  NO

SQL> 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 retention 4 year;
复制代码

创建Flashback Data Archive:

SQL> create tablespace tbs1 datafile '/u01/app/oracle/oradata/dbt/tbs101.dbf' size 2G;
SQL> create flashback archive default fla1 tablespace tbs1 quota 1g retention 1 month;

修改Flashback Data Archive:

复制代码
#设置默认的flashback data archive
SQL> alter flashback archive fla1 set default; 
#flashback data archive 添加表空间 
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 retention 2 year;
#移除一个表空间
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 scn 123456;
复制代码

删除Flashback Data Archive:

SQL> drop flashback archive fla1;

开启、取消flashback data archive:
缺省情况下没有对表开启Flashback Data Archive

复制代码
SQL> create table test1( name varchar2(30),address varchar2(30)) flashback archive flash1;
SQL> select * from dba_flashback_archive_tables;

TABLE_NAME                OWNER_NAME                FLASHBACK_ARCHIVE_NAME         ARCHIVE_TABLE_NAME                                    STATUS
------------------------- ------------------------- ------------------------------ ----------------------------------------------------- -------------
TEST1                     HR                        FLASH1                         SYS_FBA_HIST_106929                                   ENABLED

SQL> create table test2( name varchar2(30),address varchar2(30)) ;
SQL> alter table test2 flashback archive;
SQL> select * from dba_flashback_archive_tables;

TABLE_NAME                OWNER_NAME                FLASHBACK_ARCHIVE_NAME         ARCHIVE_TABLE_NAME                                    STATUS
------------------------- ------------------------- ------------------------------ ----------------------------------------------------- -------------
TEST1                     HR                        FLASH1                         SYS_FBA_HIST_106929                                   ENABLED
TEST2                     HR                        FLASH1                         SYS_FBA_HIST_106930                                   ENABLED

SQL> alter table test2 no flashback archive;
SQL> drop flashback archive flash1;
复制代码

Flashback Data Archive开启对DDL的支持:
只支持以下DDL语句:
-alter table
-truncate table
-rename table
如果使用了不受支持的DDL语句,可以先用dbms_flashback_archive.disassociate_fba过程使得表和flashback data archive分离,DDL操作完成后使用dbms_flashback_archive.reassociate_fba重新关联

 

示例:

复制代码
SQL> create table test01(id int,name varchar2(20),job varchar2(20)) flashback archive fla1;

Table created.

SQL> insert into test01 values(1,'hello','tec');

1 row created.

SQL> commit;

Commit complete.

SQL> select to_char(systimestamp, 'YYYY-MM-DD HH24:MI:SS') FROM dual;

TO_CHAR(SYSTIMESTAM
-------------------
2015-11-03 10:03:04

SQL> update test01 set job='dev' where id=1;

1 row updated.

SQL> commit;

Commit complete.

SQL> select to_char(systimestamp, 'YYYY-MM-DD HH24:MI:SS') FROM dual;

TO_CHAR(SYSTIMESTAM
-------------------
2015-11-03 10:06:04

SQL>  update test01 set job='dba' where id=1;

1 row updated.

SQL> commit;

Commit complete.

SQL> select to_char(systimestamp, 'YYYY-MM-DD HH24:MI:SS') FROM dual;

TO_CHAR(SYSTIMESTAM
-------------------
2015-11-03 10:09:02

SQL> select * from test01;

        ID NAME                 JOB
---------- -------------------- --------------------
         1 hello                dba

SQL> SELECT * FROM test01 AS OF TIMESTAMP TO_TIMESTAMP ('2015-11-03 10:06:04', 'YYYY-MM-DD HH24:MI:SS');

        ID NAME                 JOB
---------- -------------------- --------------------
         1 hello                dev

SQL> 
复制代码

 

编辑推荐:
· AI与.NET技术实操系列(二):开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识(上)
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析
阅读排行:
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布:重大改进与新特性概览!
· AI与.NET技术实操系列(二):开始使用ML.NET
· .NET10 - 预览版1新功能体验(一)
点击右上角即可分享
微信分享提示