课程第23次（2018-03-28星期三）

botang

1. select  * from dba_flashback_archive;
   
2. 
   
3. select * from dba_flashback_archive_tables;
   
4. 
   
5. select * from dba_flashback_archive_ts;
   
6. 
   
7. select  * from dba_tablespaces;
   
8. 
   
9. create tablespace tbs_fda1  datafile size 20M ;
   
10. 
    
11. create flashback archive fda1 tablespace tbs_fda1
    
12. quota  10M retention 2 year;
    
13. 
    
14. alter flashback archive fda1 set default;
    
15. 
    
16. grant flashback archive on fda1 to hr;
    
17. 
    
18. select  object_id from dba_objects o where o.object_name='EMPLOYEES'
    
19.   and o.owner='HR';
    
20. 
    
21. select  versions_xid, versions_operation, versions_startscn , employee_id,
    
22.    salary from hr.employees
    
23.    versions between scn minvalue and maxvalue
    
24.    where employee_id=100;
    
25. 
    
26. 等5分钟：
    
27. select  * from hr.SYS_FBA_HIST_73953;
    
28. 
    

复制代码

闪回表是用不上闪回数据归档的：

1. SQL> conn hr/oracle_4U
   
2. Connected.
   
3. SQL> create table t05311_a ( a number   , b varchar2(20))   ;
   
4. 
   
5. Table created.
   
6. 
   
7. SQL> insert into t05311_a values ( 0, 'O')  ;
   
8. 
   
9. 1 row created.
   
10. 
    
11. SQL> commit;
    
12. 
    
13. Commit complete.
    
14. 
    
15. SQL> alter table t05311_a  flashback archive;
    
16. 
    
17. Table altered.
    
18. 
    
19. SQL> update t05311_a set a=1, b='a' ;
    
20. 
    
21. 1 row updated.
    
22. 
    
23. SQL> commit;
    
24. 
    
25. Commit complete.
    
26. 
    
27. SQL> desc dbms_flashback
    
28. ERROR:
    
29. ORA-04043: object "SYS"."DBMS_FLASHBACK" does not exist
    
30. 
    
31. 
    
32. SQL> select  timestamp2scn ( systimestamp) from dual ;
    
33. select        timestamp2scn ( systimestamp) from dual
    
34.         *
    
35. ERROR at line 1:
    
36. ORA-00904: "TIMESTAMP2SCN": invalid identifier
    
37. 
    
38. 
    
39. SQL> select  timestamp_to_scn ( systimestamp) from dual ;
    
40. 
    
41. TIMESTAMP_TO_SCN(SYSTIMESTAMP)
    
42. ------------------------------
    
43.                        1602365
    
44. 
    
45. SQL> select  timestamp_to_scn ( systimestamp) from dual ;
    
46. 
    
47. TIMESTAMP_TO_SCN(SYSTIMESTAMP)
    
48. ------------------------------
    
49.                        1602367
    
50. 
    
51. SQL> select  timestamp_to_scn ( systimestamp) from dual ;
    
52. 
    
53. TIMESTAMP_TO_SCN(SYSTIMESTAMP)
    
54. ------------------------------
    
55.                        1602367
    
56. 
    
57. SQL> select  timestamp_to_scn ( systimestamp) from dual ;
    
58. 
    
59. TIMESTAMP_TO_SCN(SYSTIMESTAMP)
    
60. ------------------------------
    
61.                        1602367
    
62. 
    
63. SQL> select  timestamp_to_scn ( systimestamp) from dual ;
    
64. 
    
65. TIMESTAMP_TO_SCN(SYSTIMESTAMP)
    
66. ------------------------------
    
67.                        1602367
    
68. 
    
69. SQL> select  timestamp_to_scn ( systimestamp) from dual ;
    
70. 
    
71. TIMESTAMP_TO_SCN(SYSTIMESTAMP)
    
72. ------------------------------
    
73.                        1602368
    
74. 
    
75. SQL> select  timestamp_to_scn ( systimestamp) from dual ;
    
76. 
    
77. TIMESTAMP_TO_SCN(SYSTIMESTAMP)
    
