ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 使用外部表保存查询结果

使用外部表保存查询结果

原创 Linux操作系统 作者:redhouser 时间:2012-07-10 10:16:51 0 删除 编辑


可以使用外部表快速将查询结果从生产库导入到测试库。
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

 

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

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

注册时间:2011-05-26

  • 博文量
    211
  • 访问量
    786839