flashback可操作类型
Flashback table
Flashback database
Flashback query (version + trancation)
闪回表 Flashback table :
SQL> show parameter recyclebin
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
recyclebin string on
如果要打开回收站 (立刻生效):
alter system set recyclebin=off scope=spfile;
alter session set recyclebin=off scope=spfile;
SQL> drop table t1;
SQL> select * From tab;
TNAME TABTYPE CLUSTERID
------------------------------------------------------------------------------------------ --------------------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
BIN$CBvugscIwNbgUAoK3ggO9Q==$0 TABLE
T2 TABLE
或者是
show recycle; 查看回收站.
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T1 BIN$CBvugscIwNbgUAoK3ggO9Q==$0 TABLE 2014-11-18:14:09:02
我们可以看到t1表在回收站中,如果我们对这个表不用,可以用
purge recyclbin;
如果是无意删除可以通过闪回表,找会此表
flashback table t1 to before drop;
flashback table t1 to before drop rename to t7;
flashback table t4 to before drop rename to t7; 新改名不用加引号.
flashback table "BIN$CBvugscIwNbgUAoK3ggO9Q==$0" to before drop;
Flashback query (version + trancation)
闪查的数据来自于回滚段 如果空间太小,闪查的维持时间就短,同时太小会有ORA-01555
undo_retention=n(秒)参数就是数据维持在ROLLBACK_segment的不强制保持时间
因为要做DML 就要先申请rollback空间 无法申请就无法继续做下去
为了保持新事务正常运行 oracle默认采用了undo_retention不强制保持时间
就是即使时间不到,因空间不足也可以备覆盖重用
闪查种类可分为
1.闪回SCN或时间查询
2.闪回事务查询
3.闪回版本查询
1.闪回时间查询
SQL> create table t1 as select * From emp;
SQL> select * From t1 where ename='SCOTT';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------------------ --------------------------- ---------- -------------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
SQL> UPDATE T1 SET SAL=SAL+1 WHERE ENAME='SCOTT';
SQL> COMMIT;
SQL> select * from t1 as of timestamp sysdate-17/1440 where ename='SCOTT';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------------------ --------------------------- ---------- -------------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
在此处我们可以看到数据被修改之前 SAL=3000
2.闪回scn时间查询
在使用oralce闪回特性恢复数据时,有时需要知道SCN与时间的对应关系,该对应关系可以通过SMON_SCN_TIME视图得到
oracle数据库会每间隔5分钟向该表中写入一条数据,这个可以从上面查询到的结果中看出来
SQL> select scn,to_char(time_dp,'yyyy-mm-dd hh24:mi:ss') time from sys.smon_scn_time where time_dp like '2014-11-18 15%' ;
SCN TIME
--------------- ---------------------------------------------------------
49733519167 2014-11-18 15:51:38
49733519333 2014-11-18 15:56:43
49733518113 2014-11-18 15:19:49
49733518272 2014-11-18 15:24:56
49733518431 2014-11-18 15:30:04
49733518680 2014-11-18 15:36:11
49733518816 2014-11-18 15:41:18
49733518972 2014-11-18 15:46:27
49733517608 2014-11-18 15:04:35
49733517742 2014-11-18 15:09:42
49733517978 2014-11-18 15:14:46
49733518633 2014-11-18 15:35:36
12 rows selected.
SQL> select * from t1 as of scn 49733518680 where ename='SCOTT';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------------------ --------------------------- ---------- -------------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
在此处我们可以看到数据被修改之前 SAL=3000
3 闪回版本查询
SQL> select versions_starttime, versions_endtime, versions_xid, versions_operation,ename,sal
2 from scott.t1 versions between timestamp to_timestamp('2014-11-18 15:36:00', 'YYYY-MM-DD HH24:MI:SS') and maxvalue order by VERSIONS_STARTTIME ;
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID VER ENAME SAL
-------------------- --------------------------------------------------------------------------- ---------------- --- ------------------------------ ----------
18-11月-14 03.36.32 0300130022060000 U SCOTT 3001
下午
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
18-11月-14 03.36.32 下午 SCOTT 3000
KING 5000
TURNER 1500
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300
SMITH 800
15 rows selected.
4 闪回事物查询
SQL> select xid,COMMIT_SCN,COMMIT_TIMESTAMP,OPERATION,UNDO_SQL
2 from flashback_transaction_query
3 where TABLE_NAME='T1' and TABLE_OWNER='SCOTT' and COMMIT_SCN >= 49733518680 and COMMIT_SCN <= 49733519680;
XID COMMIT_SCN COMMIT_TIMESTAMP OPERATION
---------------- ---------- ------------------- ------------------------------------------------------------------------------------------------
UNDO_SQL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0300130022060000 4.9734E+10 2014-11-18 15:36:32 UPDATE
update "SCOTT"."T1" set "SAL" = '3000' where ROWID = 'AAAOa6AAEAAAAA8AAH';
oracle 常用的闪回操作
最新推荐文章于 2018-11-20 09:54:17 发布