闪回归档操作

1.创建闪回归档表空间

11:17:46 sys@ORA11G> create tablespace mytest datafile size 100m extent management local uniform size 1m segment space management auto;
Tablespace created.

2.创建闪回归档

11:19:21 sys@ORA11G> create flashback archive flashback1 tablespace mytest retention 4year;
Flashback archive created.

3.删除闪回归档

11:21:19 sys@ORA11G> drop flashback archive flashback1;
Flashback archive dropped.

 

4.创建闪回归档必须的权限

11:24:28 sys@ORA11G> select * from dba_sys_privs where privilege like '%FLASH%';
GRANTEE                  PRIVILEGE                    ADM
------------------------------ ---------------------------------------- ---
SYS                      FLASHBACK ANY TABLE               NO
DBA                      FLASHBACK ANY TABLE               YES
SYS                      FLASHBACK ARCHIVE ADMINISTER          NO
DBA                      FLASHBACK ARCHIVE ADMINISTER          YES
MDSYS                      FLASHBACK ANY TABLE               NO

 

事例

1.授权test

11:26:06 sys@ORA11G> grant  FLASHBACK ARCHIVE ADMINISTER to test;
Grant succeeded.

2.用test 帐户创建闪回归档

11:27:13 test@ORA11G> create flashback archive flashback1 tablespace mytest quota 100m retention 4 year;
Flashback archive created.

 

3.创建用户表并插入数据

11:31:39 test@ORA11G> select * from test;
     ID NAME
---------- ----------
     1 test1
     2 test2
11:31:42 test@ORA11G>

4.用sys用户查询现有flashbak归档

11:35:39 sys@ORA11G> SELECT FLASHBACK_ARCHIVE_NAME, status FROM DBA_FLASHBACK_ARCHIVE;

FLASHBACK_ARCHIVE_NAME
------------------------------------------------------------------------------------------------------------------------
STATUS
-------

FLASHBACK1 

指定默认的flashbakarchive

11:49:19 test@ORA11G> conn / as sysdba
Connected.
11:51:11 sys@ORA11G> alter flashback archive flashback1 set default;

 

为表指定flashbackarhive

11:51:20 test@ORA11G> alter table test flashback archive;
Table altered.