Oracle的导入导出工具EXP/IMP由来已久,大部分人对于使用这两个工具也都不陌生。Oracle10g开始推出的数据泵EXPDP/IMPDP,提供了导入导出的性能,也增加了导入导出的功能,但是由于使用时间不长,可能部分人对这个工具不太了解。一直打算写几篇文章,简单描述一下EXP/IMP和EXPDP/IMPDP的差异。
对比数据泵与原始导入导出工具(一):http://yangtingkun.itpub.net/post/468/476017
对比数据泵与原始导入导出工具(二):http://yangtingkun.itpub.net/post/468/476060
对比数据泵与原始导入导出工具(三):http://yangtingkun.itpub.net/post/468/477335
对比数据泵与原始导入导出工具(四):http://yangtingkun.itpub.net/post/468/477380
对比数据泵与原始导入导出工具(五):http://yangtingkun.itpub.net/post/468/477633
对比数据泵与原始导入导出工具(六):http://yangtingkun.itpub.net/post/468/504239
这篇描述数据泵的SQLFILE参数。
Oracle的exp提供了INDEXFILE命令,可以将索引和表的创建脚本输出到文件:
[oracle@bjtest ~]$ exp yangtk/yangtk file=yangtk.dmp
Export: Release 11.2.0.1.0 - Production on 星期四 8月 26 01:10:55 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
即将导出指定的用户...
. 正在导出 pre-schema 过程对象和操作
. 正在导出用户 YANGTK 的外部函数库名
. 导出 PUBLIC 类型同义词
. 正在导出专用类型同义词
. 正在导出用户 YANGTK 的对象类型定义
即将导出 YANGTK 的对象...
. 正在导出数据库链接
. 正在导出序号
. 正在导出簇定义
. 即将导出 YANGTK 的表通过常规路径...
. . 正在导出表 MV_UNIONALL导出了 25 行
. . 正在导出表 T导出了 11 行
. . 正在导出表 T_DOUBLE_IND导出了 85995 行
.
.
.
. 正在导出 post-schema 过程对象和操作
. 正在导出统计信息
成功终止导出, 没有出现警告。
[oracle@bjtest ~]$ imp yangtk/yangtk file=yangtk.dmp indexfile=yangtk_imp.sql full=y
Import: Release 11.2.0.1.0 - Production on 星期四 8月 26 01:11:51 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
经由常规路径由 EXPORT:V11.02.00 创建的导出文件
已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入
. . 正在跳过表 "MV_UNIONALL"
. . 正在跳过表 "T"
. . 正在跳过表 "T_DOUBLE_IND"
. . 正在跳过表 "T_EXP"
. . 正在跳过表 "T_ID"
. . 正在跳过表 "T_LOG"
. . 正在跳过表 "T_NEW"
. . 正在跳过分区 "T_PART_LIST":"P1"
. . 正在跳过分区 "T_PART_LIST":"P2"
. . 正在跳过分区 "T_PART_LIST":"P4"
. . 正在跳过表 "T_RECORD"
成功终止导入, 没有出现警告。
[oracle@bjtest ~]$ more yangtk_imp.sql
REM CREATE TABLE "YANGTK"."MV_UNIONALL" ("ROW_ID" ROWID, "ID" NUMBER,
REM "NAME" VARCHAR2(30), "FLAG" CHAR(1)) PCTFREE 10 PCTUSED 40 INITRANS 1
REM MAXTRANS 255 STORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1
REM FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE
REM "YANGTK" LOGGING NOCOMPRESS ;
REM ... 25 rows
REM CREATE TABLE "YANGTK"."T" ("ID" NUMBER, "NAME" VARCHAR2(30)) PCTFREE
REM 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 131072 NEXT
REM 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
REM DEFAULT) TABLESPACE "YANGTK" LOGGING NOCOMPRESS ;
REM ... 11 rows
CONNECT YANGTK;
CREATE INDEX "YANGTK"."IND_T_ID" ON "T" ("ID" ) PCTFREE 10 INITRANS 2
MAXTRANS 255 STORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 FREELISTS 1
FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "YANGTK" LOGGING ;
REM ALTER TABLE "YANGTK"."T" ADD CHECK (id > 0) ENABLE ;
REM ALTER TABLE "YANGTK"."T" ADD CHECK (id > 0) ENABLE ;
REM CREATE TABLE "YANGTK"."T_DOUBLE_IND" ("ID" NUMBER, "NAME"
REM VARCHAR2(30), "TYPE" VARCHAR2(30), "CONTENTS" VARCHAR2(4000)) PCTFREE
REM 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 109051904 NEXT
REM 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
REM DEFAULT) TABLESPACE "YANGTK" LOGGING NOCOMPRESS ;
REM ... 85995 rows
CREATE INDEX "YANGTK"."IND_DOUBLE_NAME" ON "T_DOUBLE_IND" ("NAME" )
PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 5242880 NEXT 1048576
MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE
"YANGTK" LOGGING ;
CREATE INDEX "YANGTK"."IND_DOUBLE_TYPE" ON "T_DOUBLE_IND" ("TYPE" )
PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 3145728 NEXT 1048576
MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE
"YANGTK" LOGGING ;
REM CREATE TABLE "YANGTK"."T_EXP" ("ID" NUMBER, "NAME" VARCHAR2(30))
REM PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 131072
.
.
.
REM CREATE TABLE "YANGTK"."T_RECORD" ("STR" VARCHAR2(30), "TIME" DATE)
REM PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 131072
REM NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
REM DEFAULT) TABLESPACE "YANGTK" LOGGING NOCOMPRESS ;
REM ... 3 rows
而在数据泵中,SQLFILE参数可以实现同样的功能:
[oracle@bjtest ~]$ expdp yangtk/yangtk directory=d_output dumpfile=yangtk.dp
Export: Release 11.2.0.1.0 - Production on 星期四 8月 26 01:12:43 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
启动 "YANGTK"."SYS_EXPORT_SCHEMA_01": yangtk/******** directory=d_output dumpfile=yangtk.dp
正在使用 BLOCKS 方法进行估计...
处理对象类型 SCHEMA_EXPORT/TABLE/TABLE_DATA
使用 BLOCKS 方法的总估计: 105.5 MB
处理对象类型 SCHEMA_EXPORT/USER
处理对象类型 SCHEMA_EXPORT/SYSTEM_GRANT
.
.
.
处理对象类型 SCHEMA_EXPORT/JOB
. . 导出了 "YANGTK"."T_DOUBLE_IND" 85.31 MB 85995 行
. . 导出了 "YANGTK"."T_PART_LIST":"P1" 252.3 KB 5574 行
. . 导出了 "YANGTK"."MV_UNIONALL" 6.890 KB 25 行
. . 导出了 "YANGTK"."T" 5.562 KB 11 行
. . 导出了 "YANGTK"."T_EXP" 5.562 KB 11 行
. . 导出了 "YANGTK"."T_ID" 5.570 KB 83 行
. . 导出了 "YANGTK"."T_LOG" 5.468 KB 4 行
. . 导出了 "YANGTK"."T_NEW" 5.562 KB 11 行
. . 导出了 "YANGTK"."T_PART_LIST":"P2" 6.859 KB 15 行
. . 导出了 "YANGTK"."T_PART_LIST":"P4" 7.921 KB 39 行
. . 导出了 "YANGTK"."T_RECORD" 5.476 KB 3 行
. . 导出了 "YANGTK"."T_TEST" 0 KB 0 行
已成功加载/卸载了主表 "YANGTK"."SYS_EXPORT_SCHEMA_01"
******************************************************************************
YANGTK.SYS_EXPORT_SCHEMA_01 的转储文件集为:
/home/oracle/yangtk.dp
作业 "YANGTK"."SYS_EXPORT_SCHEMA_01" 已于 01:13:45 成功完成
[oracle@bjtest ~]$ impdp yangtk/yangtk directory=d_output dumpfile=yangtk.dp sqlfile=yangtk_impdp.sql
Import: Release 11.2.0.1.0 - Production on 星期四 8月 26 01:20:45 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已成功加载/卸载了主表 "YANGTK"."SYS_SQL_FILE_FULL_01"
启动 "YANGTK"."SYS_SQL_FILE_FULL_01": yangtk/******** directory=d_output dumpfile=yangtk.dp sqlfile=yangtk_impdp.sql
处理对象类型 SCHEMA_EXPORT/USER
处理对象类型 SCHEMA_EXPORT/SYSTEM_GRANT
处理对象类型 SCHEMA_EXPORT/ROLE_GRANT
处理对象类型 SCHEMA_EXPORT/DEFAULT_ROLE
处理对象类型 SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
处理对象类型 SCHEMA_EXPORT/TYPE/TYPE_SPEC
处理对象类型 SCHEMA_EXPORT/DB_LINK
处理对象类型 SCHEMA_EXPORT/TABLE/TABLE
处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/INDEX
处理对象类型 SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
处理对象类型 SCHEMA_EXPORT/TABLE/COMMENT
处理对象类型 SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
处理对象类型 SCHEMA_EXPORT/PROCEDURE/PROCEDURE
处理对象类型 SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
处理对象类型 SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
处理对象类型 SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
处理对象类型 SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
处理对象类型 SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
处理对象类型 SCHEMA_EXPORT/MATERIALIZED_VIEW
处理对象类型 SCHEMA_EXPORT/JOB
作业 "YANGTK"."SYS_SQL_FILE_FULL_01" 已于 01:20:49 成功完成
[oracle@bjtest ~]$ more yangtk_impdp.sql
-- CONNECT YANGTK
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: SCHEMA_EXPORT/USER
-- CONNECT SYSTEM
CREATE USER "YANGTK" IDENTIFIED BY VALUES 'S:CACB810781CB82D028FD99FB7839AA499B59399E41C820CCC2288B9A402F;B50318A3C52FE640'
DEFAULT TABLESPACE "YANGTK"
TEMPORARY TABLESPACE "TEMP";
-- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT
GRANT ALTER DATABASE LINK TO "YANGTK";
.
.
.
CREATE TABLE "YANGTK"."T_RECORD"
( "STR" VARCHAR2(30 BYTE),
"TIME" DATE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "YANGTK" ;
.
.
.
-- new object type path: SCHEMA_EXPORT/JOB
BEGIN SYS.DBMS_IJOB.SUBMIT(
JOB=> 64,
LUSER=> 'YANGTK',
PUSER=> 'YANGTK',
CUSER=> 'YANGTK',
NEXT_DATE=> TO_DATE('4000-01-01 00:00:00', 'YYYY-MM-DD:HH24:MI:SS'),
INTERVAL=> 'sysdate + 1/1440',
BROKEN=> TRUE,
WHAT=> 'begin insert into t_id values (1); commit; end;',
NLSENV=> 'NLS_LANGUAGE=''SIMPLIFIED CHINESE'' NLS_TERRITORY=''CHINA'' NLS_CURRENCY=''¥'' NLS_ISO_CURRENCY=''CHINA'' NLS_NUMER
IC_CHARACTERS=''.,'' NLS_DATE_FORMAT=''DD-MON-RR'' NLS_DATE_LANGUAGE=''SIMPLIFIED CHINESE'' NLS_SORT=''BINARY''',
ENV=> '0102000200000000');
END;
/
数据泵的SQLFILE比IMP的INDEXFILE包含的源数据更多。INDEXFILE如名称那样,主要包含索引信息,连建表的语句都是被注释的。而SQLFILE则包含当前SCHEMA所有对象的创建信息。
而且SQLFILE功能更强大之处在于定制性,这是IMP的INDEXFILE所不具备的。INCLUDE和EXCLUDE用来限制导入对象的参数完全可以和SQLFILE配合使用,使得SQLFILE获取DDL更加的灵活方便:
[oracle@bjtest ~]$ impdp yangtk/yangtk directory=d_output dumpfile=yangtk.dp sqlfile=yangtk_impdp.sql include=table include=index
Import: Release 11.2.0.1.0 - Production on 星期四 8月 26 01:21:26 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已成功加载/卸载了主表 "YANGTK"."SYS_SQL_FILE_FULL_01"
启动 "YANGTK"."SYS_SQL_FILE_FULL_01": yangtk/******** directory=d_output dumpfile=yangtk.dp sqlfile=yangtk_impdp.sql include=table include=index
处理对象类型 SCHEMA_EXPORT/TABLE/TABLE
处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/INDEX
处理对象类型 SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
处理对象类型 SCHEMA_EXPORT/TABLE/COMMENT
处理对象类型 SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
作业 "YANGTK"."SYS_SQL_FILE_FULL_01" 已于 01:21:30 成功完成
[oracle@bjtest ~]$ more yangtk_impdp.sql
-- CONNECT YANGTK
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: SCHEMA_EXPORT/TABLE/TABLE
CREATE TABLE "YANGTK"."T_PART_LIST"
( "OWNER" VARCHAR2(30 BYTE),
"NAME" VARCHAR2(30 BYTE),
"TABLESPACE_NAME" VARCHAR2(30 BYTE),
"TYPE" VARCHAR2(18 BYTE)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "YANGTK"
PARTITION BY LIST ("TABLESPACE_NAME")
(PARTITION "P1" VALUES ('SYSAUX', 'SYSTEM')
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "YANGTK" NOCOMPRESS ,
PARTITION "P2" VALUES ('YANGTK')
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "YANGTK" NOCOMPRESS ,
PARTITION "P4" VALUES (DEFAULT)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "YANGTK" NOCOMPRESS ) ;
CREATE TABLE "YANGTK"."T_LOG"
( "NAME" VARCHAR2(30 BYTE),
"VALUE" NUMBER
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "YANGTK" ;
CREATE TABLE "YANGTK"."T_RECORD"
( "STR" VARCHAR2(30 BYTE),
"TIME" DATE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "YANGTK" ;
.
.
.
-- new object type path: SCHEMA_EXPORT/TABLE/INDEX/INDEX
CREATE INDEX "YANGTK"."IND_DOUBLE_NAME" ON "YANGTK"."T_DOUBLE_IND" ("NAME")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "YANGTK" PARALLEL 1 ;
ALTER INDEX "YANGTK"."IND_DOUBLE_NAME" NOPARALLEL;
CREATE INDEX "YANGTK"."IND_DOUBLE_TYPE" ON "YANGTK"."T_DOUBLE_IND" ("TYPE")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "YANGTK" PARALLEL 1 ;
ALTER INDEX "YANGTK"."IND_DOUBLE_TYPE" NOPARALLEL;
.
.
.
通过指定INDEX和TABLE,使得SQLFILE获取的DDL仅包括建表和索引的语句。
来自 “ ITPUB博客 ” ,链接:https://blog.itpub.net/4227/viewspace-671886/ ,如需转载,请注明出处,否则将追究法律责任。