[10/13] 실습 - 4 Flashback query, versions query, transaction query

versions_xid ~ versions_operstion

11g WS2.ch11 Flashback 

-----------------------------

■ Flashback Query

 -----------------------------

HR@orcl>select salary from employees where employee_id = 100;

    SALARY

----------

     24000

HR@orcl>update employees set salary = 30000 where employee_id = 100;

1 row updated.

HR@orcl>commit;

Commit complete.

HR@orcl>select salary from employees where employee_id = 100;

    SALARY

----------

     30000

HR@orcl>flashback query

  2  select salary from employees

  3  as of timestamp(systimestamp - interval '5' minute)

  4  where employee_id = 100;

flashback query

          *

ERROR at line 1:

ORA-00905: missing keyword

HR@orcl>ed

Wrote file afiedt.buf

  1  select salary from employees

  2  as of timestamp(systimestamp - interval '5' minute)

  3* where employee_id = 100

HR@orcl>/

    SALARY

----------

     24000

HR@orcl>ed

Wrote file afiedt.buf

  1  update employees

  2  set salary = (select salary from employees

  3  as of timestamp(systimestamp - interval '5' minute)

  4* where employee_id = 100)

HR@orcl>/

107 rows updated.

HR@orcl>ed

Wrote file afiedt.buf

  1  update employees

  2  set salary = (select salary from employees

  3  as of timestamp(systimestamp - interval '5' minute) <- second minute hour 모두 가능

  4* where employee_id = 100)

HR@orcl>select salary from employees where employee_id = 100;

    SALARY

----------

     24000

 -------------------------------------------

■ Flashback  versions query(10g New Feature)

 -------------------------------------------

SYS@orcl>alter database add supplemental log data; <- 비활성화 상태에서는 해당 컬럼(필드) 정보에 대한

변경분만 저장되고 add 활성화되면 해당 log에 대한 row data가 모두 저장이 되서 versions transaction query에서는 필수 옵션이다.

Database altered.

HR@orcl>create table fvq_table as select * from employees;

Table created.

select employee_id,  first_name, last_name, salary

from fvq_table

where department_id in (10,20);

EMPLOYEE_ID FIRST_NAME           LAST_NAME                     SALARY

----------- -------------------- ------------------------- ----------

        200 Jennifer             Whalen                          4400

        201 Michael              Hartstein                      13000

        202 Pat                  Fay                             6000

HR@orcl>update fvq_table set salary=15000 where employee_id = 201;

1 row updated.

HR@orcl>commit;

Commit complete.

HR@orcl>update fvq_table set first_name='OCM' where employee_id = 202;

1 row updated.

HR@orcl>commit;

Commit complete.

select employee_id,  first_name, last_name, salary

from fvq_table

  3  where department_id in (10,20);

EMPLOYEE_ID FIRST_NAME           LAST_NAME                     SALARY

----------- -------------------- ------------------------- ----------

        200 Jennifer             Whalen                          4400

        201 Michael              Hartstein                      15000

        202 OCM                  Fay                             6000

SYS@orcl>flashback table hr.employees to timestamp (systimestamp - interval '25' minute);

flashback table hr.employees to timestamp (systimestamp - interval '25' minute)

                   *

ERROR at line 1:

ORA-08189: cannot flashback the table because row movement is not enabled

salary가 모두 24000이 되서 table level로 되돌림

SYS@orcl>alter table hr.employees enable row movement;

Table altered.

SYS@orcl>flashback table hr.employees to timestamp (systimestamp - interval '25' minute);

Flashback complete.

select ora_rowscn, employee_id, first_name, last_name, salary

from fvq_table

  3  where employee_id=202;

ORA_ROWSCN EMPLOYEE_ID FIRST_NAME           LAST_NAME                     SALARY

---------- ----------- -------------------- ------------------------- ----------

   1129971         202 OCM                  Fay                             6000

select versions_xid, versions_startscn, versions_operation, employee_id, first_name, last_name, salary

 from fvq_table versions between scn minvalue and maxvalue

  3  where department_id in (10,20);

VERSIONS_XID     VERSIONS_STARTSCN V EMPLOYEE_ID FIRST_NAME           LAST_NAME                     SALARY

