57.闪回技术的应用
1.闪回技术概述
sys@ORCL10G 2023-03-08 09:31:52> show parameter undo;
NAME_COL_PLUS_SHOW_PARAM TYPE VALUE_COL_PLUS_SHOW_PARAM
---------------------------------------- ----------- --------------------------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
sys@ORCL10G 2023-03-08 09:31:55> select tablespace_name,retention from dba_tablespaces;
TABLESPACE_NAME RETENTION
------------------------------ -----------
SYSTEM NOT APPLY
UNDOTBS1 NOGUARANTEE
SYSAUX NOT APPLY
TEMP NOT APPLY
USERS NOT APPLY
EXAMPLE NOT APPLY
--undo是否被覆盖
UNDOTBS1 NOGUARANTEE2.闪回查询和闪回版本查询
scott@ORCL10G 2023-03-08 09:46:36> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Elapsed: 00:00:00.01
scott@ORCL10G 2023-03-08 09:46:40> insert into dept values(50,'D50','L50');
1 row created.
Elapsed: 00:00:00.00
scott@ORCL10G 2023-03-08 09:46:52> commit;
Commit complete.
Elapsed: 00:00:00.00
scott@ORCL10G 2023-03-08 09:46:55> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
50 D50 L50
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Elapsed: 00:00:00.00
scott@ORCL10G 2023-03-08 09:47:29> select * from dept as of timestamp sysdate - 1/1440;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Elapsed: 00:00:00.00
scott@ORCL10G 2023-03-08 09:52:28> select ora_rowscn from dept;
ORA_ROWSCN
----------
980671
384749
384749
384749
384749
Elapsed: 00:00:00.00
scott@ORCL10G 2023-03-08 09:52:32> select * from dept as of scn 980671-1;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Elapsed: 00:00:00.00
scott@ORCL10G 2023-03-08 09:53:51> update dept set dname = 'DBA' where deptno = 50;
1 row updated.
Elapsed: 00:00:00.00
scott@ORCL10G 2023-03-08 09:53:57> commit;
Commit complete.
Elapsed: 00:00:00.00
scott@ORCL10G 2023-03-08 09:53:59> select ora_rowscn from dept;
ORA_ROWSCN
----------
980897
384749
384749
384749
384749
Elapsed: 00:00:00.00
scott@ORCL10G 2023-03-08 09:54:03> select * from dept as of scn 980897-1;
DEPTNO DNAME LOC
---------- -------------- -------------
50 D50 L50
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Elapsed: 00:00:00.00
--提交之前是D50
scott@ORCL10G 2023-03-08 10:09:38> select * from dept as of timestamp to_timestamp('2023-03-08 09:53:57','yyyy-mm-dd hh24:mi;ss');
DEPTNO DNAME LOC
---------- -------------- -------------
50 D50 L50
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Elapsed: 00:00:00.00
--提交之后是DBA
scott@ORCL10G 2023-03-08 10:09:44> select * from dept as of timestamp to_timestamp('2023-03-08 09:53:59','yyyy-mm-dd hh24:mi;ss');
DEPTNO DNAME LOC
---------- -------------- -------------
50 DBA L50
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Elapsed: 00:00:00.00
scott@ORCL10G 2023-03-08 11:17:45> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
50 DBA L50
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Elapsed: 00:00:00.00
scott@ORCL10G 2023-03-08 11:17:51> delete from dept where deptno = 50;
1 row deleted.
Elapsed: 00:00:00.00
scott@ORCL10G 2023-03-08 11:18:06> commit;
Commit complete.
Elapsed: 00:00:00.00
scott@ORCL10G 2023-03-08 11:18:10> select versions_xid,versions_operation,deptno from dept versions between scn minvalue and maxvalue;
VERSIONS_XID V DEPTNO
---------------- - ----------
08001A00EA000000 D 50
50
10
20
30
40
6 rows selected.
Elapsed: 00:00:00.00
scott@ORCL10G 2023-03-08 11:18:13> select * from dept as of timestamp to_timestamp('2023-03-08 09:53:59','yyyy-mm-dd hh24:mi;ss') where deptno = 50;
DEPTNO DNAME LOC
---------- -------------- -------------
50 DBA L50
Elapsed: 00:00:00.00
--不能跨相关的ddl操作
scott@ORCL10G 2023-03-08 11:22:39> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
50 D50 L50
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Elapsed: 00:00:00.00
scott@ORCL10G 2023-03-08 11:22:45> delete from dept where deptno = 50;
1 row deleted.
Elapsed: 00:00:00.00
scott@ORCL10G 2023-03-08 11:23:03> alter table dept move;
Table altered.
Elapsed: 00:00:00.05
scott@ORCL10G 2023-03-08 11:23:11> select versions_xid,versions_operation,deptno from dept versions between scn minvalue and maxvalue;
VERSIONS_XID V DEPTNO
---------------- - ----------
10
20
30
40
Elapsed: 00:00:00.013.闪回表的操作和闪回事务的处理
[oracle@yuanzj.com:/home/oracle]$ scott
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Mar 8 11:28:54 2023
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
scott@ORCL10G 2023-03-08 11:28:54> update emp set sal=sal+100;
14 rows updated.
Elapsed: 00:00:00.01
scott@ORCL10G 2023-03-08 11:29:06> commit;
Commit complete.
Elapsed: 00:00:00.00
scott@ORCL10G 2023-03-08 11:29:48> select versions_starttime,versions_startscn from emp versions between scn minvalue and maxvalue;
VERSIONS_STARTTIME VERSIONS_STARTSCN
--------------------------------------------------------------------------- -----------------
08-MAR-23 11.29.07 AM 985068
08-MAR-23 11.29.07 AM 985068
08-MAR-23 11.29.07 AM 985068
08-MAR-23 11.29.07 AM 985068
08-MAR-23 11.29.07 AM 985068
08-MAR-23 11.29.07 AM 985068
08-MAR-23 11.29.07 AM 985068
08-MAR-23 11.29.07 AM 985068
08-MAR-23 11.29.07 AM 985068
08-MAR-23 11.29.07 AM 985068
08-MAR-23 11.29.07 AM 985068
08-MAR-23 11.29.07 AM 985068
08-MAR-23 11.29.07 AM 985068
08-MAR-23 11.29.07 AM 985068
14 rows selected.
Elapsed: 00:00:00.00
scott@ORCL10G 2023-03-08 11:31:11> select distinct versions_starttime, versions_startscn from emp versions between scn minvalue and maxvalue;
VERSIONS_STARTTIME VERSIONS_STARTSCN
--------------------------------------------------------------------------- -----------------
08-MAR-23 11.29.07 AM 985068
Elapsed: 00:00:00.00
scott@ORCL10G 2023-03-08 11:31:27> alter table emp enable row movement;
Table altered.
Elapsed: 00:00:00.02
scott@ORCL10G 2023-03-08 11:32:18> flashback table emp to scn 985068-1;
flashback table emp to scn 985068-1
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index 'SCOTT.PK_DEPT' or partition of such index is in unusable state
Elapsed: 00:00:00.05
scott@ORCL10G 2023-03-08 11:32:45> alter index PK_DEPT rebuild;
Index altered.
Elapsed: 00:00:00.02
scott@ORCL10G 2023-03-08 11:33:02> flashback table emp to scn 985068-1;
Flashback complete.
Elapsed: 00:00:00.06
scott@ORCL10G 2023-03-08 11:33:05> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
14 rows selected.
Elapsed: 00:00:00.01
scott@ORCL10G 2023-03-08 11:33:10> alter table emp disable row movement;
Table altered.
Elapsed: 00:00:00.00
scott@ORCL10G 2023-03-08 11:34:46> select table_name,row_movement from user_tables;
TABLE_NAME ROW_MOVE
------------------------------ --------
EMP DISABLED
BONUS DISABLED
SALGRADE DISABLED
SYS_TEMP_FBT DISABLED
DEPT DISABLED
Elapsed: 00:00:00.03 
--闪回事务查询先决条件
--1.必须有附加日志
[oracle@yuanzj.com:/home/oracle]$ scott
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Mar 8 12:05:58 2023
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options
scott@ORCL10G 2023-03-08 12:05:58> update emp set sal=sal-100;
14 rows updated.
Elapsed: 00:00:00.00
scott@ORCL10G 2023-03-08 12:06:10> commit;
Commit complete.
Elapsed: 00:00:00.00
scott@ORCL10G 2023-03-08 12:06:13> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
14 rows selected.
Elapsed: 00:00:00.01
scott@ORCL10G 2023-03-08 12:06:17> delete emp;
14 rows deleted.
Elapsed: 00:00:00.00
scott@ORCL10G 2023-03-08 12:06:25> commit;
Commit complete.
Elapsed: 00:00:00.00
scott@ORCL10G 2023-03-08 12:06:28> conn / as sysdba
Connected.
sys@ORCL10G 2023-03-08 12:07:11> select versions_xid,versions_operation from scott.emp versions between scn minvalue and maxvalue where empno = 7566;
VERSIONS_XID V
---------------- -
0A000C0008010000 D
0900010015010000 U
0900150014010000 I
0100100032010000 D
0A00260007010000 U
6 rows selected.
Elapsed: 00:00:00.00
sys@ORCL10G 2023-03-08 12:07:14> select undo_sql from flashback_transaction_query where xid = '0A000C0008010000';
UNDO_SQL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7934','MILLER','CLERK','7782',TO_DATE('1982-01-23 00:00:00', 'yyyy-mm-dd HH24:MI:SS'),'1300',NULL,'10');
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7902','FORD','ANALYST','7566',TO_DATE('1981-12-03 00:00:00', 'yyyy-mm-dd HH24:MI:SS'),'3000',NULL,'20');
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7900','JAMES','CLERK','7698',TO_DATE('1981-12-03 00:00:00', 'yyyy-mm-dd HH24:MI:SS'),'950',NULL,'30');
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7876','ADAMS','CLERK','7788',TO_DATE('1987-05-23 00:00:00', 'yyyy-mm-dd HH24:MI:SS'),'1100',NULL,'20');
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7844','TURNER','SALESMAN','7698',TO_DATE('1981-09-08 00:00:00', 'yyyy-mm-dd HH24:MI:SS'),'1500','0','30');
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7839','KING','PRESIDENT',NULL,TO_DATE('1981-11-17 00:00:00', 'yyyy-mm-dd HH24:MI:SS'),'5000',NULL,'10');
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7788','SCOTT','ANALYST','7566',TO_DATE('1987-04-19 00:00:00', 'yyyy-mm-dd HH24:MI:SS'),'3000',NULL,'20');
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7782','CLARK','MANAGER','7839',TO_DATE('1981-06-09 00:00:00', 'yyyy-mm-dd HH24:MI:SS'),'2450',NULL,'10');
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7698','BLAKE','MANAGER','7839',TO_DATE('1981-05-01 00:00:00', 'yyyy-mm-dd HH24:MI:SS'),'2850',NULL,'30');
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7654','MARTIN','SALESMAN','7698',TO_DATE('1981-09-28 00:00:00', 'yyyy-mm-dd HH24:MI:SS'),'1250','1400','30');
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7566','JONES','MANAGER','7839',TO_DATE('1981-04-02 00:00:00', 'yyyy-mm-dd HH24:MI:SS'),'2975',NULL,'20');
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7521','WARD','SALESMAN','7698',TO_DATE('1981-02-22 00:00:00', 'yyyy-mm-dd HH24:MI:SS'),'1250','500','30');
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7499','ALLEN','SALESMAN','7698',TO_DATE('1981-02-20 00:00:00', 'yyyy-mm-dd HH24:MI:SS'),'1600','300','30');
insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO") values ('7369','SMITH','CLERK','7902',TO_DATE('1980-12-17 00:00:00', 'yyyy-mm-dd HH24:MI:SS'),'800',NULL,'20');
15 rows selected.
Elapsed: 00:00:01.79
sys@ORCL10G 2023-03-08 12:08:14> spool /tmp/a.sql
sys@ORCL10G 2023-03-08 12:08:39> @/tmp/a.sql
1 row created.
Elapsed: 00:00:00.01
1 row created.
Elapsed: 00:00:00.00
1 row created.
Elapsed: 00:00:00.00
1 row created.
Elapsed: 00:00:00.00
1 row created.
Elapsed: 00:00:00.00
1 row created.
Elapsed: 00:00:00.00
1 row created.
Elapsed: 00:00:00.00
1 row created.
Elapsed: 00:00:00.00
1 row created.
Elapsed: 00:00:00.00
1 row created.
Elapsed: 00:00:00.00
1 row created.
Elapsed: 00:00:00.00
1 row created.
Elapsed: 00:00:00.00
1 row created.
Elapsed: 00:00:00.00
1 row created.
Elapsed: 00:00:00.00
sys@ORCL10G 2023-03-08 12:09:21> commit;
Commit complete.
Elapsed: 00:00:00.01
sys@ORCL10G 2023-03-08 12:09:25> select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
14 rows selected.
Elapsed: 00:00:00.004.闪回技术归档
[oracle@yuanzj.com:/home/oracle]$ sas
SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 8 12:27:34 2023
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
sys@ORCL11G 2023-03-08 12:27:34> create tablespace fda datafile '/oradata/orcl11g/fda01.dbf' size 1024m autoextend on maxsize 30720m;
Tablespace created.
Elapsed: 00:00:08.50
sys@ORCL11G 2023-03-08 12:28:33> create flashback archive default fda1 tablespace fda quota 10m retention 1 year;
Flashback archive created.
Elapsed: 00:00:00.02
sys@ORCL11G 2023-03-08 12:34:22> select * from system_privilege_map where name like '%FLASH%';
PRIVILEGE NAME PROPERTY
---------- -------------------------------------------------------------------------------- ----------
-243 FLASHBACK ANY TABLE 0
-350 FLASHBACK ARCHIVE ADMINISTER 0
Elapsed: 00:00:00.00
sys@ORCL11G 2023-03-08 12:34:27> grant FLASHBACK ARCHIVE ADMINISTER to scott;
Grant succeeded.
Elapsed: 00:00:00.01
sys@ORCL11G 2023-03-08 12:40:27> conn scott/tiger
Connected.
scott@ORCL11G 2023-03-08 12:35:37> alter table emp flashback archive;
Table altered.
Elapsed: 00:00:00.02
scott@ORCL11G 2023-03-08 12:38:15> alter table emp no flashback archive;
Table altered.
Elapsed: 00:00:00.01
scott@ORCL11G 2023-03-08 12:38:22> alter table emp flashback archive;
Table altered.
Elapsed: 00:00:00.01
scott@ORCL11G 2023-03-08 12:38:24> conn / as sysdba
Connected.
sys@ORCL11G 2023-03-08 12:40:02> select * from tab where tname like '%FLASHBACK%';
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DBA_FLASHBACK_ARCHIVE VIEW
DBA_FLASHBACK_ARCHIVE_TABLES VIEW
DBA_FLASHBACK_ARCHIVE_TS VIEW
DBA_FLASHBACK_TXN_REPORT VIEW
DBA_FLASHBACK_TXN_STATE VIEW
FLASHBACK_TRANSACTION_QUERY VIEW
GV_$FLASHBACK_DATABASE_LOG VIEW
GV_$FLASHBACK_DATABASE_LOGFILE VIEW
GV_$FLASHBACK_DATABASE_STAT VIEW
USER_FLASHBACK_ARCHIVE VIEW
USER_FLASHBACK_ARCHIVE_TABLES VIEW
USER_FLASHBACK_TXN_REPORT VIEW
USER_FLASHBACK_TXN_STATE VIEW
V_$FLASHBACK_DATABASE_LOG VIEW
V_$FLASHBACK_DATABASE_LOGFILE VIEW
V_$FLASHBACK_DATABASE_STAT VIEW
V_$FLASHBACK_TXN_GRAPH VIEW
V_$FLASHBACK_TXN_MODS VIEW
18 rows selected.
Elapsed: 00:00:00.02
sys@ORCL11G 2023-03-08 12:40:11> desc USER_FLASHBACK_ARCHIVE_TABLES
Name Null? Type
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TABLE_NAME NOT NULL VARCHAR2(30)
OWNER_NAME NOT NULL VARCHAR2(30)
FLASHBACK_ARCHIVE_NAME NOT NULL VARCHAR2(255)
ARCHIVE_TABLE_NAME VARCHAR2(53)
STATUS VARCHAR2(13)
sys@ORCL11G 2023-03-08 12:40:27> conn scott/tiger
Connected.
scott@ORCL11G 2023-03-08 12:40:43> select * from USER_FLASHBACK_ARCHIVE_TABLES;
TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME STATUS
------------------------------ ------------------------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------- -------------
EMP SCOTT FDA1 SYS_FBA_HIST_87108 ENABLED
Elapsed: 00:00:00.08
scott@ORCL11G 2023-03-08 12:50:51> select * from emp as of timestamp sysdate - 10/1440;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
scott@ORCL11G 2023-03-08 12:51:39> insert into emp select * from emp as of timestamp sysdate - 7/1440;
14 rows created.
Elapsed: 00:00:00.01
scott@ORCL11G 2023-03-08 12:51:45> commit;
Commit complete.
Elapsed: 00:00:00.00
scott@ORCL11G 2023-03-08 12:51:48> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
14 rows selected.
Elapsed: 00:00:00.00
5.闪回删除的对象和回收站
[oracle@yuanzj.com:/home/oracle]$ sas
SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 8 13:03:30 2023
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
sys@ORCL11G 2023-03-08 13:03:30> show parameter recyclebin;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string on
sys@ORCL11G 2023-03-08 13:03:35> conn scott/tiger
Connected.
scott@ORCL11G 2023-03-08 13:03:38> create table e as select * from emp;
Table created.
Elapsed: 00:00:00.03
scott@ORCL11G 2023-03-08 13:03:43> drop table e;
Table dropped.
Elapsed: 00:00:00.01
scott@ORCL11G 2023-03-08 13:03:50> create table e as select * from dept;
Table created.
Elapsed: 00:00:00.01
scott@ORCL11G 2023-03-08 13:03:56> drop table e;
Table dropped.
Elapsed: 00:00:00.02
scott@ORCL11G 2023-03-08 13:03:59> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
E BIN$9l10ME6uQDDgUygCqMAlCA==$0 TABLE 2023-03-08:13:03:59
E BIN$9l10ME6tQDDgUygCqMAlCA==$0 TABLE 2023-03-08:13:03:50
E BIN$9l1r/7tZPrvgUygCqMB1gw==$0 TABLE 2023-03-08:13:01:36
E BIN$9l1r/7tYPrvgUygCqMB1gw==$0 TABLE 2023-03-08:13:01:32
scott@ORCL11G 2023-03-08 13:04:07> flashback table "BIN$9l1r/7tZPrvgUygCqMB1gw==$0" to before drop;
Flashback complete.
Elapsed: 00:00:00.01
scott@ORCL11G 2023-03-08 13:04:49> desc e;
Name Null? Type
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
Name Null? Type
scott@ORCL11G 2023-03-08 13:04:56> flashback table "BIN$9l10ME6uQDDgUygCqMAlCA==$0" to before drop rename to dept_old;
Flashback complete.
Elapsed: 00:00:00.01
scott@ORCL11G 2023-03-08 13:06:04> desc dept_old;
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
--先进后出
--后进先出
--清空回收站
--purge recyclebin6.闪回数据库
sys@ORCL11G 2023-03-08 13:22:55> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
Elapsed: 00:00:00.00
sys@ORCL11G 2023-03-08 13:23:59> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ORCL11G 2023-03-08 13:24:11> startup
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2253664 bytes
Variable Size 989858976 bytes
Database Buffers 603979776 bytes
Redo Buffers 7319552 bytes
Database mounted.
Database opened.
sys@ORCL11G 2023-03-08 13:24:22> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oradata/orcl11g/archivelog
Oldest online log sequence 31
Next log sequence to archive 33
Current log sequence 33
sys@ORCL11G 2023-03-08 13:24:29> alter database flashback on;
Database altered.
Elapsed: 00:00:00.30
sys@ORCL11G 2023-03-08 13:25:34> show parameter flashback;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440
