當前位置：首頁 > 编程资源 > 编程问答 >内容正文

编程问答

Oracle：闪回，闪回表，闪回删除，闪回版本查询，闪回事务查询

發布時間：2024/9/27 编程问答 25 豆豆

生活随笔收集整理的這篇文章主要介紹了 Oracle：闪回，闪回表，闪回删除，闪回版本查询，闪回事务查询小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

SQL> --SCN(系統改變號) sysdate的對應關系
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),timestamp_to_scn(sysdate) from dual;

TO_CHAR(SYSDATE,'YY TIMESTAMP_TO_SCN(SYSDATE)??????????????????????????????????
------------------- -------------------------??????????????????????????????????
2011-06-15 14:11:06?????????????????? 1267674??????????????????????????????????

SQL> --undo表空間:用于保存歷史操作記錄
SQL> show parameters undo;

NAME???????????????????????????????? TYPE??????? VALUE?????????????????????????
------------------------------------ ----------- ------------------------------
undo_management????????????????????? string????? AUTO??????????????????????????
undo_retention?????????????????????? integer???? 900???????????????????????????
undo_tablespace????????????????????? string????? UNDOTBS1??????????????????????
SQL> --修改閃回時間
SQL> conn / as sysdba
已連接。
SQL> alter system set undo_retention=1200 scope=both;

系統已更改。

SQL> /*
SQL> scope: momory-當前session中有效
SQL>??????? spfile-修改配置文件，但當前會話中無效
SQL>??????? both
SQL> */
SQL> conn scott/tiger
已連接。
SQL> show parameters undo;

NAME???????????????????????????????? TYPE??????? VALUE?????????????????????????
------------------------------------ ----------- ------------------------------
undo_management????????????????????? string????? AUTO??????????????????????????
undo_retention?????????????????????? integer???? 1200??????????????????????????
undo_tablespace????????????????????? string????? UNDOTBS1??????????????????????
SQL> conn / as sysdba
已連接。
SQL> -- 為scott用戶授予閃回權限
SQL> grant flashback any table to scott;

授權成功。

SQL> conn scott/tiger
已連接。
SQL> host cls

SQL> --閃回表
SQL> create table flashback_table
? 2? (tid number,tname varchar(20));

表已創建。

SQL> insert into flashback_table values(1,'Tom');

已創建 1 行。

SQL> insert into flashback_table values(2,'Mary');

已創建 1 行。

SQL> insert into flashback_table values(3,'Mike');

已創建 1 行。

SQL> commit;

提交完成。

SQL> select * from flashback_table;

?????? TID TNAME???????????????????????????????????????????????????????????????
---------- --------------------????????????????????????????????????????????????
???????? 1 Tom?????????????????????????????????????????????????????????????????
???????? 2 Mary????????????????????????????????????????????????????????????????
???????? 3 Mike????????????????????????????????????????????????????????????????

SQL> host cls

SQL> --刪除一條數據
SQL> --保留刪除前的scn號
SQL> select timestamp_to_scn(sysdate) from dual;

TIMESTAMP_TO_SCN(SYSDATE)??????????????????????????????????????????????????????
-------------------------??????????????????????????????????????????????????????
????????????????? 1267969??????????????????????????????????????????????????????

SQL> delete from flashback_table where tid=2;

已刪除 1 行。

SQL> commit;

提交完成。

SQL> select * from flashback_table;

?????? TID TNAME???????????????????????????????????????????????????????????????
---------- --------------------????????????????????????????????????????????????
???????? 1 Tom?????????????????????????????????????????????????????????????????
???????? 3 Mike????????????????????????????????????????????????????????????????

SQL> --執行閃回表
SQL> select rowid,tid,tname from flashback_table;

ROWID???????????????????? TID TNAME????????????????????????????????????????????
------------------ ---------- --------------------?????????????????????????????
AAANIBAAEAAAAGwAAA????????? 1 Tom??????????????????????????????????????????????
AAANIBAAEAAAAGwAAC????????? 3 Mike?????????????????????????????????????????????

SQL> --開啟表的行移動功能
SQL> alter table flashback_table enable row movement;

表已更改。

SQL> flashback table flashback_table to scn 1267969;

閃回完成。

SQL> select * from flashback_table;

SQL> /*
SQL> 閃回表的注意實現:
SQL> 1. flashback any table的權限
SQL> 2. undo的參數
SQL> 3. scn或者時間
SQL> 4. 開啟表的行移動功能
SQL> */
SQL> host cls

SQL> --刪除刪除
SQL> --了解oracle回收站
SQL> show recyclebin;
SQL> create table aaa (ddd number);

表已創建。

SQL> drop table aaa;

表已刪除。

SQL> show recyclebin;
ORIGINAL NAME??? RECYCLEBIN NAME??????????????? OBJECT TYPE? DROP TIME?????????
---------------- ------------------------------ ------------ -------------------
AAA????????????? BIN$tGr4rS9KRomVbEjLLr4zvw==$0 TABLE??????? 2011-06-15:14:30:54
SQL> --回收站只對普通用戶有效
SQL> conn / as sysdba
已連接。
SQL> create table bbb (bbb number);

表已創建。

SQL> drop table bbb;

表已刪除。

SQL> show user;
USER 為 "SYS"
SQL> show recyclebin;
SQL> conn scott/tiger
已連接。
SQL> host cls

閃回完成。

SQL> select * from tab;

TNAME????????????????????????? TABTYPE? CLUSTERID??????????????????????????????
------------------------------ ------- ----------??????????????????????????????
DEPT?????????????????????????? TABLE???????????????????????????????????????????
EMP??????????????????????????? TABLE???????????????????????????????????????????
BONUS????????????????????????? TABLE???????????????????????????????????????????
SALGRADE?????????????????????? TABLE???????????????????????????????????????????
SYS_TEMP_FBT?????????????????? TABLE???????????????????????????????????????????
FLASHBACK_TABLE??????????????? TABLE???????????????????????????????????????????
AAA??????????????????????????? TABLE???????????????????????????????????????????

已選擇7行。

SQL> drop table aaa;

表已刪除。

SQL> show recyclebin;
ORIGINAL NAME??? RECYCLEBIN NAME??????????????? OBJECT TYPE? DROP TIME?????????
---------------- ------------------------------ ------------ -------------------
AAA????????????? BIN$YiaXW4DXTC6CrwUeTvrGjQ==$0 TABLE??????? 2011-06-15:14:34:32
SQL> flashback table "BIN$YiaXW4DXTC6CrwUeTvrGjQ==$0" to before drop;

閃回完成。

SQL> /*
SQL> 閃回刪除:
SQL> 1. 了解oracle的回收站(只有普通用戶有)
SQL> 2. 可以通過原始的名字閃回刪除
SQL> 3. 可以通過回收站中的名字閃回(需要雙引號)
SQL> */
SQL> drop table aaa;

表已刪除。

SQL> --清空回收站
SQL> purge recyclebin;

回收站已清空。

SQL> show recyclebin;
SQL> --drop table aaa purge; 這樣不能被閃回
SQL> host cls

SQL> -閃回重名表
SP2-0734: 未知的命令開頭 "-閃回重名..." - 忽略了剩余的行。
SQL> --閃回重名表
SQL> create table test(testid number);

表已創建。

SQL> drop table test;

表已刪除。

SQL> show recyclebin;
ORIGINAL NAME??? RECYCLEBIN NAME??????????????? OBJECT TYPE? DROP TIME?????????
---------------- ------------------------------ ------------ -------------------
TEST???????????? BIN$p3jlQPFZQNWAHIimotfNpg==$0 TABLE??????? 2011-06-15:14:40:06
SQL> create table test(testid number);

表已創建。

SQL> drop table test;

表已刪除。

SQL> show recyclebin;
ORIGINAL NAME??? RECYCLEBIN NAME??????????????? OBJECT TYPE? DROP TIME?????????
---------------- ------------------------------ ------------ -------------------
TEST???????????? BIN$+n1D+iKTTeOra0Esv9y4iA==$0 TABLE??????? 2011-06-15:14:40:22
TEST???????????? BIN$p3jlQPFZQNWAHIimotfNpg==$0 TABLE??????? 2011-06-15:14:40:06
SQL> flashback table test to before drop;

閃回完成。

SQL> flashback table test to before drop rename to test123;

閃回完成。

SQL> select * from tab;

TNAME????????????????????????? TABTYPE? CLUSTERID??????????????????????????????
------------------------------ ------- ----------??????????????????????????????
DEPT?????????????????????????? TABLE???????????????????????????????????????????
EMP??????????????????????????? TABLE???????????????????????????????????????????
BONUS????????????????????????? TABLE???????????????????????????????????????????
SALGRADE?????????????????????? TABLE???????????????????????????????????????????
SYS_TEMP_FBT?????????????????? TABLE???????????????????????????????????????????
FLASHBACK_TABLE??????????????? TABLE???????????????????????????????????????????
TEST123??????????????????????? TABLE???????????????????????????????????????????
TEST?????????????????????????? TABLE???????????????????????????????????????????

已選擇8行。

SQL> host cls

SQL> drop table test purge;

表已刪除。

SQL> drop table test123 purge;

表已刪除。

SQL> host cls

SQL> select * from tab;

已選擇6行。

SQL> host cls

SQL> --閃回版本查詢
SQL> --就是查詢表的歷史記錄
SQL> create table version_table
? 2? (empno number,ename varchar(20));

表已創建。

SQL> insert into version_table values(1,'Tom');

已創建 1 行。

SQL> commit;

提交完成。

SQL> insert into version_table values(2,'Mary');

已創建 1 行。

SQL> commit;

提交完成。

SQL> insert into version_table values(3,'Mike');

已創建 1 行。

SQL> commit;

提交完成。

SQL> update version_table set ename='Mary123' where empno=2;

已更新 1 行。

SQL> commit;

提交完成。

SQL> host cls

SQL> select * from version_table;

???? EMPNO ENAME???????????????????????????????????????????????????????????????
---------- --------------------????????????????????????????????????????????????
???????? 1 Tom?????????????????????????????????????????????????????????????????
???????? 2 Mary123?????????????????????????????????????????????????????????????
???????? 3 Mike????????????????????????????????????????????????????????????????

SQL> /*
SQL> versions提供一些偽列:
SQL> versions_operation:?? 操作
SQL> versions_starttime:?? 起始時間
SQL> versions_endtime:? 結束時間
SQL> versions_xid:??? 事務號
SQL> */
SQL> set linesize 150
SQL> col versions_operation for a4
SQL> col versions_starttime for a25
SQL> col versions_endtime for a25
SQL> select empno,ename,versions_operation,versions_starttime,versions_endtime
? 2? from version_table
? 3? versions between timestamp minvalue and maxvalue
? 4? order by empno,versions_starttime;

???? EMPNO ENAME??????????????? VERS VERSIONS_STARTTIME??????? VERSIONS_ENDTIME??????????????????????????????????????????????????????????????????????
---------- -------------------- ---- ------------------------- -------------------------?????????????????????????????????????????????????????????????
???????? 1 Tom????????????????? I??? 15-6月 -11 02.57.27 下午????????????????????????????????????????????????????????????????????????????????????????
???????? 2 Mary???????????????? I??? 15-6月 -11 02.57.42 下午? 15-6月 -11 02.58.18 下午??????????????????????????????????????????????????????????????
???????? 2 Mary123????????????? U??? 15-6月 -11 02.58.18 下午????????????????????????????????????????????????????????????????????????????????????????
???????? 3 Mike???????????????? I??? 15-6月 -11 02.57.51 下午????????????????????????????????????????????????????????????????????????????????????????

SQL> select empno,ename,versions_operation,versions_starttime,versions_endtime
? 2? from version_table
? 3? versions between timestamp? to_date('2011-06-15 14:57:27','yyyy-mm-dd hh24:mi:ss') and to_date('2011-06-15 14:57:51','yyyy-mm-dd hh24:mi:ss')
? 4? order by empno,versions_starttime;

SQL> ed
已寫入 file afiedt.buf

? 1? select empno,ename,versions_operation,versions_starttime,versions_endtime
? 2? from version_table
? 3? versions between timestamp? to_date('2011-06-15 14:57:27','yyyy-mm-dd hh24:mi:ss') and to_date('2011-06-15 14:57:55','yyyy-mm-dd hh24:mi:ss')
? 4* order by empno,versions_starttime
SQL> /

SQL> host cls

SQL> --閃回事務查詢
SQL> desc flashback_transaction_query;
?名稱??????????????????????????????????????????????????????????????????????????????? 是否為空? 類型
?----------------------------------------------------------------------------------- -------- --------------------------------------------------------
?XID????????????????????????????????????????????????????????????????????????????????????????? RAW(8)
?START_SCN??????????????????????????????????????????????????????????????????????????????????? NUMBER
?START_TIMESTAMP????????????????????????????????????????????????????????????????????????????? DATE
?COMMIT_SCN?????????????????????????????????????????????????????????????????????????????????? NUMBER
?COMMIT_TIMESTAMP???????????????????????????????????????????????????????????????????????????? DATE
?LOGON_USER?????????????????????????????????????????????????????????????????????????????????? VARCHAR2(30)
?UNDO_CHANGE#???????????????????????????????????????????????????????????????????????????????? NUMBER
?OPERATION??????????????????????????????????????????????????????????????????????????????????? VARCHAR2(32)
?TABLE_NAME?????????????????????????????????????????????????????????????????????????????????? VARCHAR2(256)
?TABLE_OWNER????????????????????????????????????????????????????????????????????????????????? VARCHAR2(32)
?ROW_ID?????????????????????????????????????????????????????????????????????????????????????? VARCHAR2(19)
?UNDO_SQL???????????????????????????????????????????????????????????????????????????????????? VARCHAR2(4000)

SQL> conn / as sysdba
已連接。
SQL> grant select any transaction to scott;

授權成功。

SQL> conn scott/tiger
已連接。
SQL> host cls

SQL> create table transaction_table
? 2? (empno number,ename varchar(20));

表已創建。

SQL> insert into transaction_table values(1,'Tom');

已創建 1 行。

SQL> commit;

提交完成。

SQL> insert into transaction_table values(2,'Mary');

已創建 1 行。

SQL> insert into transaction_table values(3,'Mike');

已創建 1 行。

SQL> commit;

提交完成。

SQL> update transaction_table set ename='Mary123' where empno=2;

已更新 1 行。

SQL> delete from transaction_table where empno=3;

已刪除 1 行。

SQL> commit;

提交完成。

SQL> -- 首先通過閃回版本查詢得到相關的信息
SQL> select empno,ename,versions_operation,versions_starttime,versions_endtime,versions_xid
? 2? from transaction_table
? 3? versions between timestamp minvalue and maxvalue
? 4? order by empno,versions_starttime;

???? EMPNO ENAME??????????????? VERS VERSIONS_STARTTIME??????? VERSIONS_ENDTIME????????? VERSIONS_XID????????????????????????????????????????????????
---------- -------------------- ---- ------------------------- ------------------------- ----------------????????????????????????????????????????????
???????? 1 Tom????????????????? I??? 15-6月 -11 03.13.12 下午??????????????????????????? 010003004F020000????????????????????????????????????????????
???????? 2 Mary???????????????? I??? 15-6月 -11 03.13.36 下午? 15-6月 -11 03.14.12 下午? 08001000B3020000????????????????????????????????????????????
???????? 2 Mary123????????????? U??? 15-6月 -11 03.14.12 下午??????????????????????????? 04001A0068020000????????????????????????????????????????????
???????? 3 Mike???????????????? I??? 15-6月 -11 03.13.36 下午? 15-6月 -11 03.14.12 下午? 08001000B3020000????????????????????????????????????????????
???????? 3 Mike???????????????? D??? 15-6月 -11 03.14.12 下午??????????????????????????? 04001A0068020000????????????????????????????????????????????

SQL> select * from transaction_table;

???? EMPNO ENAME?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
---------- --------------------??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
???????? 1 Tom???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
???????? 2 Mary123???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????

SQL> select operation,table_name,undo_sql
? 2? from flashback_transaction_query
? 3? where xid='04001A0068020000';

OPERATION????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
--------------------------------?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
TABLE_NAME???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
------------------------------------------------------------------------------------------------------------------------------------------------------
UNDO_SQL?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
------------------------------------------------------------------------------------------------------------------------------------------------------
DELETE???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
TRANSACTION_TABLE????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
insert into "SCOTT"."TRANSACTION_TABLE"("EMPNO","ENAME") values ('3','Mike');????????????????????????????????????????????????????????????????????????
?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
UPDATE???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
TRANSACTION_TABLE????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
update "SCOTT"."TRANSACTION_TABLE" set "ENAME" = 'Mary' where ROWID = 'AAANIIAAEAAAAHAAAB';??????????????????????????????????????????????????????????

OPERATION????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
--------------------------------?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
TABLE_NAME???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
------------------------------------------------------------------------------------------------------------------------------------------------------
UNDO_SQL?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
------------------------------------------------------------------------------------------------------------------------------------------------------
?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
BEGIN????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????

SQL> col operation for a8
SQL> col table_name for a15
SQL> col undo_sql for a30
SQL> /

OPERATIO TABLE_NAME????? UNDO_SQL????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
-------- --------------- ------------------------------??????????????????????????????????????????????????????????????????????????????????????????????
DELETE?? TRANSACTION_TAB insert into "SCOTT"."TRANSACTI??????????????????????????????????????????????????????????????????????????????????????????????
???????? LE????????????? ON_TABLE"("EMPNO","ENAME") val??????????????????????????????????????????????????????????????????????????????????????????????
???????????????????????? ues ('3','Mike');???????????????????????????????????????????????????????????????????????????????????????????????????????????
?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
UPDATE?? TRANSACTION_TAB update "SCOTT"."TRANSACTION_TA??????????????????????????????????????????????????????????????????????????????????????????????
???????? LE????????????? BLE" set "ENAME" = 'Mary' wher??????????????????????????????????????????????????????????????????????????????????????????????
???????????????????????? e ROWID = 'AAANIIAAEAAAAHAAAB'??????????????????????????????????????????????????????????????????????????????????????????????
???????????????????????? ;???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
BEGIN????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????

SQL> col table_name for a20
SQL> /

OPERATIO TABLE_NAME?????????? UNDO_SQL???????????????????????????????????????????????????????????????????????????????????????????????????????????????
-------- -------------------- ------------------------------?????????????????????????????????????????????????????????????????????????????????????????
DELETE?? TRANSACTION_TABLE??? insert into "SCOTT"."TRANSACTI?????????????????????????????????????????????????????????????????????????????????????????
????????????????????????????? ON_TABLE"("EMPNO","ENAME") val?????????????????????????????????????????????????????????????????????????????????????????
????????????????????????????? ues ('3','Mike');??????????????????????????????????????????????????????????????????????????????????????????????????????
?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
UPDATE?? TRANSACTION_TABLE??? update "SCOTT"."TRANSACTION_TA?????????????????????????????????????????????????????????????????????????????????????????
????????????????????????????? BLE" set "ENAME" = 'Mary' wher?????????????????????????????????????????????????????????????????????????????????????????
????????????????????????????? e ROWID = 'AAANIIAAEAAAAHAAAB'?????????????????????????????????????????????????????????????????????????????????????????
????????????????????????????? ;??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
BEGIN????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????

SQL>? insert into "SCOTT"."TRANSACTION_TABLE"("EMPNO","ENAME") values ('3','Mike');

已創建 1 行。

SQL>? update "SCOTT"."TRANSACTION_TABLE" set "ENAME" = 'Mary' where ROWID = 'AAANIIAAEAAAAHAAAB';

已更新 1 行。

SQL> commit;

提交完成。

SQL> select * from TRANSACTION_TABLE;

???? EMPNO ENAME?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
---------- --------------------??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
???????? 1 Tom???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
???????? 2 Mary??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
???????? 3 Mike??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????

SQL> spool off

總結

以上是生活随笔為你收集整理的Oracle：闪回，闪回表，闪回删除，闪回版本查询，闪回事务查询的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇：质保15年的金高丽粉与附着力有关吗
下一篇：新手司机，请问怎么才知道自己的机油该不该