课程第9次（2018-06-05星期二）

botang

1. 闪回单点查询（9i就能做的闪回，闪回技术2，因为有undo所以能够见到提交以前的样子）：

1. SQL> conn hr/oracle_4U
   
2. Connected.
   
3. SQL> select  salary from employees where employee_id=100;
   
4. 
   
5.     SALARY
   
6. ----------
   
7.      24000
   
8. 
   
9. SQL> update employees set salary=25000  where employee_id=100;
   
10. 
    
11. 1 row updated.
    
12. 
    
13. SQL> commit;
    
14. 
    
15. Commit complete.
    
16. 
    
17. SQL> select  salary from employees where employee_id=100;
    
18. 
    
19.     SALARY
    
20. ----------
    
21.      25000
    
22. 
    
23. 
    
24. SQL> select  salary from employees as of timestamp systimestamp-1/1440       where employee_id=100;
    
25. 
    
26.     SALARY
    
27. ----------
    
28.      24000
    
29. 
    
30. SQL>
    

复制代码

2. 闪回技术1（10g， FLASHBACK_TRANSACTION_QUERY，是所有闪回的原理，查询它就是以Oracle的语言来查看整个UNDO表空间）：




3. 闪回技术4 （闪回表）：

1. SQL> flashback table employees to scn 1165416;
   
2. flashback table employees to scn 1165416
   
3.                 *
   
4. ERROR at line 1:
   
5. ORA-08189: cannot flashback the table because row movement is not enabled
   

复制代码

1. SQL> alter table employees enable row movement;
   
2. 
   
3. Table altered.
   
4. 
   
5. SQL> flashback table employees to scn 1165416;
   
6. 
   
7. Flashback complete.
   
8. 
   
9. SQL> alter table employees disable row movement;
   
10. 
    
11. Table altered.
    
12. 
    
13. SQL> select  salary from employees where employee_id=100;
    
14. 
    
15.     SALARY
    
16. ----------
    
17.      24000
    
18. 
    
19. SQL>
    

复制代码

证明它是一个事务，而且还是删除再添加：

1. select  * from Flashback_Transaction_Query  ftq
   
2. where ftq.logon_user='HR' and ftq.table_name='EMPLOYEES'
   
3. order by ftq.commit_timestamp;
   

复制代码

4. 闪回技术3（闪回版本查询，把 Flashback_Transaction_Query 的信息映射到自己的表（employees）的查询技术）：

1. SQL> select versions_xid, versions_startscn, versions_operation, salary from employees
   
2.   2  versions between scn minvalue and maxvalue
   
3.   3  where employee_id=100;
   
4. 
   
5. VERSIONS_XID         VERSIONS_STARTSCN V         SALARY
   
6. ---------------- ----------------- - ----------
   
7. 05001400BB030000           1170658 I          24000
   
8. 05001400BB030000           1170658 D          25000
   
9.                                           25000
   

复制代码

versions_startscn就是flashback_transaction_query中的提交的时间。在EM上看同样versions查询，startscn被图形界面-1：

1. oracle@station87 ~]$ sqlplus /nolog
   
2. 
   
3. SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 6 04:18:23 2018
   
4. 
   
5. Copyright (c) 1982, 2009, Oracle.  All rights reserved.
   
6. 
   
7. SQL> conn hr/oracle_4U
   
8. Connected.
   
9. SQL> select salary from employees where employee_id=100;
   
10. 
    
11.     SALARY
    
12. ----------
    
13.      24000
    
14. 
    
15. SQL> update employees set salary=30000 where employee_id=100;
    
16. 
    
17. 1 row updated.
    
18. 
    
19. SQL> commit;
    
20. 
    
21. Commit complete.
    
22. 
    
23. SQL> update employees set salary=40000 where employee_id=100;
    
24. 
    
25. 1 row updated.
    
26. 
    
27. SQL> commit;
    
28. 
    
29. Commit complete.
    
30. 
    
31. SQL> update employees set salary=50000 where employee_id=100;
    
32. 
    
33. 1 row updated.
    