---------------- ----------------- - ----------- -------------------- ------------------------- ----------

0100180017020000           1130441 U         202 OCM                  Fay                             6000

0100100017020000           1130391 U         201 Michael              Hartstein                      15000

          1130391 U         202 OCM                  Fay                             6000

                                             200 Jennifer             Whalen                          4400

                                             201 Michael              Hartstein                      13000

                                             202 Pat                  Fay                             6000

6 rows selected.

HR@orcl>select scn_to_timestamp(1130391) from dual;

SCN_TO_TIMESTAMP(1130391)

---------------------------------------------------------------------------

13-OCT-15 05.41.45.000000000 PM

-----------------------------

■ Flashback transaction query 

---------------------------

HR@orcl>select operation, undo_sql, start_scn

  2  from flashback_transaction_query

  3  where xid=hextoraw('0100180017020000'); <헥사값

from flashback_transaction_query

     *

ERROR at line 2:

ORA-01031: insufficient privileges

HR@orcl>conn / as sysdba

Connected.

SYS@orcl>grant select any transaction to hr; <꼭 필요한 권한

Grant succeeded.

SYS@orcl>conn hr/hr

Connected.

  1  select operation, undo_sql, start_scn

  2  from flashback_transaction_query

  3* where xid=hextoraw('0100180017020000')

HR@orcl>/

HR@orcl>/

OPERATION

--------------------------------

UNDO_SQL

------------------------------------------------------------------------------------------------------------------------------------------------------

 START_SCN

----------

UPDATE

update "HR"."FVQ_TABLE" set "FIRST_NAME" = 'OCM' where ROWID = 'AAASRxAAEAAAAIbAAJ';

   1130440

BEGIN

   1130440

HR@orcl>conn / as sysdba

Connected.

SYS@orcl>select current_scn from v$database;

CURRENT_SCN

-----------

    1131229

 1  select xid, operation, start_scn, start_timestamp, commit_scn, commit_timestamp, undo_sql

  2  from flashback_transaction_query

  3  where table_name = 'FVQ_TABLE'

  4* and commit_scn between 1130391 and 1131229

SYS@orcl>/

XID              OPERATION                         START_SCN START_TIMESTAMP     COMMIT_SCN COMMIT_TIMESTAMP

---------------- -------------------------------- ---------- ------------------- ---------- -------------------

UNDO_SQL

------------------------------------------------------------------------------------------------------------------------------------------------------

0100180017020000 UPDATE                              1130440 2015-10-13:17:42:06    1130441 2015-10-13:17:42:06

update "HR"."FVQ_TABLE" set "FIRST_NAME" = 'OCM' where ROWID = 'AAASRxAAEAAAAIbAAJ';

0100100017020000 UPDATE                              1130017 2015-10-13:17:35:12    1130391 2015-10-13:17:41:45

update "HR"."FVQ_TABLE" set "SALARY" = '15000' where ROWID = 'AAASRxAAEAAAAIbAAI';

0100100017020000 UPDATE                              1130017 2015-10-13:17:35:12    1130391 2015-10-13:17:41:45

update "HR"."FVQ_TABLE" set "FIRST_NAME" = 'Pat' where ROWID = 'AAASRxAAEAAAAIbAAJ';

0100100017020000 UPDATE                              1130017 2015-10-13:17:35:12    1130391 2015-10-13:17:41:45

update "HR"."FVQ_TABLE" set "SALARY" = '13000' where ROWID = 'AAASRxAAEAAAAIbAAI';

SYS@orcl>update "HR"."FVQ_TABLE" set "FIRST_NAME" = 'Pat' where ROWID = 'AAASRxAAEAAAAIbAAJ';

1 row updated.

SYS@orcl>update "HR"."FVQ_TABLE" set "SALARY" = '13000' where ROWID = 'AAASRxAAEAAAAIbAAI';

1 row updated.

SYS@orcl>select employee_id, first_name, last_name, salary

  2  from hr.fvq_table

  3  where department_id in (10,20);

EMPLOYEE_ID FIRST_NAME           LAST_NAME                     SALARY

----------- -------------------- ------------------------- ----------

        200 Jennifer             Whalen                          4400

        201 Michael              Hartstein                      13000

        202 Pat                  Fay                             6000

