课程第25次

secoug

Notice: This blog is written by Bo Tang.

闪回1：闪回事务查询

1. SQL>  select salary from employees where employee_id=100;
   
2. 
   
3.     SALARY
   
4. ----------
   
5.      30002
   
6. 
   
7. SQL> update employees  set salary=40000 where employee_id=100;
   
8. 
   
9. 1 row updated.
   
10. 
    
11. SQL> commit;
    
12. 
    
13. Commit complete.
    
14. 
    
15. SQL>

复制代码

1. select * from v$transaction;
   
2. 
   
3. select   * from flashback_transaction_query ftq
   
4. where ftq.logon_user='HR' and ftq.table_name='EMPLOYEES';

复制代码

XID

START_SCN

START_TIMESTAMP

COMMIT_SCN

COMMIT_TIMESTAMP

LOGON_USER

UNDO_CHANGE#

OPERATION

TABLE_NAME

TABLE_OWNER

ROW_ID

UNDO_SQL

1

0F001100AD020000

0

2019-10-23 12:00:34

4936055

2019-10-23 12:00:10

HR

2

UNKNOWN

EMPLOYEES

2

0F001100AD020000

0

2019-10-23 12:00:34

4936055

2019-10-23 12:00:10

HR

3

UNKNOWN

EMPLOYEES

t05310.zip (295 Bytes, 下载次数: 220)

2019-11-25 20:20 上传

点击文件名下载附件

1. SQL> @t05310
   
2. Enter value for s1: t_cascade
   
3. old   1: create table &&s1( id number )
   
4. new   1: create table t_cascade( id number )
   
5. 
   
6. Table created.
   
7. 
   
8. 
   
9. old   1: insert into &&s1 values (1)
   
10. new   1: insert into t_cascade values (1)
    
11. 
    
12. 1 row created.
    
13. 
    
14. old   1: insert into &&s1 values (2)
    
15. new   1: insert into t_cascade values (2)
    
16. 
    
17. 1 row created.
    
18. 
    
19. old   1: insert into &&s1 values (3)
    
20. new   1: insert into t_cascade values (3)
    
21. 
    
22. 1 row created.
    
23. 
    
24. 
    
25. Commit complete.
    
26. 
    
27. old   1: update &&s1 set id=11 where id=1
    
28. new   1: update t_cascade set id=11 where id=1
    
29. 
    
30. 1 row updated.
    
31. 
    
32. old   1: update &&s1 set id=22 where id=2
    
33. new   1: update t_cascade set id=22 where id=2
    
34. 
    
35. 1 row updated.
    
36. 
    
37. old   1: update &&s1 set id=33 where id=3
    
38. new   1: update t_cascade set id=33 where id=3
    
39. 
    
40. 1 row updated.
    
41. 
    
42. 
    
43. Commit complete.
    
44. 
    
45. old   1: update &&s1 set id=222 where id=22
    
46. new   1: update t_cascade set id=222 where id=22
    
47. 
    
48. 1 row updated.
    
49. 
    
50. old   1: update &&s1 set id=333 where id=33
    
51. new   1: update t_cascade set id=333 where id=33
    
52. 
    
53. 1 row updated.
    
54. 
    
55. 
    
56. Commit complete.
    
57. 
    
58. old   1: update &&s1 set id=22 where id=222
    
59. new   1: update t_cascade set id=22 where id=222
    
60. 
    
61. 1 row updated.
    
62. 
    
63. 
    
64. Commit complete.
    
65. 
    
66. SQL>

复制代码

1. SQL> conn hr/oracle_4U
   
2. Connected.
   
3. SQL> create table t05310_b ( a number ) ;
   
4. 
   
5. Table created.
   
6. 
   
7. SQL>

复制代码

1. 
   
2. 
   
3. select   * from flashback_transaction_query ftq
   
4. where ftq.logon_user='HR' and ftq.table_name='T_CASCADE'  order by 2;
   
5. 
   
6. 
   
7. select  * from dba_objects o where o.owner='HR' and o.object_name='T05310_B';
   
8. 
   
9. 
   
10. alter database add supplemental log data;
    
11. 
    
12. select  d.SUPPLEMENTAL_LOG_DATA_MIN  from v_$database  d ;
    
13. 
    
14. select  * from tab$ where ROWID = 'AAAAACAABAAAXAkAAD';
    
15. 
    
16.   select   * from flashback_transaction_query ftq
    
17. where  ftq.logon_user='HR' and    ftq.table_name='TAB'
    
18.   order by 2;
    
19. 
    
20. 
    

复制代码

图形表示：





--------------------------------------------------------------------------
闪回3：

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

复制代码

1. VERSIONS_XID     VERSIONS_STARTSCN V     SALARY
   
2. ---------------- ----------------- - ----------
   
3. 20001B003F000000           4943834 U      60000
   
4. 14000000A8020000           4943826 U      50000
   
5.                                           40000
   
6. 
   
7. SQL> select salary from employees where employee_id=100;
   
8. 
   
9.     SALARY
   
10. ----------
    
11.      40000
    
12. 
    
13. SQL> flashback table employees to scn 4943826;
    
14. 
    
15. Flashback complete.
    
16. 
    
17. SQL>  select salary from employees where employee_id=100;
    
18. 
    
19.     SALARY
    
20. ----------
    
21.      50000
    
22. 
    
23. SQL>

复制代码

1. Wrote file afiedt.buf
   
2. 
   
3.   1   select  versions_xid ,  versions_startscn , versions_operation , salary  from employees
   
4.   2  versions between  scn minvalue and maxvalue
   
5.   3* where employee_id=100
   
6. SQL> /
   
7. 
   
8. VERSIONS_XID     VERSIONS_STARTSCN V     SALARY
   
9. ---------------- ----------------- - ----------
   
10. 20001B003F000000           4943834 U      60000
    
11. 14000000A8020000           4943826 U      50000
    
12.                                           40000
    

复制代码

闪回4：

1. SQL> /
   
2. 
   
3. VERSIONS_XID     VERSIONS_STARTSCN V     SALARY
   
4. ---------------- ----------------- - ----------
   
5. 20001B003F000000           4943834 U      60000
   
6. 14000000A8020000           4943826 U      50000
   
7.                                           40000
   
8. 
   
9. SQL> select salary from employees where employee_id=100;
   
10. 
    
11.     SALARY
    
12. ----------
    
13.      40000
    
14. 
    
15. SQL> flashback table employees to scn 4943826;
    
16. 
    
17. Flashback complete.
    
18. 
    
19. SQL>  select salary from employees where employee_id=100;
    
20. 
    
21.     SALARY
    
22. ----------
    
23.      50000
    
24. 
    
25. SQL>

复制代码

闪回8：

1. alter database add supplemental log data ( primary key ) columns ;

复制代码