34. 
    
35. SQL> commit;
    
36. 
    
37. Commit complete.
    
38. 
    
39. SQL>  select versions_xid, versions_startscn, versions_operation, salary from employees
    
40.   2   versions between scn minvalue and maxvalue
    
41.   3  where employee_id=100  order by versions_startscn;
    
42. 
    
43. VERSIONS_XID         VERSIONS_STARTSCN V         SALARY
    
44. ---------------- ----------------- - ----------
    
45. 05001400BB030000           1170658 D          25000
    
46. 05001400BB030000           1170658 I          24000
    
47. 06001600A0030000           1172047 U          30000
    
48. 02000E008D030000           1172182 U          40000
    
49. 09000400C1030000           1172237 U          50000
    
50.                                           25000
    
51. 
    
52. 6 rows selected.
    
53. 
    
54. SQL> /
    
55. 
    
56. VERSIONS_XID         VERSIONS_STARTSCN V         SALARY
    
57. ---------------- ----------------- - ----------
    
58. 05001400BB030000           1170658 D          25000
    
59. 05001400BB030000           1170658 I          24000
    
60. 06001600A0030000           1172047 U          30000
    
61. 02000E008D030000           1172182 U          40000
    
62. 09000400C1030000           1172237 U          50000
    
63. 09001600C7030000           1178141 D          50000
    
64. 09001600C7030000           1178141 I          30000
    
65.                                           25000
    
66. 
    
67. 8 rows selected.
    
68. 
    
69. SQL> select  salary from employees where employee_id=100;
    
70. 
    
71.     SALARY
    
72. ----------
    
73.      30000
    
74. 
    
75. SQL> clear
    
76. SQL>
    
77. SQL>
    
78. SQL>
    
79. SQL>
    
80. SQL>
    
81. SQL>
    
82. SQL> !clear
    
83. 
    
84. 
    
85. SQL> create table t05310_a( a number ) ;
    
86. 
    
87. Table created.
    
88. 
    
89. SQL> insert into t05310_a values (1) ;
    
90. 
    
91. 1 row created.
    
92. 
    
93. SQL> commit;
    
94. 
    
95. Commit complete.
    
96. 
    
97. SQL> update t05310_a set a=2 ;
    
98. 
    
99. 1 row updated.
    
100. 
     
101. SQL> commit;
     
102. 
     
103. Commit complete.
     
104. 
     
105. SQL> select versions_xid, versions_startscn, versions_operation,a from  t05210_a
     
106.   2  
     
107. 
     
108. SQL>
     
109. SQL>
     
110. SQL>
     
111. SQL> select versions_xid, versions_startscn, versions_operation,a from  t05310_a
     
112.   2  versions between scn minvalue and maxvalue;
     
113. 
     
114. VERSIONS_XID         VERSIONS_STARTSCN V              A
     
115. ---------------- ----------------- - ----------
     
116. 03000D00C6030000           1179717 U              2
     
117. 0200140096030000           1179700 I              1
     
118. 
     
119. SQL> select  a from t05310_a as of scn 1179700 ;
     
120. 
     
121.          A
     
122. ----------
     
123.          1
     
124. 
     
125. SQL> alter table t05310_a add ( b varchar2(20)) ;
     
126. 
     
127. Table altered.
     
128. 
     
129. SQL> update t05310_a set a=3,b='C' ;
     
130. 
     
131. 1 row updated.
     
132. 
     
133. SQL> commit;
     
134. 
     
135. Commit complete.
     
136. 
     
137. SQL> select  * from t05310_a;
     
138. 
     
139.          A B
     
140. ---------- --------------------
     
141.          3 C
     
142. 
     
143. SQL> select versions_xid, versions_startscn, versions_operation,a from  t05310_a
     
144.   2  versions between scn minvalue and maxvalue;
     
145. 
     
146. VERSIONS_XID         VERSIONS_STARTSCN V              A
     
147. ---------------- ----------------- - ----------
     
148. 09001D00CA030000           1180048 U              3
     
149. 03000D00C6030000           1179717 U              2
     
