使用外部表儲存查詢結果

可以使用外部錶快速將查詢結果從生產庫匯入到測試庫。
1，從生產庫匯出
SQL> select * from dba_directories;

DIRECTORY_NAME    DIRECTORY_PATH
--------------------------------------------
       TTBSDIR    /u01/app/oracle/ttbs

SQL> create table dba_objects_ext
  2  organization external
  3  (type oracle_datapump
  4   default directory workdir
  5   location('dba_object1.exp','dba_object2.exp'))
  6  parallel 2 reject limit unlimited
  7  as
  8  select * from dba_objects;
注：這裡可以是複雜查詢；因為使用並行，可以匯出大量資料

Table created.
SQL> desc dba_objects
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                          NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)

[oracle@rac1 workdir]$ ls -lrt
-rw-r--r-- 1 oracle oinstall      41 Jul  9 20:35 DBA_OBJECTS_EXT_7321.log
-rw-r--r-- 1 oracle oinstall      41 Jul  9 20:35 DBA_OBJECTS_EXT_14988.log
-rw-r--r-- 1 oracle oinstall      41 Jul  9 20:35 DBA_OBJECTS_EXT_14986.log
-rw-r----- 1 oracle oinstall 2482176 Jul  9 20:35 dba_object2.exp
-rw-r----- 1 oracle oinstall 2482176 Jul  9 20:35 dba_object1.exp

2，在測試庫匯入
在把匯出檔案傳輸到測試庫後：

[oracle@rac1 ~]$ env|grep ORA
ORACLE_SID=emrep
ORACLE_BASE=/u01/app
ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Mon Jul 9 20:37:31 2012

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select * from dba_directories;

DIRECTORY_NAME    DIRECTORY_PATH
--------------------------------------------
       TTBSDIR    /u01/app/oracle/ttbs

SQL> create table dba_objects_ext
( OWNER                                              VARCHAR2(30)
, OBJECT_NAME                                        VARCHAR2(128)
, SUBOBJECT_NAME                                     VARCHAR2(30)
, OBJECT_ID                                          NUMBER
, DATA_OBJECT_ID                                     NUMBER
, OBJECT_TYPE                                        VARCHAR2(19)
, CREATED                                            DATE
, LAST_DDL_TIME                                      DATE
, TIMESTAMP                                          VARCHAR2(19)
, STATUS                                             VARCHAR2(7)
, TEMPORARY                                          VARCHAR2(1)
, GENERATED                                          VARCHAR2(1)
, SECONDARY                                          VARCHAR2(1)
)
organization external
(type oracle_datapump
 default directory workdir
 location ('dba_object1.exp','dba_object2.exp'))
;
Table created.
SQL> select count(*) from dba_objects_ext;

  COUNT(*)
----------
     50527