|
|
- create tablespace tbsfda1 datafile size 15M autoextend off;
-
- select * from dba_tablespaces;
-
- select * from dba_flashback_archive;
-
- create flashback archive fda1 tablespace tbsfda1
- quota 10M retention 3 year;
- select * from dba_flashback_archive;
- select * from dba_flashback_archive_ts;
-
- select * from dba_flashback_archive_tables;
-
- alter table hr.tbig flashback archive fda1;
-
- select object_id from dba_objects o where o.object_name='TBIG'
- and o.owner='HR';
-
- select * from dba_flashback_archive_tables;
-
闪回数据归档，quota满后会报错：
这个时候，所有的旧值全部都堆在undo 表空间上：
这个时候应该加空间：
- alter database datafile '+DATA/orcl/datafile/tbsfda1.267.975923969'
- autoextend on;
-
- select * from dba_flashback_archive_ts;
-
- alter flashback archive fda1 modify tablespace tbsfda1 quota 1000M;
-
- alter flashback archive fda1 add tablespace users quota 1000M;
问题就解决了：
- SQL> update tbig set line=30000 where rownum=1;
- update tbig set line=30000 where rownum=1
- *
- ERROR at line 1:
- ORA-55617: Flashback Archive "FDA1" runs out of space and tracking on "TBIG" is
- suspended
- SQL> update tbig set line=30000 where rownum=1;
- 1 row updated.
- SQL> commit;
- Commit complete.
过一会查看这些表空间：
普通用户如何绑定闪回数据归档，以及闪回数据归档的默认：
- create flashback archive default fda2
- tablespace tbsfda1 quota 50M retention 40 day;
-
- select * from dba_flashback_archive;
-
- alter flashback archive fda1 set default;
-
- grant flashback archive on fda1 to hr;
只有两个闪回查询直接跨过DDL操作，但是闪回表不行：
- 1 select versions_xid, versions_startscn , a from t05311_a
- 2* versions between scn minvalue and maxvalue
- SQL> /
- VERSIONS_XID VERSIONS_STARTSCN A
- ---------------- ----------------- ----------
- 1
- 08000B0048050000 2756094 2
- 2756243 2
- SQL> select * from t05311_a
- 2 as of scn 2756242;
- A
- ----------
- 2
- SQL> select * from t05311_a
- 2 as of scn 2756093;
- A B
- ---------- --------------------
- 1 A
- SQL> select * from t05311_a
- 2 as of scn 2756094;
- A B
- ---------- --------------------
- 2 B
- SQL> select * from t05311_a
- 2 as of scn 2756242;
- A
- ----------
- 2
- SQL> alter table t05311_a enable row movement;
- Table altered.
- SQL> flashback table t05311_a to scn 2756094;
- flashback table t05311_a to scn 2756094
- *
- ERROR at line 1:
- ORA-01466: unable to read data - table definition has changed
- SQL>
人为purge闪回数据归档：
- alter flashback archive fda2 purge before timestamp
- (systimestamp - interval '1' second );
-
- alter system flush buffer_cache;
有一些表和闪回数据归档相联系后，某些DDL操作做不了：
- create table part1 ( a number )
- partition by range(a)
- (
- partition p1 values less than(100),
- partition p2 values less than (maxvalue)
- );
- SQL> insert into part1 values (100) ;
- 1 row created.
- SQL> commit;
- Commit complete.
- SQL> select * from part1 ;
- A
- ----------
- 100
- SQL> select * from part1 (p2 ) ;
- select * from part1 (p2 )
- *
- ERROR at line 1:
- ORA-00933: SQL command not properly ended
- SQL> select * from part1 partition (p2 ) ;
- A
- ----------
- 100
- SQL> alter table part1 flashback archive fda2;
- Table altered.
- SQL> update part1 set a=90 where a=100;
- update part1 set a=90 where a=100
- *
- ERROR at line 1:
- ORA-14402: updating partition key column would cause a partition change
- SQL> update part1 set a=900 where a=100;
- 1 row updated.
- SQL> commit;
- Commit complete.
- SQL> select * from part1;
- A
- ----------
- 900
- SQL> alter table part1 split partition p2 at (1000) into ( partition p21 , partition p22) ;
- alter table part1 split partition p2 at (1000) into ( partition p21 , partition p22)
- *
- ERROR at line 1:
- ORA-55610: Invalid DDL statement on history-tracked table
- SQL>
- grant flashback archive on fda2 to hr;
- select * from dba_flashback_archive_tables;
- select * from hr.SYS_FBA_HIST_80616;
- select * from dba_tab_partitions tp
- where tp.table_name='PART1';
全局方案进化（Full Schema Evolution），顺便改了数据的历史：
- SQL> select * from part1;
- A
- ----------
- 900
- SQL> alter table part1 split partition p2 at (1000) into ( partition p21 , partition p22) ;
- alter table part1 split partition p2 at (1000) into ( partition p21 , partition p22)
- *
- ERROR at line 1:
- ORA-55610: Invalid DDL statement on history-tracked table
- SQL> select * from part1;
- A
- ----------
- 900
- SQL> select * from part1 as of scn 2759049;
- A
- ----------
- 900
- SQL> select * from part1 as of scn 2759048;
- A
- ----------
- 100
- SQL> alter table part1 split partition p2 at (1000) into ( partition p21 , partition p22) ;
- Table altered.
- SQL> select * from part1 as of scn 2759048;
- A
- ----------
- 200
- SQL>
- begin
- dbms_flashback_archive.disassociate_fba(owner_name => 'HR',
- table_name => 'PART1');
- end;
- select * from dba_tab_partitions tp
- where tp.table_name='PART1';
- select * from dba_flashback_archive_tables;
- select * from hr.SYS_FBA_HIST_80616;
- update hr.SYS_FBA_HIST_80616 set a=200;
- commit;
- begin
- dbms_flashback_archive.reassociate_fba(owner_name => 'HR',
- table_name => 'PART1');
- end;
-
-----------------------------------------------------
索引不在show recyclebin 显示之列：
flashback table ... to before drop;索引没有改回原来的名字：
- select flashback_on from v$database;
- select * from v$flashback_database_log;
- select * from v$flashback_database_logfile;
- alter database flashback on;
- SELECT addr FROM x$ksbdp WHERE ksbdpnam = 'RVWR';
- SELECT ksmfsnam,ksmfstyp FROM x$ksmfsv WHERE ksmfsadr = '0000000060028DE8';
- SELECT bytes FROM v$sgastat
- WHERE pool = 'shared pool' AND name = 'flashback generation buff';
闪回用日志（带上穿越化身命令）：
|
|
发表于 2018-5-12 10:02:42