150. 0200140096030000           1179700 I              1
     
151. 
     
152. SQL> select versions_xid, versions_startscn, versions_operation,a,b from  t05310_a
     
153.   2  versions between scn minvalue and maxvalue;
     
154. 
     
155. VERSIONS_XID         VERSIONS_STARTSCN V              A B
     
156. ---------------- ----------------- - ---------- --------------------
     
157. 09001D00CA030000           1180048 U              3 C
     
158. 03000D00C6030000           1179717 U              2
     
159. 0200140096030000           1179700 I              1
     
160. 
     
161. SQL> select  a from t05310_a as of scn 1179700 ;
     
162. 
     
163.          A
     
164. ----------
     
165.          1
     
166. 
     
167. SQL> alter table t05310_a enable row movement;
     
168. 
     
169. Table altered.
     
170. 
     
171. SQL> flashback table t05310_a to scn 1179700;
     
172. 
     
173. Flashback complete.
     
174. 
     
175. SQL> select  a from t05310_a ;
     
176. 
     
177.          A
     
178. ----------
     
179.          1
     
180. 
     
181. SQL> select versions_xid, versions_startscn, versions_operation,a,b from  t05310_a
     
182.   2  versions between scn minvalue and maxvalue;versions between scn minvalue and maxvalue;
     
183. versions between scn minvalue and maxvalue;versions between scn minvalue and maxvalue
     
184.                                           *
     
185. ERROR at line 2:
     
186. ORA-00911: invalid character
     
187. 
     
188. 
     
189. SQL> select versions_xid, versions_startscn, versions_operation,a,b from  t05310_a         
     
190.   2  versions between scn minvalue and maxvalue;                              
     
191. 
     
192. VERSIONS_XID         VERSIONS_STARTSCN V              A B
     
193. ---------------- ----------------- - ---------- --------------------
     
194. 01000D00DA020000           1180198 I              1
     
195. 01000D00DA020000           1180198 D              3 C
     
196. 09001D00CA030000           1180048 U              3 C
     
197. 03000D00C6030000           1179717 U              2
     
198. 0200140096030000           1179700 I              1
     
199. 
     
200. SQL> select  a from t05310_a as of scn 1180198 ;
     
201. 
     
202.          A
     
203. ----------
     
204.          1
     
205. 
     
206. SQL> update t05310_a set a=4 ;
     
207. 
     
208. 1 row updated.
     
209. 
     
210. SQL> COMMIT;
     
211. 
     
212. Commit complete.
     
213. 
     
214. SQL> select  * from t05310_a;
     
215. 
     
216.          A B
     
217. ---------- --------------------
     
218.          4
     
219. 
     
220. SQL> select versions_xid, versions_startscn, versions_operation,a,b from  t05310_a
     
221.   2   versions between scn minvalue and maxvalue;
     
222. 
     
223. VERSIONS_XID         VERSIONS_STARTSCN V              A B
     
224. ---------------- ----------------- - ---------- --------------------
     
225. 06002100AB030000           1180318 U              4
     
226. 01000D00DA020000           1180198 I              1
     
227. 01000D00DA020000           1180198 D              3 C
     
228. 09001D00CA030000           1180048 U              3 C
     
229. 03000D00C6030000           1179717 U              2
     
230. 0200140096030000           1179700 I              1
     
231. 
     
232. 6 rows selected.
     
233. 
     
234. SQL> select  a from t05310_a as of scn 1180198 ;
     
235. 
     
236.          A
     
237. ----------
     
238.          1
     
239. 
     
240. SQL> alter table t05310_a drop ( b) ;
     
241. 
     
242. Table altered.
     
243. 
     
244. SQL> select  a from t05310_a as of scn 1180198 ;
     
245. select        a from t05310_a as of scn 1180198
     
246.                *
     
247. ERROR at line 1:
     
248. ORA-01466: unable to read data - table definition has changed
     
249. 
     
250. 
     
251. SQL>
     

复制代码

5. 闪回技术8:闪回事务

1. undefine s1
   
2. create table &&s1( id number ) ;
   
