oracle 常用的闪回操作

来源：互联网发布：ipad怎么开淘宝店铺编辑：程序博客网时间：2024/06/05 06:04
flashback可操作类型Flashback table Flashback database Flashback query (version + trancation)闪回表 Flashback table  ：SQL> show  parameter recyclebinNAME     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   TABLEEMP   TABLEBONUS   TABLESALGRADE   TABLEBIN$CBvugscIwNbgUAoK3ggO9Q==$0   TABLET2   TABLE或者是show recycle; 查看回收站.SQL> show  recyclebinORIGINAL NAME RECYCLEBIN NAMEOBJECT 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-01555undo_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                    20SQL> 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=30002.闪回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:3612 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=30003 闪回版本查询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    80015 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_TIMESTAMPOPERATION---------------- ---------- ------------------- ------------------------------------------------------------------------------------------------UNDO_SQL--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------0300130022060000 4.9734E+10 2014-11-18 15:36:32 UPDATEupdate "SCOTT"."T1" set "SAL" = '3000' where ROWID = 'AAAOa6AAEAAAAA8AAH';
0 0