--闪回到15分钟前 select * from orders as of timestamp (systimestamp - interval ''15'' minute) where ...... 这里可以使用DAY、SECOND、MONTH替换minute,例如: SELECT * FROM orders AS OF TIMESTAMP(SYSTIMESTAMP - INTERVAL ''2'' DAY) --闪回到某个时间点 select * from orders as of timestamp to_timestamp (''01-Sep-04 16:18:57.845993'', ''DD-Mon-RR HH24:MI:SS.FF'') where ...
--闪回到两天前 select * from orders as of timestamp (sysdate - 2) where.........
/*2.FLASHBACK DROP*/
1.flashback table orders to before drop;
2.如果源表已经重建,可以使用rename to子句: flashback table order to before drop rename to order_old_version;
/*3.FLASHBACK TABLE*/
1.首先要启用行迁移: alter table order enable row movement; 2.闪回表到15分钟前: flashback table order to timestamp systimestamp - interval ''15'' minute; 闪回到某个时间点: FLASHBACK TABLE order TO TIMESTAMP TO_TIMESTAMP('2007-09-12 01:15:25 PM','YYYY-MM-DD HH:MI:SS AM')
我在昨天把这一行记录的comm改成了1 7844 TURNER SALESMAN 7698 08-SEP-81 1500 1 30 我现在要闪回前天为0时的状态, 既可以按时间也可以按scn号闪回现在给一个按时间闪回的例子,楼主看看就明白了: SQL> select * from scott.emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ------------ ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 1 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 1014 rows selected.SQL> call dbms_flashback.enable_at_time(to_date('2009/02/02:12:00','yyyy/mm/dd:hh24:mi:ss'));Call completed.SQL> select * from scott.emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ------------ ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 1014 rows selected.SQL> call dbms_flashback.disable();Call completed.SQL> select * from scott.emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ------------ ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 1 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 1014 rows selected.SQL>
shutdown immediate; startup mount; flashback database to timestamp to_timestamp('2009-02-04 16:00:00','yyyy-mm-dd hh24:mi:ss'); alter database open resetlogs
ERROR at line 1: ORA-03113: end-of-file on communication channel 为什么会有这个提示
--闪回到15分钟前
select * from orders as of timestamp (systimestamp - interval ''15'' minute) where ......
这里可以使用DAY、SECOND、MONTH替换minute,例如:
SELECT * FROM orders AS OF TIMESTAMP(SYSTIMESTAMP - INTERVAL ''2'' DAY) --闪回到某个时间点
select * from orders as of timestamp to_timestamp (''01-Sep-04 16:18:57.845993'', ''DD-Mon-RR HH24:MI:SS.FF'') where ...
--闪回到两天前
select * from orders as of timestamp (sysdate - 2) where.........
/*2.FLASHBACK DROP*/
1.flashback table orders to before drop;
2.如果源表已经重建,可以使用rename to子句:
flashback table order to before drop rename to order_old_version;
/*3.FLASHBACK TABLE*/
1.首先要启用行迁移:
alter table order enable row movement;
2.闪回表到15分钟前:
flashback table order to timestamp systimestamp - interval ''15'' minute;
闪回到某个时间点:
FLASHBACK TABLE order TO TIMESTAMP TO_TIMESTAMP('2007-09-12 01:15:25 PM','YYYY-MM-DD HH:MI:SS AM')
7844 TURNER SALESMAN 7698 08-SEP-81 1500 1 30
我现在要闪回前天为0时的状态,
既可以按时间也可以按scn号闪回现在给一个按时间闪回的例子,楼主看看就明白了: SQL> select * from scott.emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 1 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 1014 rows selected.SQL> call dbms_flashback.enable_at_time(to_date('2009/02/02:12:00','yyyy/mm/dd:hh24:mi:ss'));Call completed.SQL> select * from scott.emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 1014 rows selected.SQL> call dbms_flashback.disable();Call completed.SQL> select * from scott.emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 1 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 1014 rows selected.SQL>
http://dev.csdn.net/article/82/82252.shtm
-rw-r----- 1 oracle oinstall 43450880 Feb 04 12:23 archive_log1_2407_651743363.dbf
-rw-r----- 1 oracle oinstall 43450880 Feb 04 12:25 archive_log1_2408_651743363.dbf
-rw-r----- 1 oracle oinstall 43450880 Feb 04 12:27 archive_log1_2409_651743363.dbf
-rw-r----- 1 oracle oinstall 43450880 Feb 04 12:29 archive_log1_2410_651743363.dbf
-rw-r----- 1 oracle oinstall 43463168 Feb 04 12:52 archive_log1_2411_651743363.dbf
-rw-r----- 1 oracle oinstall 43450880 Feb 04 12:53 archive_log1_2412_651743363.dbf
-rw-r----- 1 oracle oinstall 43450880 Feb 04 12:54 archive_log1_2413_651743363.dbf
-rw-r----- 1 oracle oinstall 43450880 Feb 04 13:01 archive_log1_2414_651743363.dbf
-rw-r----- 1 oracle oinstall 43473920 Feb 04 14:39 archive_log1_2415_651743363.dbf
-rw-r----- 1 oracle oinstall 43490816 Feb 04 15:05 archive_log1_2416_651743363.dbf
-rw-r----- 1 oracle oinstall 43450880 Feb 04 15:06 archive_log1_2417_651743363.dbf
-rw-r----- 1 oracle oinstall 43450880 Feb 04 15:07 archive_log1_2418_651743363.dbf
-rw-r----- 1 oracle oinstall 43450880 Feb 04 15:09 archive_log1_2419_651743363.dbf
-rw-r----- 1 oracle oinstall 43450880 Feb 04 15:09 archive_log1_2420_651743363.dbf
-rw-r----- 1 oracle oinstall 43450880 Feb 04 15:11 archive_log1_2421_651743363.dbf
-rw-r----- 1 oracle oinstall 43451904 Feb 04 15:13 archive_log1_2422_651743363.dbf
-rw-r----- 1 oracle oinstall 43450880 Feb 04 15:15 archive_log1_2423_651743363.dbf
-rw-r----- 1 oracle oinstall 43471872 Feb 04 15:17 archive_log1_2424_651743363.dbf
-rw-r----- 1 oracle oinstall 43450880 Feb 04 15:18 archive_log1_2425_651743363.dbf
-rw-r----- 1 oracle oinstall 43450880 Feb 04 15:20 archive_log1_2426_651743363.dbf
-rw-r----- 1 oracle oinstall 43450880 Feb 04 15:22 archive_log1_2427_651743363.dbf
-rw-r----- 1 oracle oinstall 43450880 Feb 04 15:24 archive_log1_2428_651743363.dbf
-rw-r----- 1 oracle oinstall 43450368 Feb 04 15:25 archive_log1_2429_651743363.dbf
-rw-r----- 1 oracle oinstall 43450880 Feb 04 15:27 archive_log1_2430_651743363.dbf
-rw-r----- 1 oracle oinstall 43450880 Feb 04 15:28 archive_log1_2431_651743363.dbf
-rw-r----- 1 oracle oinstall 43450880 Feb 04 15:29 archive_log1_2432_651743363.dbf
-rw-r----- 1 oracle oinstall 43448320 Feb 04 15:32 archive_log1_2433_651743363.dbf
-rw-r----- 1 oracle oinstall 43444736 Feb 04 15:34 archive_log1_2434_651743363.dbf
-rw-r----- 1 oracle oinstall 43450880 Feb 04 15:38 archive_log1_2435_651743363.dbf
-rw-r----- 1 oracle oinstall 43450880 Feb 04 15:39 archive_log1_2436_651743363.dbf
-rw-r----- 1 oracle oinstall 43450880 Feb 04 15:41 archive_log1_2437_651743363.dbf
-rw-r----- 1 oracle oinstall 43449856 Feb 04 15:44 archive_log1_2438_651743363.dbf
-rw-r----- 1 oracle oinstall 43458560 Feb 04 15:47 archive_log1_2439_651743363.dbf
-rw-r----- 1 oracle oinstall 43450880 Feb 04 15:49 archive_log1_2440_651743363.dbf
-rw-r----- 1 oracle oinstall 43454464 Feb 04 15:52 archive_log1_2441_651743363.dbf
-rw-r----- 1 oracle oinstall 43448832 Feb 04 15:55 archive_log1_2442_651743363.dbf
-rw-r----- 1 oracle oinstall 45694464 Feb 04 15:58 archive_log1_2443_651743363.dbf
-rw-r----- 1 oracle oinstall 43450368 Feb 04 16:00 archive_log1_2444_651743363.dbf
-rw-r----- 1 oracle oinstall 43450880 Feb 04 16:04 archive_log1_2445_651743363.dbf
-rw-r----- 1 oracle oinstall 43450880 Feb 04 16:05 archive_log1_2446_651743363.dbf
-rw-r----- 1 oracle oinstall 43450880 Feb 04 16:07 archive_log1_2447_651743363.dbf
-rw-r----- 1 oracle oinstall 43535360 Feb 04 16:08 archive_log1_2448_651743363.dbf
-rw-r----- 1 oracle oinstall 43450880 Feb 04 16:09 archive_log1_2449_651743363.dbf
-rw-r----- 1 oracle oinstall 43450368 Feb 04 16:13 archive_log1_2450_651743363.dbf
-rw-r----- 1 oracle oinstall 43450368 Feb 04 16:15 archive_log1_2451_651743363.dbf
-rw-r----- 1 oracle oinstall 43447808 Feb 04 16:17 archive_log1_2452_651743363.dbf
-rw-r----- 1 oracle oinstall 43450880 Feb 04 16:20 archive_log1_2453_651743363.dbf
-rw-r----- 1 oracle oinstall 44668928 Feb 04 22:00 archive_log1_2454_651743363.dbf
-rw-r----- 1 oracle oinstall 43454976 Feb 04 22:01 archive_log1_2455_651743363.dbf
-rw-r----- 1 oracle oinstall 43450880 Feb 04 22:02 archive_log1_2456_651743363.dbf
-rw-r----- 1 oracle oinstall 43450880 Feb 04 22:03 archive_log1_2457_651743363.dbf
我想闪回到12:30
SQL> select * from scott.emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7566 JONES MANAGER 7839 02-APR-81 2975
20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30 7698 BLAKE MANAGER 7839 01-MAY-81 2850
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
10 7788 SCOTT ANALYST 7566 19-APR-87 3000
20 7839 KING PRESIDENT 17-NOV-81 5000
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30 7876 ADAMS CLERK 7788 23-MAY-87 1100
20 7900 JAMES CLERK 7698 03-DEC-81 950
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 03-DEC-81 3000
20 7934 MILLER CLERK 7782 23-JAN-82 1300
10
14 rows selected.SQL> select * from scott.emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7566 JONES MANAGER 7839 02-APR-81 2975
20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30 7698 BLAKE MANAGER 7839 01-MAY-81 2850
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
10 7788 SCOTT ANALYST 7566 19-APR-87 3000
20 7839 KING PRESIDENT 17-NOV-81 5000
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30 7876 ADAMS CLERK 7788 23-MAY-87 1100
20 7900 JAMES CLERK 7698 03-DEC-81 950
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 03-DEC-81 3000
20 7934 MILLER CLERK 7782 23-JAN-82 1300
10
14 rows selected.
那我能还原到昨天12点前吗?
是不是这句就可以了.
为什么你日期那里前后不符
'2009/02/02:12:00','yyyy/mm/dd:hh24:mi:ss'
startup mount;
flashback database to timestamp to_timestamp('2009-02-04 16:00:00','yyyy-mm-dd hh24:mi:ss');
alter database open resetlogs
ORA-03113: end-of-file on communication channel
为什么会有这个提示