3. ! sleep 5
   
4. insert into &&s1 values (1) ;
   
5. insert into &&s1 values (2) ;
   
6. insert into &&s1 values (3) ;
   
7. commit;
   
8. update &&s1 set id=11 where id=1;
   
9. update &&s1 set id=22 where id=2;
   
10. update &&s1 set id=33 where id=3;
    
11. commit;
    
12. update &&s1 set id=222 where id=22;
    
13. update &&s1 set id=333 where id=33;
    
14. commit;
    
15. update &&s1 set id=22 where id=222;
    
16. commit;

复制代码

1. select  d.SUPPLEMENTAL_LOG_DATA_MIN,
   
2.           d.SUPPLEMENTAL_LOG_DATA_PK,
   
3.           d.SUPPLEMENTAL_LOG_DATA_FK
   
4. from v_$database  d;
   
5. 
   
6. alter database add supplemental log data;
   
7. 
   
8. alter database add supplemental log data (primary key) columns;

复制代码

----------------------------------------------------------------------------
T_NOCASCADE:

1. select  * from flashback_transaction_query ftq
   
2. where ftq.logon_user='HR' and ftq.table_name='T_NOCASCADE'
   
3. order by commit_Scn;
   
4. 
   
5. begin
   
6.     dbms_flashback.transaction_backout(numtxns => 1,
   
7.     xids => xid_array('03000600D6030000'),
   
8.     options => dbms_flashback.nocascade);
   
9.   end;

复制代码

1. SQL> select * from t_nocascade;
   
2. 
   
3.         ID
   
4. ----------
   
5.         11
   
6.         22
   
7.        333
   

复制代码

T_CASCADE:

1.     select  * from flashback_transaction_query ftq
   
2.     where ftq.logon_user='HR' and ftq.table_name='T_CASCADE'
   
3.     order by commit_Scn;
   
4. 
   
5.     begin
   
6.         dbms_flashback.transaction_backout(numtxns => 1,
   
7.         xids => xid_array('0A000D002F030000'),
   
8.         options => dbms_flashback.cascade);
   
9.       end;
   
10.       
    
11.       select  * from dba_flashback_txn_report;
    
12.       
    
13.       commit;

复制代码

1. <?xml version="1.0" encoding="ISO-8859-1"?>
   
2. <COMP_XID_REPORT XID="04001B0021030000">
   
3.         <TRANSACTION XID="0A000D002F030000">
   
4.         <CHARACTERISTICS>
   
5.         </CHARACTERISTICS>
   
6.         <UNDO_SQL>
   
7.                 <USQL exec="yes">
   
8.                  update "HR"."T_CASCADE" set "ID" = '3' where "ID" = '33' and ROWID = 'AAATGVAAEAAAAI9AAC'
   
9.                 </USQL>
   
10.                 <USQL exec="yes">
    
11.                  update "HR"."T_CASCADE" set "ID" = '2' where "ID" = '22' and ROWID = 'AAATGVAAEAAAAI9AAB'
    
12.                 </USQL>
    
13.                 <USQL exec="yes">
    
14.                  update "HR"."T_CASCADE" set "ID" = '1' where "ID" = '11' and ROWID = 'AAATGVAAEAAAAI9AAA'
    
15.                 </USQL>
    
16.         </UNDO_SQL>
    
17.         <DEPENDENT_XIDS>
    
18.                 <TRANSACTION XID="0400160020030000">
    
19.                 <CHARACTERISTICS>
    
20.                 </CHARACTERISTICS>
    
21.                 <UNDO_SQL>
    
22.                         <USQL exec="yes">
    
23.                          update "HR"."T_CASCADE" set "ID" = '33' where "ID" = '333' and ROWID = 'AAATGVAAEAAAAI9AAC'
    
24.                         </USQL>
    
25.                         <USQL exec="yes">
    
26.                          update "HR"."T_CASCADE" set "ID" = '22' where "ID" = '222' and ROWID = 'AAATGVAAEAAAAI9AAB'
    
27.                         </USQL>
    
