oracle閃回特性
一、閃回查詢
1)查詢emp1表
20:55:03 SQL> select * from emp1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
2)刪除一條記錄
20:55:55 SQL> delete from emp1 where ename='JONES';
1 row deleted.
20:56:05 SQL> commit;
Commit complete.
3)再次查詢
20:56:07 SQL> select * from emp1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
13 rows selected.
4)恢復刪除的記錄
20:58:41 SQL> insert into emp1(select *from emp as of timestamp to_timestamp('2014-1-23 20:54:00','yyyy-mm-dd hh24:mi:ss') where ename='JONES');
1 row created.
20:59:11 SQL> commit;
Commit complete.
20:59:18 SQL> select * from emp1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7566 JONES MANAGER 7839 02-APR-81 2975 20
14 rows selected.
二、閃回表
a)刪除一條記錄
21:01:02 SQL> select *from emp1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7566 JONES MANAGER 7839 02-APR-81 2975 20
14 rows selected.
21:11:50 SQL> delete from emp1 where empno=7566; --刪除一行
1 row deleted.
21:12:08 SQL> commit;
Commit complete.
21:12:10 SQL> select *from emp1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
13 rows selected.
2)使用閃回表恢復
21:12:16 SQL> flashback table emp1 to timestamp to_timestamp('2014-1-23 21:10:00'); --沒有啟用行移動,不能使用flashback table進行恢復
flashback table emp1 to timestamp to_timestamp('2014-1-23 21:10:00')
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled
21:14:45 SQL> select row_movement from user_tables where table_name='EMP1';
ROW_MOVE
--------
DISABLED
21:15:08 SQL> alter table emp1 enable row movement; --啟用行移動
Table altered.
21:15:37 SQL> select row_movement from user_tables where table_name='EMP1';
ROW_MOVE
--------
ENABLED
21:15:43 SQL> flashback table emp1 to timestamp to_timestamp('2014-1-23 21:10:00','yyyy-mm-dd hh24:mi:ss'); --閃回表
Flashback complete.
21:16:26 SQL> select * from emp1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7566 JONES MANAGER 7839 02-APR-81 2975 20
14 rows selected.
三、閃回drop
1) 要使用閃回drop,要先啟用recyclebin為on,預設為on,檢視recyclebin引數值：
SQL> show parameter recycleb
NAME TYPE VALUE
------------------------------------ ---------- ----------
recyclebin string on
SQL> alter system set recyclebin=off; --關閉recyclebin
System altered.
SQL> show parameter recycleb
NAME TYPE VALUE
------------------------------------ ----------- -------------
recyclebin string OFF
關閉後刪除的物件不會放在recyclebin當中
SQL> drop table s;
Table dropped.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T BIN$rq6W8XMORSC+tI/adKNm7w==$0 TABLE 2013-11-18:21:29:15
沒有s表.
SQL> alter system set recyclebin=on; --啟用recyclebin
System altered.
SQL> drop table t2;
Table dropped.
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T BIN$rq6W8XMORSC+tI/adKNm7w==$0 TABLE 2013-11-18:21:29:15
T2 BIN$4fnu/CPsRIeeFe4rPxP6yA==$0 TABLE 2014-01-25:12:48:00
可以在recycle中查詢到刪除的t2表
2)查詢recyclebin中的物件
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T BIN$rq6W8XMORSC+tI/adKNm7w==$0 TABLE 2013-11-18:21:29:15
或者使用user_recyclebin這個檢視來查詢
3)查詢刪除的t2表
SQL> select * from "BIN$4fnu/CPsRIeeFe4rPxP6yA==$0";
ID ID2
---------- ----------
1 2
2 3
3 4
4 5
5 6
6 7
7 8
8 9
9 10
10 11
11 12
4)使用flashback table進行恢復
SQL> flashback table "BIN$4fnu/CPsRIeeFe4rPxP6yA==$0" to before drop;
Flashback complete.
5)t2表已恢復
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26937943/viewspace-1075111/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 閃回特性(FLASHBACK DATABASE)OracleDatabase
- Oracle 10G 新特性——閃回表Oracle 10g
- 【閃回特性之閃回查詢】使用閃回查詢(select as of)
- Oracle Database 10g新特性-閃回表OracleDatabase
- oracle flashback特性(1.2)--閃回查詢之As of scnOracle
- ［閃回特性之閃回版本查詢］Flashback Version Query
- oracle閃回Oracle
- oracle 閃回Oracle
- oracle flashback特性(1.1)--閃回查詢之As of timestampOracle
- oracle flashback特性(1.4)--閃回查詢之Transaction queryOracle
- Oracle 12.2的新特性之PDB級別閃回Oracle
- Oracle Database 10g新特性-閃回版本查詢OracleDatabase
- oracle flashback特性(1.5)--閃回查詢之制約因素Oracle
- 使用 Oracle Database 10g中的閃回表特性OracleDatabase
- oracle 閃回 flashbackOracle
- Oracle閃回技術 為Oracle閃回配置資料庫Oracle資料庫
- 全面學習oracle flashback特性(1.2)--閃回查詢之As of scnOracle
- Oracle閃回技術之閃回資料庫Oracle資料庫
- Oracle 11G 閃回技術 使用Oracle閃回查詢Oracle
- oracle閃回查詢Oracle
- Oracle閃回刪除Oracle
- Oracle閃回詳解Oracle
- 清空 oracle 閃回區Oracle
- Oracle 之 閃回操作Oracle
- oracle 閃回查詢Oracle
- 【閃回特性之閃回事務查詢】Flashback Transaction Query
- Oracle 12.2新特性: PDB級閃回資料庫（Flashback PDB）Oracle資料庫
- 全面學習oracle flashback特性(1.4)--閃回查詢之Transaction queryOracle
- 全面學習oracle flashback特性(1.1)--閃回查詢之As of timestampOracle
- 【徵文】全面學習oracle flashback特性(1.2)--閃回查詢之As of scnOracle
- oracle10g新特性：閃回恢復區(Flash recovery area)Oracle
- Oracle 閃回技術 概覽 資料庫閃回功能Oracle資料庫
- Oracle -- 閃回恢復區---實踐1---閃回庫Oracle
- Oracle資料庫閃回Oracle資料庫
- Oracle閃回資料庫Oracle資料庫
- Oracle閃回恢復區Oracle
- oracle的閃回查詢Oracle
- oracle的回閃查詢Oracle