设为首页收藏本站

Botang唐波's Oracle Station

查看: 446|回复: 0

课程第23次(2018-03-28星期三)

[复制链接]

719

主题

1081

帖子

7868

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
7868
发表于 2018-3-28 19:56:54 | 显示全部楼层 |阅读模式
  1. select  * from dba_flashback_archive;

  2. select * from dba_flashback_archive_tables;

  3. select * from dba_flashback_archive_ts;

  4. select  * from dba_tablespaces;

  5. create tablespace tbs_fda1  datafile size 20M ;

  6. create flashback archive fda1 tablespace tbs_fda1
  7. quota  10M retention 2 year;

  8. alter flashback archive fda1 set default;

  9. grant flashback archive on fda1 to hr;

  10. select  object_id from dba_objects o where o.object_name='EMPLOYEES'
  11.   and o.owner='HR';

  12. select  versions_xid, versions_operation, versions_startscn , employee_id,
  13.    salary from hr.employees
  14.    versions between scn minvalue and maxvalue
  15.    where employee_id=100;

  16. 等5分钟:
  17. select  * from hr.SYS_FBA_HIST_73953;

复制代码
闪回表是用不上闪回数据归档的:

  1. SQL> conn hr/oracle_4U
  2. Connected.
  3. SQL> create table t05311_a ( a number   , b varchar2(20))   ;

  4. Table created.

  5. SQL> insert into t05311_a values ( 0, 'O')  ;

  6. 1 row created.

  7. SQL> commit;

  8. Commit complete.

  9. SQL> alter table t05311_a  flashback archive;

  10. Table altered.

  11. SQL> update t05311_a set a=1, b='a' ;

  12. 1 row updated.

  13. SQL> commit;

  14. Commit complete.

  15. SQL> desc dbms_flashback
  16. ERROR:
  17. ORA-04043: object "SYS"."DBMS_FLASHBACK" does not exist


  18. SQL> select  timestamp2scn ( systimestamp) from dual ;
  19. select        timestamp2scn ( systimestamp) from dual
  20.         *
  21. ERROR at line 1:
  22. ORA-00904: "TIMESTAMP2SCN": invalid identifier


  23. SQL> select  timestamp_to_scn ( systimestamp) from dual ;

  24. TIMESTAMP_TO_SCN(SYSTIMESTAMP)
  25. ------------------------------
  26.                        1602365

  27. SQL> select  timestamp_to_scn ( systimestamp) from dual ;

  28. TIMESTAMP_TO_SCN(SYSTIMESTAMP)
  29. ------------------------------
  30.                        1602367

  31. SQL> select  timestamp_to_scn ( systimestamp) from dual ;

  32. TIMESTAMP_TO_SCN(SYSTIMESTAMP)
  33. ------------------------------
  34.                        1602367

  35. SQL> select  timestamp_to_scn ( systimestamp) from dual ;

  36. TIMESTAMP_TO_SCN(SYSTIMESTAMP)
  37. ------------------------------
  38.                        1602367

  39. SQL> select  timestamp_to_scn ( systimestamp) from dual ;

  40. TIMESTAMP_TO_SCN(SYSTIMESTAMP)
  41. ------------------------------
  42.                        1602367

  43. SQL> select  timestamp_to_scn ( systimestamp) from dual ;

  44. TIMESTAMP_TO_SCN(SYSTIMESTAMP)
  45. ------------------------------
  46.                        1602368

  47. SQL> select  timestamp_to_scn ( systimestamp) from dual ;

  48. TIMESTAMP_TO_SCN(SYSTIMESTAMP)
  49. ------------------------------
  50.                        1602369

  51. SQL> alter table t05311_A drop ( b ) ;  

  52. Table altered.

  53. SQL> select  * from t05311_A;

  54.          A
  55. ----------
  56.          1

  57. SQL> select  timestamp_to_scn ( systimestamp) from dual ;

  58. TIMESTAMP_TO_SCN(SYSTIMESTAMP)
  59. ------------------------------
  60.                        1602582

  61. SQL> update t05311_A set a=2 ;

  62. 1 row updated.

  63. SQL> commit;

  64. Commit complete.

  65. SQL> select  * from t05311_A;

  66.          A
  67. ----------
  68.          2

  69. SQL> flashback table t05311_A to scn 1602582 ;

  70. Flashback complete.

  71. SQL> select  * from t05311_A;

  72.          A
  73. ----------
  74.          1

  75. SQL> flashback table t05311_A to scn 1602582 ;

复制代码
  1. select  versions_xid, versions_operation, versions_startscn , employee_id,
  2.    salary from hr.employees
  3.    versions between scn minvalue and maxvalue
  4.    where employee_id=100;
  5.    
  6. create undo tablespace undotbs1 datafile size 30M;

  7. select * from dba_flashback_archive_tables;

  8. select  * from hr.SYS_FBA_HIST_74874;

  9. select  versions_xid, versions_operation, versions_startscn ,a, b
  10.    from hr.t05311_a
  11.    versions between scn minvalue and maxvalue   ;
  12.    
  13. select  * from hr.t05311_a
  14.   as of scn 1601367;
  15.   
  16.   alter table hr.t05311_a  enable row movement;
  17.   
  18.   flashback table  hr.t05311_a to scn 1601367;
复制代码
  1. select  * from dba_flashback_archive;

  2. select * from dba_flashback_archive_tables;

  3. select * from dba_flashback_archive_ts;

  4. select  bytes/(1024*1024)  from dba_segments s
  5. where s.owner='HR' and s.segment_name in ('SYS_FBA_HIST_74874'   ,'T05311_A');

  6. select  *  from hr.SYS_FBA_HIST_74874;

  7. alter flashback archive fda1 add tablespace users quota  100M  ;

  8. alter flashback archive  fda1 modify retention 30 day;

  9. alter flashback archive fda1 modify  tablespace tbs_fda1  quota 15M;  



复制代码
  1. [oracle@station90 ~]$ sqlplus /nolog

  2. SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 28 21:00:09 2018

  3. Copyright (c) 1982, 2009, Oracle.  All rights reserved.

  4. SQL> conn  hr/oracle_4U
  5. Connected.
  6. SQL> create table t05311_b ( a  number )
  7.   2  partition by range (a)
  8.   3  ( partition  p1 values less than (100) ,
  9.   4    partition p2 values less than (maxvalue))  ;

  10. Table created.

  11. SQL> alter table t05311_b flashback archive fda2 ;  

  12. Table altered.

复制代码
  1. select  * from dba_tab_partitions tp
  2. where tp.table_owner='HR' and tp.table_name='T05311_B';

  3. alter table hr.t05311_b  split partition p1 at (50)
  4.   into ( partition p11, partition p12 ) ;
  5.   
  6.   select  * from dba_flashback_archive_tables;
  7.   
  8.   alter table hr.t05311_a no flashback archive;
  9.   
  10.   select  * from hr.SYS_FBA_HIST_74884;
  11.   
  12.   begin
  13.      dbms_flashback_archive.disassociate_fba(owner_name => 'HR',table_name => 'T05311_B');
  14.   end;
复制代码

闪回数据归档quota满后,会报错(在不可扩展表空间上):
  1. SQL> /
  2. begin
  3. *
  4. ERROR at line 1:
  5. ORA-55617: Flashback Archive "FDA1" runs out of space and tracking on
  6. "T05311_B" is suspended
  7. ORA-06512: at line 4
复制代码




回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Botang唐波's Oracle Station   

GMT+8, 2018-9-20 12:58 , Processed in 0.107941 second(s), 24 queries .

快速回复 返回顶部 返回列表