技术分享 | 独立思考

0%

oracle闪回练习

发表于 更新于 分类于 ，
本文字数： 2.8k 阅读时长 ≈ 3 分钟

配置数据库使用自动撤销管理(Automatic Undo Management)

1
2
alter system set undo_retention=86400;
show parameter undo;

1

闪回查询

1
2
3
select * from emp
update emp set sal=sal*1.5;
commit;

启用补充日志记录

1
alter database add supplemental log data;

闪回查询(SELECT AS OF)

merge

1
2
3
4
5
6
7
MERGE INTO emp t1
USING ( SELECT * FROM emp AS OF TIMESTAMP sysdate - 1 / 24) t2 
ON ( t1.empno = t2.empno )
WHEN MATCHED THEN 
UPDATE SET t1.sal = t2.sal;

COMMIT;

for

1
2
3
4
5
6
7
begin
for rec in (select * from emp as of TIMESTAMP sysdate -60/24/60) loop
    update emp set sal=rec.sal where empno=rec.empno;
end loop;
commit;

end;

游标

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
declare
cursor cur_emp is SELECT * FROM emp AS OF TIMESTAMP sysdate - 1 / 24  t1;
type tab_emp is table of cur_emp%rowtype;
v_emp tab_emp;
-----------------
begin
open cur_emp;
-----------------
loop
fetch cur_emp bulk collect into v_emp limit 4;
exit when v_emp.count = 0; 
    for i in v_emp.first .. v_emp.last 
        loop
            update emp set sal=v_emp(i).sal where empno=v_emp(i).empno;
        end loop;
end loop;
close cur_emp; 
end;

闪回版本查询(Oracle Flashback Version Query)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
 
create table emp_new as select * from emp;
select * from emp_new where empno=7369;
update emp_new set sal=3000 where empno=7369;
commit;
update emp_new set sal=4000 where empno=7369; 
commit;
delete emp_new where empno=7369; commit;
commit;
 
 
select 
versions_startscn, versions_starttime+0 versions_starttime, versions_endscn,
versions_endtime+0 versions_endtime, versions_xid,
versions_operation,empno,ename,sal
from 
emp_new versions between timestamp sysdate - 15/24/60 and sysdate 
where empno = 7369
order by versions_starttime;

2

闪回事务查询

1
2
3
4
5
select 
    *
from flashback_transaction_query t
where t.xid in
('060002003A080000', '0A000A0080050000', '08001F000F070000');

闪回表

1
2
3
4
5
6
select * from emp;
update emp set sal=5000;
select * from emp as of timestamp sysdate-10/24; 
flashback table emp to timestamp sysdate-10/24; 
alter table emp enable row movement;
flashback table emp to scn 1918658;

闪回drop

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
SQL> show parameter recyclebin
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------ recyclebin string on
SQL>
SQL> conn scott/scott@orcl
Connected.
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- ------------------------------ ------------ ------------------- EMP_TEST1 BIN$plx+t07gDRTgU2Y4qMBi7g==$0 TABLE 2020-05-
24:10:19:41
select * from user_recyclebin;
SQL> purge recyclebin;
Recyclebin purged.
SQL> select count(1) from emp_new; SQL> drop table emp_new;
Table dropped.
SQL> select count(1) from emp_new; select count(1) from emp_new
* ERROR at line 1:
ORA-00942: table or view does not exist
SQL> flashback table emp_new to before drop;
Flashback complete.
SQL> select count(1) from emp_new;
COUNT(1) ----------
14
SQL> drop table emp_new;
SQL> create table emp_new01 as select * from dept;
SQL> drop table emp_new01;
SQL> show recyclebin
SQL> select * from "BIN$pum8ERSSDxPgU2Y4qMDIoQ==$0"; SQL> desc "BIN$pum8ERSRDxPgU2Y4qMDIoQ==$0"