28.                 </UNDO_SQL>
    
29.                 <DEPENDENT_XIDS>
    
30.                         <TRANSACTION XID="01001E00E9020000">
    
31.                         <CHARACTERISTICS>
    
32.                         </CHARACTERISTICS>
    
33.                         <UNDO_SQL>
    
34.                                 <USQL exec="yes">
    
35.                                  update "HR"."T_CASCADE" set "ID" = '222' where "ID" = '22' and ROWID = 'AAATGVAAEAAAAI9AAB'
    
36.                                 </USQL>
    
37.                         </UNDO_SQL>
    
38.                         <DEPENDENT_XIDS>
    
39.                         </DEPENDENT_XIDS>
    
40.                         </TRANSACTION>
    
41.                 </DEPENDENT_XIDS>
    
42.                 </TRANSACTION>
    
43.         </DEPENDENT_XIDS>
    
44.         </TRANSACTION>
    
45. <EXECUTED_UNDO_SQL>
    
46. <EXEC_USQL>update "HR"."T_CASCADE" set "ID" = '222' where "ID" = '22' and ROWID = 'AAATGVAAEAAAAI9AAB'
    
47. </EXEC_USQL>
    
48. <EXEC_USQL>update "HR"."T_CASCADE" set "ID" = '33' where "ID" = '333' and ROWID = 'AAATGVAAEAAAAI9AAC'
    
49. </EXEC_USQL>
    
50. <EXEC_USQL>update "HR"."T_CASCADE" set "ID" = '22' where "ID" = '222' and ROWID = 'AAATGVAAEAAAAI9AAB'
    
51. </EXEC_USQL>
    
52. <EXEC_USQL>update "HR"."T_CASCADE" set "ID" = '3' where "ID" = '33' and ROWID = 'AAATGVAAEAAAAI9AAC'
    
53. </EXEC_USQL>
    
54. <EXEC_USQL>update "HR"."T_CASCADE" set "ID" = '2' where "ID" = '22' and ROWID = 'AAATGVAAEAAAAI9AAB'
    
55. </EXEC_USQL>
    
56. <EXEC_USQL>update "HR"."T_CASCADE" set "ID" = '1' where "ID" = '11' and ROWID = 'AAATGVAAEAAAAI9AAA'
    
57. </EXEC_USQL>
    
58. </EXECUTED_UNDO_SQL>
    
59. </COMP_XID_REPORT>

复制代码

1. SQL> select * from t_cascade;
   
2. 
   
3.         ID
   
4. ----------
   
5.          1
   
6.          2
   
7.          3
   

复制代码

----------------------------------------------------

T_NONCONFLICT_ONLY:

1. select * from flashback_transaction_query ftq
   
2. where ftq.logon_user='HR' and ftq.table_name='T_NONCONFLICT_ONLY'
   
3. order by commit_Scn;
   
4. 
   
5. begin
   
6. dbms_flashback.transaction_backout(numtxns => 1,
   
7. xids => xid_array('070015002A030000'),
   
8. options => dbms_flashback.nonconflict_only);
   
9. end;
   
10. 
    
11. select * from dba_flashback_txn_report;
    
12. 
    
13. commit;

复制代码

1. <?xml version="1.0" encoding="ISO-8859-1"?>
   
2. <COMP_XID_REPORT XID="0800040094040000" NAME="_SYS_COMP_TXN_458772_TIM_1528234613">
   
3.         <TRANSACTION XID="070015002A030000">
   
4.         <CHARACTERISTICS>
   
5.         </CHARACTERISTICS>
   
6.         <UNDO_SQL>
   
7.                 <USQL exec="no">
   
8.                  update "HR"."T_NONCONFLICT_ONLY" set "ID" = '3' where "ID" = '33' and ROWID = 'AAATGWAAEAAAAJFAAC'
   
9.                 </USQL>
   
10.                 <USQL exec="no">
    
11.                  update "HR"."T_NONCONFLICT_ONLY" set "ID" = '2' where "ID" = '22' and ROWID = 'AAATGWAAEAAAAJFAAB'
    
