课程第19次

botang

1. SQL> select  uvalue from t04209_uname where uname='g1';
   
2. 
   
3.     UVALUE
   
4. ----------
   
5.          1
   
6. 
   
7. SQL> update t04209_uname  set uvalue=2 where uname='g1';
   
8. 
   
9. 1 row updated.
   
10. 
    
11. SQL> commit;
    
12. 
    
13. Commit complete.
    
14. 
    
15. SQL>  update t04209_uname  set uvalue=3 where uname='g1';
    
16. 
    
17. 1 row updated.
    
18. 
    
19. SQL> commit;
    
20. 
    
21. Commit complete.
    
22. 
    
23. SQL>  update t04209_uname  set uvalue=4 where uname='g1';
    
24. 
    
25. 1 row updated.
    
26. 
    
27. SQL> commit;
    
28. 
    
29. Commit complete.
    
30. 
    
31. SQL> update t04209_uname  set uvalue=5 where uname='g1';
    
32. 
    
33. 1 row updated.
    
34. 
    
35. SQL> commit;
    
36. 
    
37. Commit complete.
    
38. 
    
39. SQL>
    

复制代码

1. select * from dba_rollback_segs;
   
2. 
   
3. select  * from v$transaction;
   
4. --1000190010000000
   
5. 
   
6. select  * from flashback_transaction_query ftq
   
7. where ftq.xid='1000190010000000';
   
8. 
   
9. select  * from flashback_transaction_query ftq
   
10. where ftq.logon_user='HR' and ftq.table_name='T04209_UNAME';
    
11. 
    
12. select  count(*) from flashback_transaction_query;

复制代码

XID

START_SCN

START_TIMESTAMP

COMMIT_SCN

COMMIT_TIMESTAMP

LOGON_USER

UNDO_CHANGE#

OPERATION

TABLE_NAME

TABLE_OWNER

ROW_ID

UNDO_SQL

1

0C00160014000000

1165592

2019/6/22 9:03:27

1166044

2019/6/22 9:12:07

HR

1

UNKNOWN

T04209_UNAME

2

1000190010000000

0

2019/6/22 9:11:08

1165965

2019/6/22 9:10:19

HR

1

UNKNOWN

T04209_UNAME

3

1100120016000000

1166057

2019/6/22 9:12:37

1166070

2019/6/22 9:12:52

HR

1

UNKNOWN

T04209_UNAME

4

1400120015000000

1166044

2019/6/22 9:12:07

1166057

2019/6/22 9:12:37

HR

1

UNKNOWN

T04209_UNAME

1. select
   
2.   versions_xid,
   
3.   versions_operation,
   
4.   versions_startscn,
   
5.   to_char(versions_starttime, 'YYYY-MM-DD:HH24:MI:SS'),
   
6.   uvalue
   
7.   from hr.t04209_uname
   
8.   versions between scn minvalue and maxvalue
   
9.   where uname='g1';

复制代码

	VERSIONS_XID	VERSIONS_OPERATION	VERSIONS_STARTSCN	TO_CHAR(VERSIONS_STARTTIME,'YY	UVALUE
1	1100120016000000	U	1166070	2019-06-22:09:12:52	5
2	1400120015000000	U	1166057	2019-06-22:09:12:37	4
3	0C00160014000000	U	1166044	2019-06-22:09:12:07	3
4	1000190010000000	U	1165965	2019-06-22:09:10:19	2
5					1

闪回表的时候，除了值要对，位置也要对，所以需要行移动（因为有可能，现在要闪回的位置被别人占领了！）

1. SQL> create table t05310_c  ( a  number )   ;
   
2. 
   
3. Table created.
   
4. 
   
5. SQL> insert into t05310_c values (1) ;
   
6. 
   
7. 1 row created.
   
8. 
   
9. SQL> commit;
   
10. 
    
11. Commit complete.
    
12. 
    
13. SQL> update t05310_c set a=2 ;
    
14. 
    
15. 1 row updated.
    
16. 
    
17. SQL> commit;
    
18. 
    
19. Commit complete.
    
20. 
    
21. SQL> alter table t05310_c add ( b varchar2(20))  ;
    
22. 
    
23. Table altered.
    
24. 
    
25. SQL> select  * from t05310_c ;
    
26. 
    
27.          A B
    
28. ---------- --------------------
    
29.          2
    
30. 
    
31. SQL>  select  * from t05310_c ;
    
32. 
    
33.          A B
    
34. ---------- --------------------
    
35.          1
    
36. 
    
37. SQL>
    

复制代码

闪回表操作，本身是删除+插入的组合，如果DDL有增加字段，闪回到过去，会拖着新加的字段显示。

1. select versions_xid, versions_startscn , versions_operation, a from hr.t05310_c
   
2. versions between scn minvalue and maxvalue
   
3. order by versions_startscn
   
4.     ;
   

复制代码

	VERSIONS_XID	VERSIONS_STARTSCN	VERSIONS_OPERATION	A
1	12000F001E000000	1172963	I	1
2	110016001E000000	1172979	U	2
3	110020001F000000	1173527	D	2
4	110020001F000000	1173527	I	1

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
如何让闪回事务查询显示无遮挡的信息（operation/table_owner/rowid/undo_sql），可以考虑打开补充日志。

1. select d.SUPPLEMENTAL_LOG_DATA_MIN,
   
2.        d.SUPPLEMENTAL_LOG_DATA_PK,
   
3.        d.SUPPLEMENTAL_LOG_DATA_UI,
   
4.        d.SUPPLEMENTAL_LOG_DATA_FK,
   
5.        d.SUPPLEMENTAL_LOG_DATA_ALL
   
6. from v_$database d;
   
7. 
   
8. alter database add supplemental log data;

复制代码

jenny

谢谢老师的总结，细心的老师，非常棒！