LABORATORIOS FLASHBACK QUERY

https://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns003.htm

LABORATORIO FLASHBACK QUERY


LABORATORIO FLASHBACK VERSION QUERY

select versions_startscn, versions_starttime,versions_endscn, versions_endtime,
       versions_xid, versions_operation, employee_id,salary from hr.employees
VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2021-05-14 18:35:00', 'YYYY-MM-DD HH24:MI:SS')
                        AND TO_TIMESTAMP('2021-05-14 18:44:00', 'YYYY-MM-DD HH24:MI:SS')
where employee_id=200;

LABORATORIO FLASHBACK EN UNPUNTO DEL PASADO

select salary from hr.prueba

as of timestamp (systimestamp - interval '2' MINUTE)

where employee_id=200

LABORATORIO SCN 

select salary from hr.prueba

versions between scn 592900 and 5929230

where employee_id=200

select versions_starttime as "start_date",

versions_endtime as "end_date", 

salary from hr.prueba

versions between scn 5768000 and 5768584

where employee_id=200

LABORATORIO FLASHBACK TABLE

SQL> create table prueba as (select * from hr.employees);

Table created.

SQL>
SQL> show user;
USER is "HR"
SQL>
SQL> !date                     
Mon Apr 27 21:25:53 -05 2020

SQL> select employee_id,salary from hr.prueba where employee_id=200;

EMPLOYEE_ID SALARY
----------- ----------
200   4400

SQL> !date         
Mon Apr 27 21:26:44 -05 2020

SQL> update hr.prueba set salary=500 where employee_id=200;

1 row updated.

SQL> commit;

Commit complete.

SQL> !date
Mon Apr 27 21:27:20 -05 2020

SQL> flashback table hr.prueba to timestamp to_timestamp('2021-05-14 18:40:00','YYYY-MM-DD HH24:MI:SS');
flashback table hr.prueba to timestamp to_timestamp('2020-04-27 21:26:44','YYYY-MM-DD HH24:MI:SS')
                   *
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled


SQL> alter table hr.prueba enable row movement;

Table altered.

SQL> flashback table hr.prueba to timestamp to_timestamp('2020-10-20 07:13:00','YYYY-MM-DD HH24:MI:SS');

Flashback complete.

SQL> select employee_id,salary from hr.prueba where employee_id=200;

EMPLOYEE_ID SALARY
----------- ----------
200   4400

SQL>

SQL> select salary from hr.prueba where employee_id=200;

    SALARY

----------

      5000

SQL> flashback table hr.prueba to timestamp to_timestamp('2020-10-20 07:13:00','YYYY-MM-DD HH24:MI:SS');

Flashback complete.

SQL> flashback table hr.prueba to timestamp to_timestamp('2020-10-20 07:10:00','YYYY-MM-DD HH24:MI:SS');

Flashback complete.

SQL> select salary from hr.prueba where employee_id=200;

    SALARY

----------

      4400