1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
| SELECT xid, logon_user
FROM flashback_transaction_query
WHERE xid IN
(SELECT versions_xid
FROM employees VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2003-07-18 14:00:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_TIMESTAMP('2003-07-18 17:00:00', 'YYYY-MM-DD HH24:MI:SS'));
SQL> select * from t;
ID
----------
1
2
SQL> insert into t values (3);
1 row created.
SQL> commit;
Commit complete.
SQL> insert into t values (4);
1 row created.
SQL> insert into t values (5);
1 row created.
SQL> insert into t values (6);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t;
ID
----------
1
2
3
4
5
6
6 rows selected.
col table_name for a10
col logon_user for a10
set linesize 200 pagesize 200
col operation for a10
col undo_sql for a60
alter session set nls_date_format='yyyymmdd hh24:mi:ss';
SELECT xid, logon_user,start_timestamp,operation,table_name,undo_sql
FROM flashback_transaction_query
WHERE xid IN
(SELECT versions_xid
FROM t VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2023-05-09 16:20:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_TIMESTAMP('2023-05-09 16:24:00', 'YYYY-MM-DD HH24:MI:SS'));
XID LOGON_USER START_TIMESTAMP OPERATION TABLE_NAME UNDO_SQL
---------------- ---------- ----------------- ---------- ---------- ------------------------------------------------------------
05000C00EB060000 ADMIN 20230509 16:22:54 INSERT T delete from "ADMIN"."T" where ROWID = 'AAAVhXAAEAAAAC7AAF';
05000C00EB060000 ADMIN 20230509 16:22:54 INSERT T delete from "ADMIN"."T" where ROWID = 'AAAVhXAAEAAAAC7AAE';
05000C00EB060000 ADMIN 20230509 16:22:54 INSERT T delete from "ADMIN"."T" where ROWID = 'AAAVhXAAEAAAAC7AAD';
05000C00EB060000 ADMIN 20230509 16:22:54 BEGIN
09001D0028070000 ADMIN 20230509 16:22:42 INSERT T delete from "ADMIN"."T" where ROWID = 'AAAVhXAAEAAAAC7AAC';
09001D0028070000 ADMIN 20230509 16:22:42 BEGIN
6 rows selected.
SELECT xid, operation, start_scn, commit_scn, logon_user, undo_sql
FROM flashback_transaction_query
WHERE xid = HEXTORAW('05000C00EB060000');
XID OPERATION START_SCN COMMIT_SCN LOGON_USER UNDO_SQL
---------------- ---------- ---------- ---------- ---------- ------------------------------------------------------------
05000C00EB060000 INSERT 3216021 3216026 ADMIN delete from "ADMIN"."T" where ROWID = 'AAAVhXAAEAAAAC7AAF';
05000C00EB060000 INSERT 3216021 3216026 ADMIN delete from "ADMIN"."T" where ROWID = 'AAAVhXAAEAAAAC7AAE';
05000C00EB060000 INSERT 3216021 3216026 ADMIN delete from "ADMIN"."T" where ROWID = 'AAAVhXAAEAAAAC7AAD';
05000C00EB060000 BEGIN 3216021 3216026 ADMIN
SQL> select * from t where ROWID = 'AAAVhXAAEAAAAC7AAD';
ID
----------
4
SQL> select * from t where ROWID = 'AAAVhXAAEAAAAC7AAE';
ID
----------
5
SQL> select * from t where ROWID = 'AAAVhXAAEAAAAC7AAF';
ID
----------
6
Using Oracle Flashback Transaction Query with Oracle Flashback Version Query:
CREATE TABLE emp (
empno NUMBER PRIMARY KEY,
empname VARCHAR2(16),
salary NUMBER
);
INSERT INTO emp (empno, empname, salary) VALUES (111, 'Mike', 555);
COMMIT;
UPDATE emp SET salary = salary + 100 WHERE empno = 111;
INSERT INTO dept (deptno, deptname) VALUES (20, 'Finance');
DELETE FROM emp WHERE empno = 111;
COMMIT;
INSERT INTO emp (empno, empname, salary) VALUES (111, 'Tom', 777);
UPDATE emp SET salary = salary + 100 WHERE empno = 111;
UPDATE emp SET salary = salary + 50 WHERE empno = 111;
COMMIT;
SELECT versions_xid XID, versions_startscn START_SCN,
versions_endscn END_SCN, versions_operation OPERATION,
empname, salary
FROM emp
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
WHERE empno = 111;
XID START_SCN END_SCN OPERATION EMPNAME SALARY
---------------- ---------- ---------- ---------- ---------------- ----------
0A001000A8090000 3216661 I Tom 927
07000A001C060000 3216655 D Mike 555
070007001C060000 3216619 3216655 I Mike 555
SELECT xid, start_scn, commit_scn, operation, logon_user, undo_sql
FROM flashback_transaction_query
WHERE xid = HEXTORAW('07000A001C060000');
XID START_SCN COMMIT_SCN OPERATION LOGON_USER UNDO_SQL
---------------- ---------- ---------- ---------- ---------- ------------------------------------------------------------
07000A001C060000 3216654 3216655 DELETE ADMIN insert into "ADMIN"."EMP"("EMPNO","EMPNAME","SALARY") values
('111','Mike','655');
07000A001C060000 3216654 3216655 INSERT ADMIN delete from "ADMIN"."DEPT" where ROWID = 'AAAVhaAAEAAAADjAAB
';
07000A001C060000 3216654 3216655 UPDATE ADMIN update "ADMIN"."EMP" set "SALARY" = '555' where ROWID = 'AAA
VhYAAEAAAADTAAA';
07000A001C060000 3216654 3216655 BEGIN ADMIN
ROWID注释:
ROWID = 'AAAVhXAAEAAAAC7AAF';
rowid是oracle中标识一条记录的唯一标识，是一个64进制的数值,总共有18位组成(9i及9i以后，8i的是16位)，也就是6(object_id) + 3(relative_fno ) + 6(block_number) + 3(row_number)这些组成。
64进制基数:
A-Z(0-25)，a-z(26-51)，0-9(52-61)，+(62)，/(63)
Oracle包dbms_rowid中提供了使用rowid来查询其信息的函数，如下所示：
select
rowid,
dbms_rowid.rowid_object(rowid) object_id, --51366 (AAAMim)AAFAAAAAMAAC 数据对象号
dbms_rowid.rowid_relative_fno(rowid) file_id, --5 AAAMim(AAF)AAAAAMAAC 相对文件号
dbms_rowid.rowid_block_number(rowid) block_id, --12 AAAMimAAF(AAAAAM)AAC 在第几个块
dbms_rowid.rowid_row_number(rowid) num --2 AAAMimAAFAAAAAM(AAC)在block中的行数
from t where rowid = 'AAAVhXAAEAAAAC7AAF';
ROWID OBJECT_ID FILE_ID BLOCK_ID NUM
------------------ ---------- ---------- ---------- ----------
AAAVhXAAEAAAAC7AAF 88151 4 187 5
下面这个函数对上面的函数改造后可以一次性返回这些信息:
Create Or Replace Function Rowid_To_Info(Str In Varchar2) Return Varchar2 As
Digit Varchar2(32);
Rest Varchar2(400);
Str_1 Varchar2(10) := Substr(Str, 1, 6);
Str_2 Varchar2(10) := Substr(Str, 7, 3);
Str_3 Varchar2(10) := Substr(Str, 10, 6);
Str_4 Varchar2(10) := Substr(Str, 16, 3);
Type Str_Varray Is Varray(4) Of Varchar2(10);
Type Rest_Varray Is Varray(4) Of Varchar2(20);
v_Str_Varray Str_Varray := Str_Varray(Str_1, Str_2, Str_3, Str_4);
v_Rest_Varray Rest_Varray := Rest_Varray('Object_id：','File_id：','Block_id：','Block_num：');
Begin
For i In 1 .. 4 Loop
Select Sum(Data)
Into Digit
From (Select (Case
When Ascii(Substr(v_Str_Varray(i), Rownum, 1)) = 43 Then
62
When Ascii(Substr(v_Str_Varray(i), Rownum, 1)) = 47 Then
63
When Ascii(Substr(v_Str_Varray(i), Rownum, 1)) Between 48 And 57 Then
Ascii(Substr(v_Str_Varray(i), Rownum, 1)) + 4
When Ascii(Substr(v_Str_Varray(i), Rownum, 1)) Between 65 And 90 Then
Ascii(Substr(v_Str_Varray(i), Rownum, 1)) - 65
When Ascii(Substr(v_Str_Varray(i), Rownum, 1)) Between 97 And 122 Then
Ascii(Substr(v_Str_Varray(i), Rownum, 1)) - 71
Else
Null
End) * Power(64, Length(v_Str_Varray(i)) - Rownum) Data
From Dual
Connect By Rownum <= Length(v_Str_Varray(i)));
If i = 4 Then
Rest := Rest || v_Rest_Varray(i) || Digit;
Else
Rest := Rest || v_Rest_Varray(i) || Digit || chr(9);
End If;
End Loop;
Return Rest;
End;
/
SQL> select rowid_to_info('AAAVhXAAEAAAAC7AAF')from dual;
|