12.                 </USQL>
    
13.                 <USQL exec="yes">
    
14.                  update "HR"."T_NONCONFLICT_ONLY" set "ID" = '1' where "ID" = '11' and ROWID = 'AAATGWAAEAAAAJFAAA'
    
15.                 </USQL>
    
16.         </UNDO_SQL>
    
17.         <DEPENDENT_XIDS>
    
18.                 <TRANSACTION XID="0800030094040000">
    
19.                 <CHARACTERISTICS>
    
20.                 </CHARACTERISTICS>
    
21.                 <UNDO_SQL>
    
22.                         <USQL exec="no">
    
23.                          update "HR"."T_NONCONFLICT_ONLY" set "ID" = '33' where "ID" = '333' and ROWID = 'AAATGWAAEAAAAJFAAC'
    
24.                         </USQL>
    
25.                         <USQL exec="no">
    
26.                          update "HR"."T_NONCONFLICT_ONLY" set "ID" = '22' where "ID" = '222' and ROWID = 'AAATGWAAEAAAAJFAAB'
    
27.                         </USQL>
    
28.                 </UNDO_SQL>
    
29.                 <DEPENDENT_XIDS>
    
30.                         <TRANSACTION XID="03001000D9030000">
    
31.                         <CHARACTERISTICS>
    
32.                         </CHARACTERISTICS>
    
33.                         <UNDO_SQL>
    
34.                                 <USQL exec="no">
    
35.                                  update "HR"."T_NONCONFLICT_ONLY" set "ID" = '222' where "ID" = '22' and ROWID = 'AAATGWAAEAAAAJFAAB'
    
36.                                 </USQL>
    
37.                         </UNDO_SQL>
    
38.                         <DEPENDENT_XIDS>
    
39.                         </DEPENDENT_XIDS>
    
40.                         </TRANSACTION>
    
41.                 </DEPENDENT_XIDS>
    
42.                 </TRANSACTION>
    
43.         </DEPENDENT_XIDS>
    
44.         </TRANSACTION>
    
45. <EXECUTED_UNDO_SQL>
    
46. <EXEC_USQL>update "HR"."T_NONCONFLICT_ONLY" set "ID" = '1' where "ID" = '11' and ROWID = 'AAATGWAAEAAAAJFAAA'
    
47. </EXEC_USQL>
    
48. </EXECUTED_UNDO_SQL>
    
49. </COMP_XID_REPORT>

复制代码

1. SQL> select  * from t_nonconflict_only;
   
2. 
   
3.         ID
   
4. ----------
   
5.          1
   
6.         22
   
7.        333
   

复制代码

----------------------------------------------
t_nocascade_force：

1. select * from flashback_transaction_query ftq
   
2. where ftq.logon_user='HR' and ftq.table_name='T_NOCASCADE_FORCE'
   
3. order by commit_Scn;
   
4. 
   
5. begin
   
6. dbms_flashback.transaction_backout(numtxns => 1,
   
7. xids => xid_array('09000800E2030000'),
   
8. options => dbms_flashback.nocascade_force);
   
9. end;
   
10. 
    
11. select * from dba_flashback_txn_report;
    
12. 
    
13. commit;

复制代码

1. <?xml version="1.0" encoding="ISO-8859-1"?>
   
2. <COMP_XID_REPORT XID="02001500AF030000" NAME="_SYS_COMP_TXN_458772_TIM_1528235102">
   
3.         <TRANSACTION XID="09000800E2030000">
   
4.         <CHARACTERISTICS>
   
5.         </CHARACTERISTICS>
   
6.         <UNDO_SQL>
   
7.                 <USQL exec="yes">
   
8.                  update "HR"."T_NOCASCADE_FORCE" set "ID" = '3' where "ID" = '33' and ROWID = 'AAATGXAAEAAAAJNAAC'
   
9.                 </USQL>
   
10.                 <USQL exec="yes">
    
11.                  update "HR"."T_NOCASCADE_FORCE" set "ID" = '2' where "ID" = '22' and ROWID = 'AAATGXAAEAAAAJNAAB'
    