SYS@orcl>drop table hr.fvq_table purge;

Table dropped.

==================================================

Flashback version query & Flashback transaction query

==================================================

SYS@orcl>conn hr/hr

Connected.

HR@orcl>create table emp

  2  (empno number primary key, empname varchar2(16), salary number);

Table created.

HR@orcl>insert into emp values(111,'Mike',555);

1 row created.

HR@orcl>commit;

Commit complete.

HR@orcl>create table dept(deptno number, deptname varchar2(32));

Table created.

HR@orcl>insert into dept values(10,'Accounting');

1 row created.

HR@orcl>commit;

Commit complete.

HR@orcl>update emp set salary = salary + 100 where empno = 111;

1 row updated.

HR@orcl>insert into dept values (20,'Finance');

1 row created.

HR@orcl>delete from emp where empno =111;

1 row deleted.

HR@orcl>commit;

Commit complete.

HR@orcl>insert into emp values (111,'Tom', 777);

1 row created.

HR@orcl>update emp set salary = salary + 100 where empno = 111;

1 row updated.

HR@orcl>update emp set salary = salary + 50 where empno = 111;

1 row updated.

HR@orcl>commit;

Commit complete.

HR@orcl>select salary from emp where empno = 111;

    SALARY

----------

       927

HR@orcl>select versions_xid XID, versions_startscn START_SCN, versions_endscn END_SCN, versions_operation OPERATION, empname, salary

  2  from hr.emp

  3  versions between scn minvalue and maxvalue

  4  where empno = 111;

XID               START_SCN    END_SCN O EMPNAME              SALARY

---------------- ---------- ---------- - ---------------- ----------

060010005D030000    1276188            I Tom                     927

03000C0076030000    1276137            D Mike                    555

060000005E030000    1276049    1276137 I Mike                    555

select xid, start_scn START, commit_scn COMMIT, operation OP, logon_user USER,undo_sql

 from flashback_transaction_query

where xid = hextoraw('03000C0076030000')

안되서 밑의 쿼리로 대신

select operation, undo_sql, start_scn

  2    from flashback_transaction_query

  3*  where xid=hextoraw('03000C0076030000')

OPERATION

--------------------------------

UNDO_SQL

------------------------------------------------------------------------------------------------------------------------------------------------------

 START_SCN

----------

DELETE

insert into "HR"."EMP"("EMPNO","EMPNAME","SALARY") values ('111','Mike','655');

   1276118

INSERT

delete from "HR"."DEPT" where ROWID = 'AAASTvAAEAAAAI9AAB';

   1276118

UPDATE

update "HR"."EMP" set "SALARY" = '555' where ROWID = 'AAASTtAAEAAAAItAAA';

   1276118

BEGIN

   1276118

 1  select xid, start_scn, commit_scn, operation, table_name, table_owner

  2  from flashback_transaction_query

  3* where table_owner ='HR' and start_timestamp >= to_timestamp('2015-10-14 00:00:00','YYYY-MM-DD HH:MI:SS')

SYS@orcl>ed

Wrote file afiedt.buf

  1  select xid, start_scn, commit_scn, operation, table_name, table_owner

  2  from flashback_transaction_query

  3* where table_owner ='HR' and start_timestamp >= to_timestamp('2015-10-14 01:00:00','YYYY-MM-DD HH:MI:SS')

SYS@orcl>/

XID               START_SCN COMMIT_SCN OPERATION

---------------- ---------- ---------- --------------------------------

TABLE_NAME

------------------------------------------------------------------------------------------------------------------------------------------------------

TABLE_OWNER

--------------------------------

0200030053030000    1276101    1276102 INSERT

DEPT

HR

03000C0076030000    1276118    1276137 DELETE

EMP

HR

03000C0076030000    1276118    1276137 INSERT

DEPT

HR

03000C0076030000    1276118    1276137 UPDATE

EMP

HR

060010005D030000    1276171    1276188 UPDATE

EMP

HR

060010005D030000    1276171    1276188 UPDATE

EMP

HR

060010005D030000    1276171    1276188 INSERT

EMP

HR

060000005E030000    1276005    1276049 INSERT

EMP

HR

8 rows selected.

http://cafe.naver.com/ocmkorea 이현룡교수님 강의자료 참고