课程第25次
Notice: This blog is written by Bo Tang.闪回1：闪回事务查询
SQL>select salary from employees where employee_id=100;
SALARY
----------
30002
SQL> update employeesset salary=40000 where employee_id=100;
1 row updated.
SQL> commit;
Commit complete.
SQL>
select * from v$transaction;
select * from flashback_transaction_query ftq
where ftq.logon_user='HR' and ftq.table_name='EMPLOYEES';
XIDSTART_SCNSTART_TIMESTAMPCOMMIT_SCNCOMMIT_TIMESTAMPLOGON_USERUNDO_CHANGE#OPERATIONTABLE_NAMETABLE_OWNERROW_IDUNDO_SQL
10F001100AD02000002019-10-23 12:00:3449360552019-10-23 12:00:10HR2UNKNOWNEMPLOYEES
20F001100AD02000002019-10-23 12:00:3449360552019-10-23 12:00:10HR3UNKNOWNEMPLOYEES
SQL> @t05310
Enter value for s1: t_cascade
old 1: create table &&s1( id number )
new 1: create table t_cascade( id number )
Table created.
old 1: insert into &&s1 values (1)
new 1: insert into t_cascade values (1)
1 row created.
old 1: insert into &&s1 values (2)
new 1: insert into t_cascade values (2)
1 row created.
old 1: insert into &&s1 values (3)
new 1: insert into t_cascade values (3)
1 row created.
Commit complete.
old 1: update &&s1 set id=11 where id=1
new 1: update t_cascade set id=11 where id=1
1 row updated.
old 1: update &&s1 set id=22 where id=2
new 1: update t_cascade set id=22 where id=2
1 row updated.
old 1: update &&s1 set id=33 where id=3
new 1: update t_cascade set id=33 where id=3
1 row updated.
Commit complete.
old 1: update &&s1 set id=222 where id=22
new 1: update t_cascade set id=222 where id=22
1 row updated.
old 1: update &&s1 set id=333 where id=33
new 1: update t_cascade set id=333 where id=33
1 row updated.
Commit complete.
old 1: update &&s1 set id=22 where id=222
new 1: update t_cascade set id=22 where id=222
1 row updated.
Commit complete.
SQL>
SQL> conn hr/oracle_4U
Connected.
SQL> create table t05310_b ( a number ) ;
Table created.
SQL>
select * from flashback_transaction_query ftq
where ftq.logon_user='HR' and ftq.table_name='T_CASCADE'order by 2;
select* from dba_objects o where o.owner='HR' and o.object_name='T05310_B';
alter database add supplemental log data;
selectd.SUPPLEMENTAL_LOG_DATA_MINfrom v_$databased ;
select* from tab$ where ROWID = 'AAAAACAABAAAXAkAAD';
select * from flashback_transaction_query ftq
whereftq.logon_user='HR' and ftq.table_name='TAB'
order by 2;
图形表示：
--------------------------------------------------------------------------
闪回3：
selectversions_xid ,versions_startscn , versions_operation , salaryfrom employees
versions betweenscn minvalue and maxvalue
where employee_id=100
VERSIONS_XID VERSIONS_STARTSCN V SALARY
---------------- ----------------- - ----------
20001B003F000000 4943834 U 60000
14000000A8020000 4943826 U 50000
40000
SQL> select salary from employees where employee_id=100;
SALARY
----------
40000
SQL> flashback table employees to scn 4943826;
Flashback complete.
SQL>select salary from employees where employee_id=100;
SALARY
----------
50000
SQL>
Wrote file afiedt.buf
1 selectversions_xid ,versions_startscn , versions_operation , salaryfrom employees
2versions betweenscn minvalue and maxvalue
3* where employee_id=100
SQL> /
VERSIONS_XID VERSIONS_STARTSCN V SALARY
---------------- ----------------- - ----------
20001B003F000000 4943834 U 60000
14000000A8020000 4943826 U 50000
40000
闪回4：
SQL> /
VERSIONS_XID VERSIONS_STARTSCN V SALARY
---------------- ----------------- - ----------
20001B003F000000 4943834 U 60000
14000000A8020000 4943826 U 50000
40000
SQL> select salary from employees where employee_id=100;
SALARY
----------
40000
SQL> flashback table employees to scn 4943826;
Flashback complete.
SQL>select salary from employees where employee_id=100;
SALARY
----------
50000
SQL>
闪回8：
alter database add supplemental log data ( primary key ) columns ;
页:
[1]