闪回数据归档的维护
创建闪回数据归档：create tablespace tbsfda datafile '+data'size 30M ;
create flashback archive fda1 tablespace tbsfda quota 10M retention 30 day;
grant flashback archive on fda1 to hr;
HR使用闪回数据归档：
alter table t063br14_fda2flashback archivefda1;-----：
select * from dba_flashback_archive;
select* from dba_flashback_archive_ts;
select* from dba_flashback_archive_tables;等待一段时间：
select* from hr.SYS_FBA_HIST_93080;
改动整个闪回数据归档的retention:
alter flashback archive fda1 modifyretention60 day;改动闪回数据归档表空间上的磁盘限额：
alter flashback archive fda1 modify tablespace tbsfda quota 20M;添加新的闪回数据归档表空间：
create tablespace tbsfda2datafile '+DATA' size 30 M ;
alter flashback archive fda1 add tablespace tbsfda2 quota 20M ;强制清理闪回数据归档：
alter flashback archive fda1 purgebeforetimestamp (systimestamp - 1/1440);
全局方案进化：
begin
dbms_flashback_archive.DISASSOCIATE_FBA('HR','T063BR14_FDA');
end;
/
update hr.SYS_FBA_HIST_93072set a=100 where a=1;
commit;
begin
dbms_flashback_archive.reASSOCIATE_FBA('HR','T063BR14_FDA');
end;
/
QL> select * from t063br14_fdaas of scn 8139263;
A
----------
100
全局方案进化2：
create table part1 ( anumber )
partition by range(a)
( partition p1values less than ( 100),
partition p2 values less than ( maxvalue) ) ;
insert intopart1values ( 100 ) ;
insert intopart1values ( 99) ;
commit;
select* from part1 partition( p2);
alter table part1flashback archive fda1;
alter table part1 split partition p2 at ( 200) into ( partition p21 , partition p22);
Error starting at line 17 in command:
alter table part1 split partition p2 at ( 200) into ( partition p21 , partition p22)
Error report:
SQL Error: ORA-55610: Invalid DDL statement on history-tracked table
begin
dbms_flashback_archive.DISASSOCIATE_FBA('HR','PART1');
end;
/
alter table hr.part1 split partition p2 at ( 200) into ( partition p21 , partition p22);
------
begin
dbms_flashback_archive.reASSOCIATE_FBA('HR','PART1');
end;
/删除带有闪回数据归档的表会报错的：
drop table hr.t063br14_fda;drop table hr.t063br14_fda
Error report:
SQL Error: ORA-55610: Invalid DDL statement on history-tracked table
如果要删除，去掉闪回数据归档：
SQL> conn hr/oracle_4U
Connected.
SQL> alter table t063br14_fda no flashback archive;
alter table t063br14_fda no flashback archive
*
ERROR at line 1:
ORA-55620: 无权使用闪回归档
SYS:
alter table hr.t063br14_fda no flashback archive;闪回数据归档空间使用：
selectsum(bytes)/1024/1024from dba_segments where segment_name in
( selecttable_namefrom dba_flashback_archive_tables where flashback_archive_name='FDA1');
闪回数据归档满了：
begin
for i in 1..10000
loop
update t063br14_fda3set a=to_char(i) ;
commit;
end loop;
end;
/
ORA-55617: Flashback Archive "FDA1" runs out of space and tracking on "T063BR14_FDA3" is suspended
ORA-06512: at line 4
alter flashback archive fda1 set default;
页:
[1]