Flashback Query and Flashback Table (fast Flashback Query and quick Flashback Table)

Flashback query is a flashback Based on the undo tablespace. The related parameters are as follows:

SQL> show parameter undo

NAME TYPE VALUE
-----------------------------------------------------------------------------
Undo_management string AUTO
Undo_retention integer 900
Undo_tablespace string UNDOTBS1

Undo_management:The undo management method can be automatic auto or manual manul. The default value is auto.

Undo_retention:This parameter indicates that if the space in the undo tablespace is sufficient, the undo data should be retained for at least 900 seconds;

 

 

 

SQL> insert into test01_delete select * from test01 as of timestamp into imestamp-interval '1' minute

Where id not in (select id from test01 );

SQL> create table test01_delete as select * from test01 as of timestamp when imestamp-interval '1' minute

Where id not in (select id from test01 );

 

Use of Flashback Table flash back Table

Like Flashback Query, Flashback Table is based on undo Flashback. You must have the flashback table permission and the row movement function when flashing back a table;

Example:

[Oracle @ b1 ~] $ Export NLS_DATE_FORMAT = "YYYY-MM-DD HH24: MI: SS"

SQL> select to_date (sysdate, 'yyyy-MM-DD HH24: MI: ss') sys_date from dual;

SYS_DATE
-------------------
18:11:39

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
1054345

SQL> grant flashback any table to scott;

Grant succeeded.

SQL> conn scott/xyc
Connected.

SQL> select * from xyc_t1;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-----------------------------------------------------------------------------------------------------------
7698 blake manager 7839 00:00:00 2850 30
7902 ford analyst 7566 00:00:00 8888 20

SQL> delete from xyc_t1;

2 rows deleted.

SQL> commit;

Commit complete.

SQL> flashback table xyc_t1 to scn '123 ';
Flashback table xyc_t1 to scn '20140901'
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled-the row movement function must be enabled

SQL> alter table xyc_t1 enable row movement;-The row movement function is enabled.

Table altered.

SQL> flashback table xyc_t1 to scn '123 ';

Flashback complete.

SQL> select * from xyc_t1;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-----------------------------------------------------------------------------------------------------------
7698 blake manager 7839 00:00:00 2850 30
7902 ford analyst 7566 00:00:00 8888 20

SQL> flashback table xyc_t1 to timestamp to_timestamp ('2017-10-06 18:10:51 ', 'yyyy-MM-DD HH24: MI: ss ');

Flashback complete.

SQL> select * from xyc_t1;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-----------------------------------------------------------------------------------------------------------
7698 blake manager 7839 00:00:00 2850 30
7902 ford analyst 7566 00:00:00 8888 20

SQL>