12.                 </USQL>
    
13.                 <USQL exec="yes">
    
14.                  update "HR"."T_NOCASCADE_FORCE" set "ID" = '1' where "ID" = '11' and ROWID = 'AAATGXAAEAAAAJNAAA'
    
15.                 </USQL>
    
16.         </UNDO_SQL>
    
17.         <DEPENDENT_XIDS>
    
18.                 <TRANSACTION XID="0400150025030000">
    
19.                 <CHARACTERISTICS>
    
20.                 </CHARACTERISTICS>
    
21.                 <UNDO_SQL>
    
22.                         <USQL exec="no">
    
23.                          update "HR"."T_NOCASCADE_FORCE" set "ID" = '33' where "ID" = '333' and ROWID = 'AAATGXAAEAAAAJNAAC'
    
24.                         </USQL>
    
25.                         <USQL exec="no">
    
26.                          update "HR"."T_NOCASCADE_FORCE" set "ID" = '22' where "ID" = '222' and ROWID = 'AAATGXAAEAAAAJNAAB'
    
27.                         </USQL>
    
28.                 </UNDO_SQL>
    
29.                 <DEPENDENT_XIDS>
    
30.                         <TRANSACTION XID="03001A00DF030000">
    
31.                         <CHARACTERISTICS>
    
32.                         </CHARACTERISTICS>
    
33.                         <UNDO_SQL>
    
34.                                 <USQL exec="no">
    
35.                                  update "HR"."T_NOCASCADE_FORCE" set "ID" = '222' where "ID" = '22' and ROWID = 'AAATGXAAEAAAAJNAAB'
    
36.                                 </USQL>
    
37.                         </UNDO_SQL>
    
38.                         <DEPENDENT_XIDS>
    
39.                         </DEPENDENT_XIDS>
    
40.                         </TRANSACTION>
    
41.                 </DEPENDENT_XIDS>
    
42.                 </TRANSACTION>
    
43.         </DEPENDENT_XIDS>
    
44.         </TRANSACTION>
    
45. <EXECUTED_UNDO_SQL>
    
46. <EXEC_USQL>update "HR"."T_NOCASCADE_FORCE" set "ID" = '3' where "ID" = '33' and ROWID = 'AAATGXAAEAAAAJNAAC'
    
47. </EXEC_USQL>
    
48. <EXEC_USQL>update "HR"."T_NOCASCADE_FORCE" set "ID" = '2' where "ID" = '22' and ROWID = 'AAATGXAAEAAAAJNAAB'
    
49. </EXEC_USQL>
    
50. <EXEC_USQL>update "HR"."T_NOCASCADE_FORCE" set "ID" = '1' where "ID" = '11' and ROWID = 'AAATGXAAEAAAAJNAAA'
    
51. </EXEC_USQL>
    
52. </EXECUTED_UNDO_SQL>
    
53. </COMP_XID_REPORT>

复制代码

1. SQL> select * from t_nocascade_force ;
   
2. 
   
3.         ID
   
4. ----------
   
5.          1
   
6.          2
   
7.        333
   

复制代码

chenmd

唐老师单点闪回不指定条件不行吗。原先这个是24000的，我改成25000，然后闪回。他并没有变回24000

chenmd

我大概知道，毕竟单点

botang

chenmd 发表于 2018-6-7 11:08
我大概知道，毕竟单点

闪回的时间为1天，可能没有UNDO支持。

chenmd

唐老师，关于闪回技术3映射表空间的语句是啥，没有映射无法进行下一步操作

botang

chenmd 发表于 2018-6-9 10:20
唐老师，关于闪回技术3映射表空间的语句是啥，没有映射无法进行下一步操作

select versions_xid, versions_startscn, versions_operation, salary from employees
  2  versions between scn minvalue and maxvalue
  3  where employee_id=100;

哪里需要表空间？

chenmd

botang 发表于 2018-6-9 16:06
select versions_xid, versions_startscn, versions_operation, salary from employees
  2  versions b ...

这条命令我无法执行