|
- select * from dba_flashback_archive;
- select * from dba_flashback_archive_tables;
- select * from dba_flashback_archive_ts;
- select * from dba_tablespaces;
- create tablespace tbs_fda1 datafile size 20M ;
- create flashback archive fda1 tablespace tbs_fda1
- quota 10M retention 2 year;
-
- alter flashback archive fda1 set default;
- grant flashback archive on fda1 to hr;
- select object_id from dba_objects o where o.object_name='EMPLOYEES'
- and o.owner='HR';
- select versions_xid, versions_operation, versions_startscn , employee_id,
- salary from hr.employees
- versions between scn minvalue and maxvalue
- where employee_id=100;
- 等5分钟:
- select * from hr.SYS_FBA_HIST_73953;
复制代码 闪回表是用不上闪回数据归档的:
- select versions_xid, versions_operation, versions_startscn , employee_id,
- salary from hr.employees
- versions between scn minvalue and maxvalue
- where employee_id=100;
-
- create undo tablespace undotbs1 datafile size 30M;
-
- select * from dba_flashback_archive_tables;
-
- select * from hr.SYS_FBA_HIST_74874;
- select versions_xid, versions_operation, versions_startscn ,a, b
- from hr.t05311_a
- versions between scn minvalue and maxvalue ;
-
- select * from hr.t05311_a
- as of scn 1601367;
-
- alter table hr.t05311_a enable row movement;
-
- flashback table hr.t05311_a to scn 1601367;
复制代码- select * from dba_flashback_archive;
- select * from dba_flashback_archive_tables;
- select * from dba_flashback_archive_ts;
- select bytes/(1024*1024) from dba_segments s
- where s.owner='HR' and s.segment_name in ('SYS_FBA_HIST_74874' ,'T05311_A');
-
- select * from hr.SYS_FBA_HIST_74874;
-
- alter flashback archive fda1 add tablespace users quota 100M ;
-
- alter flashback archive fda1 modify retention 30 day;
-
- alter flashback archive fda1 modify tablespace tbs_fda1 quota 15M;
-
-
-
-
复制代码- [oracle@station90 ~]$ sqlplus /nolog
- SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 28 21:00:09 2018
- Copyright (c) 1982, 2009, Oracle. All rights reserved.
- SQL> conn hr/oracle_4U
- Connected.
- SQL> create table t05311_b ( a number )
- 2 partition by range (a)
- 3 ( partition p1 values less than (100) ,
- 4 partition p2 values less than (maxvalue)) ;
- Table created.
- SQL> alter table t05311_b flashback archive fda2 ;
- Table altered.
复制代码- select * from dba_tab_partitions tp
- where tp.table_owner='HR' and tp.table_name='T05311_B';
-
- alter table hr.t05311_b split partition p1 at (50)
- into ( partition p11, partition p12 ) ;
-
- select * from dba_flashback_archive_tables;
-
- alter table hr.t05311_a no flashback archive;
-
- select * from hr.SYS_FBA_HIST_74884;
-
- begin
- dbms_flashback_archive.disassociate_fba(owner_name => 'HR',table_name => 'T05311_B');
- end;
复制代码
闪回数据归档quota满后,会报错(在不可扩展表空间上):
- SQL> /
- begin
- *
- ERROR at line 1:
- ORA-55617: Flashback Archive "FDA1" runs out of space and tracking on
- "T05311_B" is suspended
- ORA-06512: at line 4
复制代码
|
|