78. ------------------------------
    
79.                        1602369
    
80. 
    
81. SQL> alter table t05311_A drop ( b ) ;  
    
82. 
    
83. Table altered.
    
84. 
    
85. SQL> select  * from t05311_A;
    
86. 
    
87.          A
    
88. ----------
    
89.          1
    
90. 
    
91. SQL> select  timestamp_to_scn ( systimestamp) from dual ;
    
92. 
    
93. TIMESTAMP_TO_SCN(SYSTIMESTAMP)
    
94. ------------------------------
    
95.                        1602582
    
96. 
    
97. SQL> update t05311_A set a=2 ;
    
98. 
    
99. 1 row updated.
    
100. 
     
101. SQL> commit;
     
102. 
     
103. Commit complete.
     
104. 
     
105. SQL> select  * from t05311_A;
     
106. 
     
107.          A
     
108. ----------
     
109.          2
     
110. 
     
111. SQL> flashback table t05311_A to scn 1602582 ;
     
112. 
     
113. Flashback complete.
     
114. 
     
115. SQL> select  * from t05311_A;
     
116. 
     
117.          A
     
118. ----------
     
119.          1
     
120. 
     
121. SQL> flashback table t05311_A to scn 1602582 ;
     
122. 
     

复制代码

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. 
   
8. select * from dba_flashback_archive_tables;
   
9. 
   
10. select  * from hr.SYS_FBA_HIST_74874;
    
11. 
    
12. select  versions_xid, versions_operation, versions_startscn ,a, b
    
13.    from hr.t05311_a
    
14.    versions between scn minvalue and maxvalue   ;
    
15.    
    
16. select  * from hr.t05311_a
    
17.   as of scn 1601367;
    
18.   
    
19.   alter table hr.t05311_a  enable row movement;
    
20.   
    
21.   flashback table  hr.t05311_a to scn 1601367;

复制代码

1. select  * from dba_flashback_archive;
   
2. 
   
3. select * from dba_flashback_archive_tables;
   
4. 
   
5. select * from dba_flashback_archive_ts;
   
6. 
   
7. select  bytes/(1024*1024)  from dba_segments s
   
8. where s.owner='HR' and s.segment_name in ('SYS_FBA_HIST_74874'   ,'T05311_A');
   
9. 
   
10. select  *  from hr.SYS_FBA_HIST_74874;
    
11. 
    
12. alter flashback archive fda1 add tablespace users quota  100M  ;
    
13. 
    
14. alter flashback archive  fda1 modify retention 30 day;
    
15. 
    
16. alter flashback archive fda1 modify  tablespace tbs_fda1  quota 15M;  
    
17. 
    
18. 
    
19. 
    

复制代码

1. [oracle@station90 ~]$ sqlplus /nolog
   
2. 
   
3. SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 28 21:00:09 2018
   
4. 
   
5. Copyright (c) 1982, 2009, Oracle.  All rights reserved.
   
6. 
   
7. SQL> conn  hr/oracle_4U
   
8. Connected.
   
9. SQL> create table t05311_b ( a  number )
   
10.   2  partition by range (a)
    
11.   3  ( partition  p1 values less than (100) ,
    
12.   4    partition p2 values less than (maxvalue))  ;
    
13. 
    
14. Table created.
    
15. 
    
16. SQL> alter table t05311_b flashback archive fda2 ;  
    
17. 
    
18. Table altered.
    
19. 
    

复制代码

1. select  * from dba_tab_partitions tp
   
2. where tp.table_owner='HR' and tp.table_name='T05311_B';
   
3. 
   
4. alter table hr.t05311_b  split partition p1 at (50)
   
5.   into ( partition p11, partition p12 ) ;
   
6.   
   
7.   select  * from dba_flashback_archive_tables;
   
8.   
   
9.   alter table hr.t05311_a no flashback archive;
   
10.   
    
11.   select  * from hr.SYS_FBA_HIST_74884;
    
12.   
    
13.   begin
    
14.      dbms_flashback_archive.disassociate_fba(owner_name => 'HR',table_name => 'T05311_B');
    
15.   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
   

复制代码