CREATE FLASHBACK ARCHIVE [DEFAULT] flashback_archive
TABLESPACE tablespace tablespace_name
[QUOTA integer {K| M| G| T| P| E}]
RETENTION integer {YEAR | MONTH | DAY};
注:default与非default的flashback archive在使用上是有区别的,如下:
非default falshback archive:create table test(x int) flashback archive flashback_archive;
default flashback archive:create table test(x int) flashback archive;
DROP FLASHBACK ARCHIVE flashback_archive;
ALTER FLASHBACK ARCHIVE flashback_archive
SYS@ORA11GR2>select * from v$version where rownum=1;
BANNER
--------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
SYS@ORA11GR2>
SYS@ORA11GR2>create tablespace ts_users datafile '/u01/app/oracle/oradata/ORA11GR2/ts_users.dbf' size 50m;
Tablespace created.
SYS@ORA11GR2>create user xxf identified by xxf default tablespace ts_users;
User created.
SYS@ORA11GR2>grant connect,resource to xxf;
Grant succeeded.
SYS@ORA11GR2>
SYS@ORA11GR2>create flashback archive fbad_ts_users tablespace ts_users retention 1 day;
Flashback archive created.
SYS@ORA11GR2>
SYS@ORA11GR2>conn xxf/xxf
Connected.
XXF@ORA11GR2>create table t1(x int) flashback archive fbad_ts_users;
create table t1(x int) flashback archive fbad_ts_users
*
ERROR at line 1:
ORA-55620: No privilege to use Flashback Archive
XXF@ORA11GR2>conn / as sysdba
Connected.
SYS@ORA11GR2>grant flashback archive on fbad_ts_users to xxf;
Grant succeeded.
SYS@ORA11GR2>conn xxf/xxf
Connected.
XXF@ORA11GR2>create table t1(x int) flashback archive fbad_ts_users;
Table created.
XXF@ORA11GR2>
4:已存在的表启用闪回归档数据
XXF@ORA11GR2>create table t2(x int);
Table created.
XXF@ORA11GR2>alter table t2 flashback archive fbad_ts_users;
Table altered.
XXF@ORA11GR2>
5:查看已启用闪回归档数据的表(其中:ARCHIVE_TABLE_NAME为记录闪回数据的表,我们无法查看它)
XXF@ORA11GR2>conn / as sysdba
Connected.
SYS@ORA11GR2>select * from dba_flashback_archive_tables;
TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME STATUS
---------- ---------- ---------------------- ------------------ -------
T1 XXF FBAD_TS_USERS SYS_FBA_HIST_75350 ENABLED
T2 XXF FBAD_TS_USERS SYS_FBA_HIST_75351 ENABLED
SYS@ORA11GR2>
SYS@ORA11GR2>conn xxf/xxf
Connected.
XXF@ORA11GR2>alter table t1 no flashback archive;
alter table t1 no flashback archive
*
ERROR at line 1:
ORA-55620: No privilege to use Flashback Archive
XXF@ORA11GR2>conn / as sysdba
Connected.
SYS@ORA11GR2>alter table xxf.t1 no flashback archive;
Table altered.
SYS@ORA11GR2>select * from dba_flashback_archive_tables;
TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME STATUS
---------- ---------- ---------------------- ------------------ --------
T2 XXF FBAD_TS_USERS SYS_FBA_HIST_75351 ENABLED
SYS@ORA11GR2>
SYS@ORA11GR2>show parameter undo_retention;
NAME TYPE VALUE
------------------------------------ ----------- -----------------------
undo_retention integer 900
SYS@ORA11GR2>
SYS@ORA11GR2>conn xxf/xxf
Connected.
XXF@ORA11GR2>set time on
21:19:02 XXF@ORA11GR2>select count(*) from t2;
COUNT(*)
----------
10
21:19:48 XXF@ORA11GR2>delete t2 where x>=6;
5 rows deleted.
21:20:03 XXF@ORA11GR2>commit;
Commit complete.
21:20:06 XXF@ORA11GR2>select count(*) from t2;
COUNT(*)
----------
5
21:20:14 XXF@ORA11GR2>
9:下面的闪回查询已经超出了15分钟,我们发现,通过闪回查询,已经查到删除前的数据,此时利用的就是闪回数据归档中的数据
21:54:52 XXF@ORA11GR2>select count(*) from t2 as of timestamp(sysdate - 37/1440);
COUNT(*)
----------
10
21:55:49 XXF@ORA11GR2>select * from t2 as of timestamp(sysdate - 37/1440);
X
----------
6
7
8
9
10
1
2
3
4
5
10 rows selected.
21:56:21 XXF@ORA11GR2>
22:03:30 XXF@ORA11GR2>select * from xxf.t2 as of timestamp(to_date('2012-11-19 21:19:00','yyyy-mm-dd hh24:mi:ss'));
X
----------
6
7
8
9
10
1
2
3
4
5
10 rows selected.
22:03:36 XXF@ORA11GR2>
22:06:04 SYS@ORA11GR2>alter flashback archive fbad_ts_users purge before timestamp (sysdate-5/1440);
Flashback archive altered.
22:06:06 SYS@ORA11GR2>select * from xxf.t2 as of timestamp(to_date('2012-11-19 21:19:00','yyyy-mm-dd hh24:mi:ss'));
X
----------
1
2
3
4
5
22:06:11 SYS@ORA11GR2>
22:08:48 SYS@ORA11GR2>delete xxf.t2 where x>=3;
3 rows deleted.
22:08:58 SYS@ORA11GR2>commit;
Commit complete.
22:09:00 SYS@ORA11GR2>select * from xxf.t2;
X
----------
1
2
22:09:07 SYS@ORA11GR2>
22:09:25 SYS@ORA11GR2>select * from xxf.t2 as of timestamp(sysdate - 5/1440);
X
----------
1
2
3
4
5
22:09:33 SYS@ORA11GR2>
22:09:35 SYS@ORA11GR2>alter flashback archive fbad_ts_users purge all;
Flashback archive altered.
22:09:45 SYS@ORA11GR2>
22:09:46 SYS@ORA11GR2>select * from xxf.t2 as of timestamp(sysdate - 5/1440);
X
----------
1
2
3
4
5
22:09:50 SYS@ORA11GR2>
SYS@ORA11GR2>conn xxf/xxf
Connected.
XXF@ORA11GR2>drop table t2;
drop table t2
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table
XXF@ORA11GR2>drop table t2 purge;
drop table t2 purge
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table
XXF@ORA11GR2>
2:启用了闪回归档功能的表允许truncate
XXF@ORA11GR2>truncate table t2;
Table truncated.
XXF@ORA11GR2>
XXF@ORA11GR2>select * from xxf.t2 as of timestamp(sysdate - 5/1440);
X
----------
1
2
XXF@ORA11GR2>
3:允许添加、删除列
XXF@ORA11GR2>alter table t2 add y int;
Table altered.
XXF@ORA11GR2>desc t2;
Name Null? Type
----------------------------------------- -------- ---------------
X NUMBER(38)
Y NUMBER(38)
XXF@ORA11GR2>alter table t2 drop column x;
Table altered.
XXF@ORA11GR2>
3:不允许删除用户
XXF@ORA11GR2>conn / as sysdba
Connected.
SYS@ORA11GR2>drop user xxf cascade;
drop user xxf cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-55622: DML, ALTER and CREATE UNIQUE INDEX operations are not allowed on
table "XXF"."SYS_FBA_TCRV_75351"
SYS@ORA11GR2>
4:将表归档数据禁用后则可以正常删除
SYS@ORA11GR2>conn xxf/xxf
Connected.
XXF@ORA11GR2>
XXF@ORA11GR2>alter table t2 no flashback archive;
alter table t2 no flashback archive
*
ERROR at line 1:
ORA-55620: No privilege to use Flashback Archive
XXF@ORA11GR2>conn / as sysdba
Connected.
SYS@ORA11GR2>grant flashback archive administer to xxf;
Grant succeeded.
SYS@ORA11GR2>conn xxf/xxf
Connected.
XXF@ORA11GR2>alter table t2 no flashback archive;
Table altered.
XXF@ORA11GR2>conn / as sysdba
Connected.
SYS@ORA11GR2>drop user xxf cascade;
User dropped.
SYS@ORA11GR2>
四、删除闪回归档
SYS@ORA11GR2>drop flashback archive fbad_ts_users ;
Flashback archive dropped.
SYS@ORA11GR2>
五、小结
这个功能还是非常实用的,在空间允许且有这方面需求的情况下,可以考虑使用,虽然多多少少给运维带来一点点麻烦,但是哪怕用到了一次,那点儿麻烦也是值得的。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/685769/viewspace-749512/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/685769/viewspace-749512/