ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle9i New Features-flashback query

oracle9i New Features-flashback query

原创 Linux操作系统 作者:xhailiang 时间:2006-10-18 00:00:00 0 删除 编辑

Flashback query lets you view and repair historical data. You can perform queries on the database as of a certain wall clock time or user-specified system commit number (SCN).


Notes:
  • Flashback query does not undo anything. It is only a query mechanism. You can take the output from a flashback query and perform an undo yourself in many circumstances.
  • Flashback query does not tell you what changed. LogMiner does that.
  • Flashback query can be used to undo changes and can be very efficient if you know the rows that need to be moved back in time. You can in theory use it to move a full table back in time but this is very expensive if the table is large since it involves a full table copy.
  • Flashback query does not work through DDL operations that modify columns, or drop or truncate tables.
  • LogMiner is very good for getting change history, but it gives you changes in terms of deltas (insert, update, delete), not in terms of the before and after image of a row. These can be difficult to deal with in some applications.

select * from table_name as of time / scn timestampe to_date('2006-10-18 22:01:13','yyyy-mm-dd hh24:min:ss') /&scn;


SQL> show parameter undo_re

NAME TYPE VALUE
------------------------------------ ----------- ------
undo_retention integer 10800

SQL> create table hr.flash as select * from hr.jobs;

SQL> SELECT * FROM HR.FLASH;

JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
AD_PRES President 20000 40000
AD_VP Administration Vice President 1444 30000
AD_ASST Administration Assistant 3000 6000
FI_MGR Finance Manager 8200 16000
FI_ACCOUNT Accountant 4200 9000
AC_MGR Accounting Manager 8200 16000
AC_ACCOUNT Public Accountant 4200 9000
SA_MAN Sales Manager 10000 20000
SA_REP Sales Representative 6000 12000
PU_MAN Purchasing Manager 8000 15000
PU_CLERK Purchasing Clerk 2500 5500

JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
ST_MAN Stock Manager 5500 8500
ST_CLERK Stock Clerk 2000 5000
SH_CLERK Shipping Clerk 2500 5500
IT_PROG Programmer 4000 10000
MK_MAN Marketing Manager 9000 15000
MK_REP Marketing Representative 4000 9000
HR_REP Human Resources Representative 4000 9000
PR_REP Public Relations Representative 4500 10500

已选择19行。

SQL> SELECT * FROM HR.FLASH WHERE MIN_SALARY>8000;

JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
AD_PRES President 20000 40000
FI_MGR Finance Manager 8200 16000
AC_MGR Accounting Manager 8200 16000
SA_MAN Sales Manager 10000 20000
MK_MAN Marketing Manager 9000 15000


SQL> delete from hr.FLASH where min_salary>8000;

已删除5行。

SQL>commit;

提交完成。

SQL>SELECT SYSDATE FROM DUAL

2006-10-18 22:13:21


SQL> SELECT * FROM HR.FLASH WHERE MIN_SALARY>8000;

未选定行

SQL> select * from hr.FLASH as of timestamp to_date('2006-10-18 22:12:13','yyyy-mm-dd hh24:mi:ss') 2 /

JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
AD_PRES President 20000 40000
AD_VP Administration Vice President 1444 30000
AD_ASST Administration Assistant 3000 6000
FI_MGR Finance Manager 8200 16000
FI_ACCOUNT Accountant 4200 9000
AC_MGR Accounting Manager 8200 16000
AC_ACCOUNT Public Accountant 4200 9000
SA_MAN Sales Manager 10000 20000
SA_REP Sales Representative 6000 12000
PU_MAN Purchasing Manager 8000 15000
PU_CLERK Purchasing Clerk 2500 5500

JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
ST_MAN Stock Manager 5500 8500
ST_CLERK Stock Clerk 2000 5000
SH_CLERK Shipping Clerk 2500 5500
IT_PROG Programmer 4000 10000
MK_MAN Marketing Manager 9000 15000
MK_REP Marketing Representative 4000 9000
HR_REP Human Resources Representative 4000 9000
PR_REP Public Relations Representative 4500 10500

已选择19行。

到了这一步就简单了,先把数据INSERT到一张临时表中,然后根据你要的数据加个WHERE 条件再INSERT到真实表中就OK了.

USER不小心删除数据是不好的,但删除了数据又担心被骂而不敢及时上报的更不好.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/308563/viewspace-171904/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-02-17

  • 博文量
    270
  • 访问量
    414550