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閃回技術 為Oracle閃回配置資料庫Oracle資料庫
- Oracle 12.2新特性: PDB級閃回資料庫（Flashback PDB）Oracle資料庫
- Oracle資料庫閃回Oracle資料庫
- 【RECO_ORACLE】Oracle閃回PDB的方法Oracle
- Oracle 11G 閃回技術 使用Oracle閃回事務查詢Oracle
- Oracle 11G 閃回技術 閃回版本查詢和閃回事務查詢Oracle
- Oracle閃回技術--Flashback Version QueryOracle
- Oracle 閃回資料庫測試Oracle資料庫
- 【PDB】pdb閃回，Oracle還原點Oracle
- 詳解oracle資料庫閃回Oracle資料庫
- Oracle資料庫閃回區空間不足Oracle資料庫
- Oracle回收站表閃回機制研究Oracle
- ORACLE 閃回檢視v$flashback_database_log/statOracleDatabase
- DM7閃回與閃回查詢
- Oracle閃回技術 概覽 應用程式開發功能Oracle
- Oracle閃回功能恢復偶然丟失的資料(轉)Oracle
- 【FLASHBACK】Oracle閃回及回收站相關語句參考Oracle
- 【Oracle 12c資料庫支援閃回庫功能】實驗Oracle資料庫
- GeminiDB Cassandra介面新特性FLASHBACK釋出：任意時間點秒級閃回
- 2.6.3 指定閃回區
- flashback query閃回資料
- Oracle drop分割槽表單個分割槽無法透過閃回恢復Oracle
- DM8 閃回查詢
- Oracle特性總結Oracle
- mysql閃回工具binlog2sqlMySql
- my2sql資料閃回SQL
- 【趙強老師】MySQL的閃回MySql
- oracle回滾溯源Oracle
- Oracle IO校準特性Oracle
- Oracle的特性分頁Oracle
- Oracle 19C Data Guard基礎運維-07 failover後閃回恢復dg架構Oracle運維AI架構
- Flashback Drop閃回刪除功能實踐
- dg_閃回資料庫實驗資料庫
- 工具分享丨資料閃回工具MyFlash
- [20180423]表空間閃回與snapshot standby
- Orcale利用閃回功能恢復資料
- Oracle 資料回滾Oracle
- 【OMF】使用Oracle的OMF 特性Oracle
- MySQL工具之binlog2sql閃回操作MySql