在Oracle Database 10g開始,透過使用flashback table語句快速恢復表,也不會影響其他表空間.

如下面操作:

C:\>sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 3月 17 16:37:59 2008

連線到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> archive log list;
資料庫日誌模式            存檔模式
自動存檔             啟用
存檔終點            USE_DB_RECOVERY_FILE_DEST
最早的聯機日誌序列     63
下一個存檔日誌序列   65
當前日誌序列           65
SQL> connect scott/tiger
已連線。
SQL> desc user_tables;
名稱                                      是否為空? 型別
----------------------------------------- -------- ----------------------------

TABLE_NAME                                NOT NULL VARCHAR2(30)
TABLESPACE_NAME                                    VARCHAR2(30)
CLUSTER_NAME                                       VARCHAR2(30)
IOT_NAME                                           VARCHAR2(30)
STATUS                                             VARCHAR2(8)
PCT_FREE                                           NUMBER
PCT_USED                                           NUMBER
INI_TRANS                                          NUMBER
MAX_TRANS                                          NUMBER
INITIAL_EXTENT                                     NUMBER
NEXT_EXTENT                                        NUMBER
MIN_EXTENTS                                        NUMBER
MAX_EXTENTS                                        NUMBER
PCT_INCREASE                                       NUMBER
FREELISTS                                          NUMBER
FREELIST_GROUPS                                    NUMBER
LOGGING                                            VARCHAR2(3)
BACKED_UP                                          VARCHAR2(1)
NUM_ROWS                                           NUMBER
BLOCKS                                             NUMBER
EMPTY_BLOCKS                                       NUMBER
AVG_SPACE                                          NUMBER
CHAIN_CNT                                          NUMBER
AVG_ROW_LEN                                        NUMBER
AVG_SPACE_FREELIST_BLOCKS                          NUMBER
NUM_FREELIST_BLOCKS                                NUMBER
DEGREE                                             VARCHAR2(10)
INSTANCES                                          VARCHAR2(10)
CACHE                                              VARCHAR2(5)
TABLE_LOCK                                         VARCHAR2(8)
SAMPLE_SIZE                                        NUMBER
LAST_ANALYZED                                      DATE
PARTITIONED                                        VARCHAR2(3)
IOT_TYPE                                           VARCHAR2(12)
TEMPORARY                                          VARCHAR2(1)
SECONDARY                                          VARCHAR2(1)
NESTED                                             VARCHAR2(3)
BUFFER_POOL                                        VARCHAR2(7)
ROW_MOVEMENT                                       VARCHAR2(8)
GLOBAL_STATS                                       VARCHAR2(3)
USER_STATS                                         VARCHAR2(3)
DURATION                                           VARCHAR2(15)
SKIP_CORRUPT                                       VARCHAR2(8)
MONITORING                                         VARCHAR2(3)
CLUSTER_OWNER                                      VARCHAR2(30)
DEPENDENCIES                                       VARCHAR2(8)
COMPRESSION                                        VARCHAR2(8)
DROPPED                                            VARCHAR2(3)

SQL> select table_name,tablespace_name from user_tables;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
DEPT                           USERS
EMP                            USERS
BONUS                          USERS
SALGRADE                       USERS

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK           7902 17-12月-80            800
        20

7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300
30

7521 WARD SALESMAN 7698 22-2月 -81 1250 500
30

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      7566 JONES      MANAGER         7839 02-4月 -81           2975
        20

7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400
30

7698 BLAKE MANAGER 7839 01-5月 -81 2850
30

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      7782 CLARK      MANAGER         7839 09-6月 -81           2450
        10

7788 SCOTT ANALYST 7566 19-4月 -87 3000
20

7839 KING PRESIDENT 17-11月-81 5000
10

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0
        30

7876 ADAMS CLERK 7788 23-5月 -87 1100
20

7900 JAMES CLERK 7698 03-12月-81 950
30

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      7902 FORD       ANALYST         7566 03-12月-81           3000
        20

7934 MILLER CLERK 7782 23-1月 -82 1300
10

已選擇14行。

SQL> drop table emp;

表已刪除。

SQL> commit;

提交完成。

SQL> desc emp;
ERROR:
ORA-04043: 物件 emp 不存在

SQL> flashback table emp to before drop;

閃回完成。

SQL> desc emp;
名稱是否為空? 型別
----------------------------------------- -------- ----------------------------

EMPNO                                     NOT NULL NUMBER(4)
ENAME                                              VARCHAR2(10)
JOB                                                VARCHAR2(9)
MGR                                                NUMBER(4)
HIREDATE                                           DATE
SAL                                                NUMBER(7,2)
COMM                                               NUMBER(7,2)
DEPTNO                                             NUMBER(2)