Oracle：闪回

来源：互联网发布：淘宝成交记录没有了编辑：程序博客网时间：2024/05/01 22:22

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> flashback table flashback_table to scn 1267969;
flashback table flashback_table to scn 1267969
*
第 1 行出现错误:
ORA-08189: 因为未启用行移动功能, 不能闪回表

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;

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

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;
flashback table test to before drop
*
第 1 行出现错误:
ORA-38312: 原始名称已被现有对象使用

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;

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

已选择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';

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