Oracle笔记十七：导入导出

1、导入

1.1、sqlldr

1.1.1、sqlldr介绍

1.1.1.1、sqlldr用法

命令：sqlldr keyword=value keyword1=value1 keyword2=value2 …
常用关键字：

• userid : ORACLE username/password@tnsname
• control : 控制文件
• log : 记录的日志文件
• bad : 坏数据文件，记录错误的未加载数据
• data : 数据文件，data参数只能指定一个数据文件,如果控制文件也通过infile指定了数据文件,并且指定多个,则sqlldr在执行时,先加载data参数指定的数据文件,控制文件中第一个infile指定的数据文件被忽略, 但后续的infile指定的数据文件继续有效
• discard : 丢弃的数据文件,默认情况不产生,必须指定
• discardmax : 允许丢弃数据的最大值 (默认全部)
• skip : (默认0)，跳过记录数,从数据文件中,从第一行开始要计算要跳过的行数,*,对于多表加载的情况,如果有when条件 判断的话，或者直接路径下的多表加载，如果要加载的记录数不同，则该参数无效
• load : Number of logical records to load (默认全部)
• errors : 允许的错误记录数,超过则终止任务 (默认50)
• readsize : 缓冲区大小,默认值:1048576单位字节,最大不超过20m,该参数仅当从数据文件读取时有效
• bindsize : 每次提交记录的缓冲区的大小，字节为单位，默认256000
• rows : 常规路径导入时：指绑定数组中的行数；直接路径导入时：指一次从数据文件只读取的行数；参数同时受bindsize制约,如果rows*每行实际占用大小超出bindsize最大可用值，则rows自动降低达到bindsize最大可用值（每次提交的记录数，默认: 常规路径 64, 直接路径：所有）
• silent : 禁止输出信息
• direct : 使用直通路径方式导入,不走buffer cache,通过direct path api发送数据到服务器端的加载引擎，加载引擎按照数据块的格式处理数据并直接写向数据文件,因此效率较高(默认FALSE)
• parallel : 并行导入，仅在直接路径加载时有效(默认FALSE)
• file : 并行加载时会用到该参数,指定file参数,要加载的内容即只向指定的数据文件写入数据，减少i/o
• multithreading: 是否启用多线程，多cpu为true,单cpu false,直接路径加载时有效

1.1.1.2、传统路径与直接路径模式

传统路径（conventional path）:
　　SQLLDR会利用SQL插入为我们加载数据。
直接路径（direct path）:
　　采用这种模式，SQLLDR不使用SQL；而是直接格式化数据库块，而绕过整个SQL引擎和UNDO生成，同时还可能避开REDO生成。要在一个没有任何数据的库中充分加载数据，最快的方法就是采用并行直接路径加载

1.1.1.3、传统方式写入和直接路径写入

• 传统方式写入:
  • Oracle会重用表里面空闲空间，并且写入会先写入到buffer cache。
• 直接路径写入：
  • Oracle不重用表里面的空闲空间，直接写入到新分配的块，并且数据直接写入到data file，不写入到buffer cache。效率更高。如果表的并行度不为1，那么直接路径写入是默认的写入行为（需要开启会话并行dml）。直接路径写入为元数据（例如空间扩展引起的数据字典数据变化）变更产生REDO, UNDO。而数据变更，redo，undo生成情况如下：
    • 不为数据写入生成undo
    • 如果数据库为非归档或者没有开启force logging，那么直接路径写入时，不为数据写入产生redo，这时与表是否设置nologging属性无关。
    • 如果数据库为归档，但是没有开启force logging，logging的表会产生redo，nologging的表不会产生redo。
    • 如果数据库为归档并且开启了force logging，数据写入会产生redo，无论是否设置logging，nologging。
• APPEND,PARALLE和直接路径写入关系：
  • Append默认也是采用直接路径写入，不要求session enable parallel。
  • Parallel 要求会话级别enable parallel，否则将无法进行直接路径写入。

1.1.2、sqlldr ctl文件

1.1.2.1、sqlldr ctl语法

##OPTIONS内容同sqlldr的关键字
OPTIONS ( { [SKIP=integer] [ LOAD = integer ]
[ERRORS = integer] [ROWS=integer]
[BINDSIZE=integer] [SILENT=(ALL|FEEDBACK|ERROR|DISCARD) ] )

LOAD[DATA]
##INFILE 和INDDN是同义词，它们后面都是要加载的数据文件。如果用 * 则表示数据就在控制文件内。在INFILE 后可以跟几个文件
[ { INFILE | INDDN } {file | * }
STREAM 表示一次读一个字节的数据。新行代表新物理记录（逻辑记录可由几个物理记录组成）
RECORD 使用宿主操作系统文件及记录管理系统。如果数据在控制文件中则使用这种方法
FIXED length 要读的记录长度为length字节
[STREAM | RECORD | FIXED length [BLOCKSIZE size]|
VARIABLE [length] ]
[ { BADFILE | BADDN } file ]
{DISCARDS | DISCARDMAX} integr ]
insert–为缺省方式，在数据装载开始时要求表为空
append–在表中追加新记录
replace–删除旧记录(用 delete from table 语句)，替换成新装载的记录
truncate–删除旧记录(用 truncate table 语句)，替换成新装载的记录
[APPEND | REPLACE | INSERT | TRUNCATE]
INTO TABLE [user.]table
[WHEN condition [AND condition]…]
数据中每行记录用 “,” 分隔
[Fields terminated by “,” ]
数据中每个字段用 ‘"’ 框起，比如字段中有 “,” 分隔符时
[Optionally enclosed by ‘"’]
表的字段没有对应的值时允许为空
[trailing nullcols]
(
column {
RECNUM | CONSTANT value |
SEQUENCE ( { integer | MAX |COUNT} [, increment] ) |
[POSITION ( { start [end] | * [ + integer] }
) ]
datatype
[TERMINATED [ BY ] {WHITESPACE| [X] ‘character’ } ]
[ [OPTIONALLY] ENCLOSE[BY] [X]‘charcter’]
[NULLIF condition ]
[DEFAULTIF condotion]
}
[ ,…]
)
[INTO TABLE…]
[BEGINDATA]

注意

• 使用定位域而不要使用分隔域，分隔域要求装载器搜索数据以查找分隔符。定位域比较快，因为装载器只需要做简单的指针运算

1.1.3、实例

1.1.3.1、实例一：传统模式

sqlldr user/password@dbname
control=/home/oracle/data/install.ctl
log=/home/oracle/data/export.log
bad=/home/oracle/data/export.bad
rows=10000
readsize=20680000
bindsize=20680000

1.1.3.2、实例二：直接模式

采用此模式：不可有序列以及索引。
sqlldr user/password@dbname
control=/home/oracle/data/export.ctl
log=/home/oracle/data/export.log
bad=/home/oracle/data/export.bad
silent=header,feedback
direct=true

并行
sqlldr user/password@dbname
control=/home/oracle/APS_LOAD/ctl/AP_CONTRACT.CTL
direct=true
parallel=true
LOG=/home/oracle/APS_LOAD/log/KaTeX parse error: Expected group after '_' at position 22: …day/AP_CONTRACT_̲yesterday.log
bad=/home/oracle/APS_LOAD/bad/DUE_BILL_$yesterday.bad
rows=10000
readsize=20000000
bindsize=20000000
DISCARD=/home/oracle/APS_LOAD/bad/discard_ts.dis

1.1.3.3、实例三：控制文件

load data　　　　　　　　　　　　--控制文件标识
infile ‘d:/pr.csv’ “str X’0A’”　　　　　--需要导入的数据文件，换行符作为结束"str X’0A’“中0A的生成方式：select utl_raw.cast_to_raw(chr(13)||chr(10)) from dual;
into table pr_tmp 　　　　　　　　--加载到表
fields terminated by “,” 　　　　　　--以“ , ”分隔
OPTIONALLY ENCLOSED BY '”’ 　--表示""之间的是一个字段
TRAILING NULLCOLS　　　　　　--指定空的单元格用null填充
(ID char(256) , 　　　　　　　　　　--修改对字段长度的限制，默认是255
MID char(256),
KS char(512),
DES char(4000)
);

1.1.3.4、实例四：控制文件

load data --控制文件标识
infile ‘d:/pr.csv’ --需要导入的数据文件
into table pr_tmp --加载到表
FIELDS TERMINATED BY WHITESPACE
WHITESPACE（包括空格、Tab、换行符、换页符及回车符）FIELDS TERMINATED BY x’09’（分割符号为tab）
TRAILING NULLCOLS --指定空的单元格用null填充
(ID ,
T_ID FILLER, – FILLER 关键字 此列的数值不会被装载
T_DATE_1 “CASE WHEN :T_DATE_1 is null THEN TO_DATE(‘2999-12-31’,‘yyyy-mm-dd’) END”,
–函数判断
T_DATE date ‘yyyy-mm-dd’, --日期类型特别说明，并且要指定其格式
T_NAME POSITION(3:6) “UPPER(:T_NAME)”, --截位，并转换为大写
T_SEX position(*:8) , --该字段的开始位置在前一字段的结束位置
ENTIRE_LINE “UPPER(:T_NAME||:T_SEX)”, --拼接并转换为大写
TS “sysdate”, --获取系统时间
Datanum sequence(max,1) --(max大小写不区分) ，sqlldr将自动找到列中的最大值
Linenum RECNUM --载入每行的行号
);

1.1.3.5、实例五：控制文件

OPTIONS (skip=1,rows=128) – sqlldr 命令显示的 选项可以写到这里边来,skip=1 用来跳过数据中的第一行
LOAD DATA
INFILE * – 因为数据同控制文件在一 起，所以用 * 表示
append – 这里用 了 append 来操作，在表 users 中附加记录
INTO TABLE users
when LOGIN_TIMES<>‘8’ – 还可以用 when 子句选择导入符合条件的记录
Fields terminated by “,”
trailing nullcols
(
virtual_column FILLER, --跳过 由 PL/SQL Developer 生成的第一列序号
user_id “user_seq.nextval”, --这一列直接取序列的下一值，而不用数据中提供的值
user_name “'Hi '||upper(:user_name)”,–,还能用SQL函数或运算对数据进行加工处理
login_times terminated by “,”, NULLIF(login_times=‘NULL’) --可为列单独指定分隔符
last_login DATE “YYYY-MM-DD HH24:MI:SS” NULLIF (last_login=“NULL”) – 当字段为"NULL"时就是 NULL
)
BEGINDATA --数据从这里开始
,USER_ID,USER_NAME,LOGIN_TIMES,LAST_LOGIN
1,1,Unmi,3,2009-1-5 20:34
2,2,Fantasia,5,2008-10-15
3,3,隔叶黄 莺,8,2009-1-2
4,4,Kypfos,NULL,NULL
5,5,不知 秋,1,2008-12-23

1.1.4、sqlldr常见问题

1.1.4.1、Multitute character error多字节错误

• 字符集不一致，一般为UTF8转换为GB2312（会出现乱码）
  （UTF8汉字占3个字节，GB2312汉字占2个字节）
  （服务器字符集、客户端字符集不一致）
• 截位错误，当截位把一个汉字强制分开（例如一个汉字有3个字节，从第二个字节截位，则会乱码，大多出现？）
• 所给文件的字段值被强制截位，导致文件导入错误

1.1.4.2、Field in data file exceeds maximum length超长

• ctl文件中使用char类型时候，未指定大小，默认255，一旦导入内容超过此长度，即会报错
• 导入文件的内容超出对应表字段的长度

1.2、imp

1.2.1、imp介绍

• imp它是操作系统下一个可执行的文件，存放目录ORACLE_HOME/bin
• imp导入工具将EXP形成的二进制系统文件导入到数据库中，它有三种模式：
  • 用户模式：导出用户所有对象以及对象中的数据；
  • 表模式： 导出用户所有表或者指定的表；
  • 整个数据库： 导出数据库中所有对象。只有拥有IMP_FULL_DATABASE和DBA权限的用户才能做整个数据库导入

1.2.2、imp语法

格式: IMP KEYWORD=value 或 KEYWORD=(value1,value2,…,valueN)
例如: IMP SCOTT/TIGER IGNORE=Y TABLES=(EMP,DEPT) FULL=N
　　　或 TABLES=(T1:P1,T1:P2), 如果 T1 是分区表
常用关键字：括号中列出的是默认值

• USERID：userName/passwd@dbname，必须是命令行中的第一个参数
• FILE：输入文件 (EXPDAT.DMP)
• FROMUSER：所有者用户名列表
• TOUSER：要导给的某用户，如果要把A用户的数据导入B用户下, A用户需要有imp_full_database权限
• FULL：导入整个文件 (默认N)，（注意：full=y 和 fromuser、touser 只能二选一）
• BUFFER：数据缓冲区大小，酌情设定此值，可加快导入速度，特别是数据量大的时候
• TABLES：需要导入的表名列表，使用逗号分隔
• IGNORE：忽略创建错误 (N)
• ROWS：导入数据行 (Y)
• LOG：imp导入的日志文件
• PARFILE：参数文件名
• DATA_ONLY：仅导入数据 (N)，Oracle 11g以上支持，并且当DATA_ONLY=y时，不能使用ignore=y
• CONSTRAINTS：导入限制 (Y)
• COMPRESS：导入到一个区 (Y)

1.2.3、imp导入常见问题

• 如果字符集不同, 导入会失败，因而导入之前可以指定环境变量（Linux：export NLS_LANG=‘字符集’，Windows：set NLS_LANG=‘字符集’）
• imp可以成功导入低版本exp生成的文件,不能导入高版本exp生成的文件

1.3、impdp

1.3.1、impdp说明

数据泵导入实用程序提供了一种用于在 Oracle 数据库之间传输数据对象的机制，只能在ORACLE服务端使用，不能在客户端使用

1.3.2、impdp语法

格式: impdp KEYWORD=value 或 KEYWORD=(value1,value2,…,valueN)
示例: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp
常用关键字：方括号中列出的是默认值

• USERID：userName/passwd@dbname，必须是命令行中的第一个参数
• CONTENT：指定要加载的数据。有效的关键字为: [ALL]——导对象定义及其所有数据, DATA_ONLY——只导对象数据 和 METADATA_ONLY——只导对象定义。
• DIRECTORY：用于转储文件, 日志文件和 SQL 文件的目录对象。
  • create or replace directory filePath as ‘E:’;（Windows）（数据库执行）
  • create or replace directory filePath as ‘/tmp/’;（Linux）（数据库执行）
  • 使用例如，DIRECTORY=filePath
• DUMPFILE：要从中导入的转储文件的列表 [expdat.dmp]。例如, DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp。
• EXCLUDE：排除特定对象类型。例如, EXCLUDE=SCHEMA:"=‘HR’"。
• FULL：导入源中的所有对象 [YES]。
• INCLUDE：包括特定对象类型。例如, INCLUDE=TABLE_DATA。
• LOGFILE：日志文件名 [import.log]。
• LOGTIME：指定要给在导入操作期间显示的消息加时间戳。有效的关键字值为: ALL, [NONE], LOGFILE 和 STATUS。
• NOLOGFILE：不写入日志文件 [NO]。
• PARFILE：指定参数文件。
• QUERY：用于导入表的子集的谓词子句。例如, QUERY=employees:“WHERE department_id > 10”。
• REMAP_DATA：指定数据转换函数。例如, REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO。
• REMAP_DATAFILE：在所有 DDL 语句中重新定义数据文件引用。
• REMAP_SCHEMA：将一个方案中的对象加载到另一个方案。
• REMAP_TABLE：将表名重新映射到另一个表。例如, REMAP_TABLE=HR.EMPLOYEES:EMPS。
• REMAP_TABLESPACE：将表空间对象重新映射到另一个表空间。
• REUSE_DATAFILES：如果表空间已存在, 则将其初始化 [NO]。
• SCHEMAS：要导入的方案的列表。
• SERVICE_NAME：约束 Oracle RAC 资源的活动服务名和关联资源组。
• SKIP_UNUSABLE_INDEXES：跳过设置为“索引不可用”状态的索引。
• SQLFILE：将所有的 SQL DDL 写入指定的文件。
• TABLE_EXISTS_ACTION：导入对象已存在时执行的操作。有效的关键字为: APPEND, REPLACE, [SKIP] 和 TRUNCATE。
• TABLES：标识要导入的表的列表。例如, TABLES=HR.EMPLOYEES,SH.SALES:SALES_1995。
• TABLESPACES：标识要导入的表空间的列表。

2、导出

2.1、sqluldr2

2.1.1、sqluldr2介绍

sqluldr2是一款Oracle数据快速导出工具，包含32、64位程序，sqluldr2在大数据量导出方面速度超快，能导出亿级数据为excel文件，另外它的导入速度也是非常快速，功能是将数据以TXT/CSV等格式导出。

2.1.1、sqluldr2语法

格式：SQLULDR2 keyword=value [,keyword=value,…]
常用关键字：

• user = username/password@tnsname
• sql = SQL file name，例如sql="/opt/sql/query.sql"
• query = select statement，例如query=“select * from xxx”
• field = separator string between fields，例如field=“0x09”
• record = separator string between records，例如record=0x0a
  • for field and record, you can use ‘0x’ to specify hex character code,
  • \r=0x0d \n=0x0a |=0x7c ,=0x2c, \t=0x09, :=0x3a, #=0x23, "=0x22 '=0x27
• rows = print progress for every given rows (default, 1000000)
• file = output file name(default: uldrdata.txt)
• log = log file name, prefix with + to append mode
• fast = auto tuning the session level parameters(YES)
• text = output type (MYSQL, CSV, MYSQLINS, ORACLEINS, FORM, SEARCH).
• charset = character set name of the target database.例如charset=UTF8
• ncharset= national character set name of the target database.
• parfile = read command option from parameter file
• read = set DB_FILE_MULTIBLOCK_READ_COUNT at session level
• sort = set SORT_AREA_SIZE at session level (UNIT:MB)
• hash = set HASH_AREA_SIZE at session level (UNIT:MB)
• array = array fetch size
• head = print row header(Yes|No)
• batch = save to new file for every rows batch (Yes/No)
• size = maximum output file piece size (UNIB:MB)
• serial = set _serial_direct_read to TRUE at session level
• trace = set event 10046 to given level at session level
• table = table name in the sqlldr control file
• control = sqlldr control file and path.
• mode = sqlldr option, INSERT or APPEND or REPLACE or TRUNCATE
• buffer = sqlldr READSIZE and BINDSIZE, default 16 (MB)
• long = maximum long field size
• width = customized max column width (w1:w2:…)
• quote = optional quote string
• data = disable real data unload (NO, OFF)
• alter = alter session SQLs to be execute before unload
• safe = use large buffer to avoid ORA-24345 error (Yes|No)
• crypt = encrypted user information only (Yes|No)
• sedf/t = enable character translation function
• null = replace null with given value
• escape = escape character for special characters
• escf/t = escape from/to characters list
• format = MYSQL: MySQL Insert SQLs, SQL: Insert SQLs.
• exec = the command to execute the SQLs.
• prehead = column name prefix for head line.
• rowpre = row prefix string for each line.
• rowsuf = row sufix string for each line.
• colsep = separator string between column name and value.
• presql = SQL or scripts to be executed before data unload.
• postsql = SQL or scripts to be executed after data unload.
• lob = extract lob values to single file (FILE).
• lobdir = subdirectory count to store lob files .
• split = table name for automatically parallelization.
• degree = parallelize data copy degree (2-128).

2.1.2、sqluldr2实例

2.1.2.1、导出文件分卷

sqluldr2 工具有一个rows参数，除了类似exp 工具的feedback 参数，每导出行数据，自动输出一行提示信息外，还可以结合batch 参数，如果batch 参数设为yes, 则每行数据生成一个导出文件。
此外size 参数可以指定导出文件的最大的大小。它与rows参数 取更严厉的要求来确定分卷文件的大小。
由于按多个文件输出，因此在指定输出文件名时，需使用动态文件名，例如指定file参数为orders_%B.csv，其中%B表示在多个文件导出时，表示文件序号。

例如，sqluldr2.par 定义为如下：

user=system/manager@dbserver
query=select /*+ parallel(8) */ * from orders
head=no
rows=1000000
file=d:\data\orders_%B.csv
log=d:\data\sqluldr.log
text=CSV
fast=yes
batch=yes
size=500
control=d:\data\order.ctl
mode=truncate
table=orders

执行起来：sqluldr2 parfile=./sqluldr2.par

2.1.2.1.1、按记录数切分文件

按记录数切分文件的功能取决于三个命令行选项: FILE, ROWS, BATCH. 其中FILE选项指定的文件名中需要包括”%b”特征串, 以表示生成的文件号. ROWS指定单个文件的记录数, 而BATCH则指定是否切换成多个文件.
sqluldr2_linux64_10204.bin user=“username/pwd” sql="/opt/sql/query.sql" field=“0x09” record=0x0a rows=100000 batch=yes file="/opt/data/table.%b.txt" charset=UTF8

2.1.2.1.2、按大小切分文件

按大小切分文件的功能取决于两个命令行选项: FILE, SIZE. 其中FILE选项指定的文件名中需要包括”%b”特征串, 以表示生成的文件号; 而SIZE选项指定每个文件的目标大小.
sqluldr2_linux64_10204.bin user=“username/pwd” sql="/opt/sql/query.sql" field=“0x09” record=0x0a size=500MB file="/opt/data/table.%b.txt" charset=UTF8

2.1.2.2、ORACLE跨数据库的导入导出

我们将数据导成文本，其最终目的还是要导入到其它数据库。 导入其它ORACLE数据库，最快的方法就是使用ORACLE 自带的sqlldr 工具。
Sqlldr 使用的控制文件，可以在第一部分中control、mode和table 参数指定后，生成控制文件，可以修改这个文件以适合实际使用。
如果我们先把数据导成文本文件，再使用sqlldr 导入到数据库，由于涉及到磁盘的写入读取，可能在大数据量时影响速度。因此可以将sqluldr2 的输出到标准输出设备（屏幕），而sqlldr从标准输出设备上读取入库。
可以将sqluldr2的file 参数设置为”-“, sqluldr2将输出到标准输出设备，修改sqlldr 中的控制文件的INFILE 参数为"-" （对于windows 和unix 参数），使用管道模式，进行数据导出装载。
此外，由于源（sqluldr2导出的库）和目标数据库（sqlldr 导入库）的字符集不一样（这也是我们之所以采用文本导出方式的原因）， 我们可以在NLS_LANG 环境变量和目标数据库字符集一致的情况下， 设置sqluldr2的charset 参数与源数据库字符集一致,避免来回修改NLS_LANG 环境变量。
举例：

在sqlldr 中的控制文件 指定INFILE 参数:

LOAD DATA
INFILE “-”

源数据库字符集为US7ASCII ，目标数据库字符集ZHS16GBK, 当前NLS_LANG 为AMERICAN_AMERICA.ZHS16GBK
sqluldr2 user=system/manager@dbserver1 query=“select /*+ parallel(8) */ * from orders where rownum <10” charset=US7ASCII file=- | sqlldr xxx/xx@dbserver 2control=D:\order.ctl

注意，的INFILE 参数为"-" 对于XE 下的sqlldr 仿佛无效

2.1.2.3、MYSQL 数据库的导入

ORACLE 数据也可以导入到MYSQL 数据库，sqluldr2的参数format指定为mysql， 输出的则为MYSQL语句，可以直接执行。例如：

sqluldr2 comm/comm@dbserver table=app_roles format=mysql quote=0x27 escape=0x53 null=null query=" select * from app_roles" file=- | mysql -h 21.103.117.51 -uroot -pqwxtpwd test

2.1.3、常见错误

sqluldr2_linux64_10204.bin: error while loading shared libraries: libclntsh.so.10.1: cannot open shared object file: No such file or directory
解决办法:
1.先检查libclntsh.so.10.1文件是否存在，一般安装完数据库，该文件都会存在。
2.设置oracle的环境变量,加载oracle的lib包export LD_LIBRARY_PATH=$ORACL{E}_{H}OME/bin:/bin:/usr/bin:/usr/local/bin:/usr/X11R6/bin/:$ORACLE_HOME/lib:$L{D}_{L}IBRAR{Y}_{P}ATHexportORACL{E}_{B}ASE=/u01/oracleexportORACL{E}_{H}OME=$ORACLE_BASE/product/11.2/db
export ORACLE_SID=oracle
export NLS_DATE_FORMAT=“YYYY-MM-DD HH24:MI:SS”
export PATH=.:$PATH:$HOME/bin:$ORACL{E}_{H}OME/bin:$ORACLE_HOME/OPatch
export LD_LIBRARY_PATH=$ORACL{E}_{H}OME/bin:/bin:/usr/bin:/usr/local/bin:/usr/X11R6/bin/:$ORACLE_HOME/lib:$L{D}_{L}IBRAR{Y}_{P}ATHexportCLASSPATH=$ORACLE_HOME/JRE:$ORACL{E}_{H}OME/jlib:$ORACLE_HOME/rdbms/jlib

ORA-24345: A Truncation or null fetch error occurred

导出参数加上:safe=yes

2.2、exp

2.2.1、exp介绍

exp导出工具将数据库中数据备份压缩成一个二进制系统文件.可以在不同OS间迁移

• exp导出对象顺序：
  • exporting database links
  • exporting sequence numbers
  • exporting cluster definitions
  • about to export UFTZDB2’s tables via Conventional Path …
  • exporting synonyms
  • exporting views
  • exporting stored procedures
  • exporting operators
  • exporting referential integrity constraints
  • exporting triggers
  • exporting indextypes
  • exporting bitmap, functional and extensible indexes
  • exporting posttables actions
  • exporting materialized views
  • exporting snapshot logs
  • exporting job queues
  • exporting refresh groups and children
  • exporting dimensions
  • exporting post-schema procedural objects and actions
  • exporting statistics

2.2.2、exp语法

格式: EXP KEYWORD=value 或 KEYWORD=(value1,value2,…,valueN)
例如: EXP SCOTT/TIGER IGNORE=Y TABLES=(EMP,DEPT)
　　　或 TABLES=(T1:P1,T1:P2), 如果 T1 是分区表
常用关键字：括号中列出的是默认值

• USERID：userName/passwd@dbname，必须是命令行中的第一个参数
• FILE：导出文件 (EXPDAT.DMP)
• OWNER：所有者用户名列表
• FULL：导出整个文件 (默认N)
• BUFFER：数据缓冲区大小，酌情设定此值，可加快导入速度，特别是数据量大的时候
• TABLES：需要导出的表名列表，使用逗号分隔
• QUERY：用于导出表的子集的 select 子句，query="‘WHERE …’"（双引号单引号）
• ROWS：导出数据行 (Y)
• LOG：exp导入的日志文件
• PARFILE：参数文件名
• CONSTRAINTS：导出限制 (Y)
• DIRECT：直接路径 (N)
• COMPRESS：导入到一个区 (Y)
• filesize：若导出的数据文件大，应该用该参数，限制文件大小不要超过2g
  • exp userid=gwm/gwm file=test1,test2,test3,test4,test5 filesize=2G log=test.log

2.3、expdp

2.3.1、说明

数据泵导出实用程序提供了一种用于在 Oracle 数据库之间传输数据对象的机制，只能在ORACLE服务端使用，不能在客户端使用

2.3.2、语法

格式: expdp KEYWORD=value 或 KEYWORD=(value1,value2,…,valueN)
示例: expdp scott/tiger DUMPFILE=scott.dmp DIRECTORY=dmpdir SCHEMAS=scott
　　　或 TABLES=(T1:P1,T1:P2), 如果 T1 是分区表
常用关键字：方括号中列出的是默认值

• USERID：userName/passwd@dbname，必须是命令行中的第一个参数
• CONTENT：指定要加载的数据。有效的关键字为: [ALL]——导对象定义及其所有数据, DATA_ONLY——只导对象数据 和 METADATA_ONLY——只导对象定义。
• DIRECTORY：用于转储文件, 日志文件和 SQL 文件的目录对象。
  • create or replace directory filePath as ‘E:’;（Windows）（数据库执行）
  • create or replace directory filePath as ‘/tmp/’;（Linux）（数据库执行）
  • 使用例如，DIRECTORY=filePath
• DUMPFILE：要从中导入的转储文件的列表 [expdat.dmp]。例如, DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp。
• EXCLUDE：排除特定对象类型。例如, EXCLUDE=SCHEMA:"=‘HR’"。
• FILESIZE：以字节为单位指定每个转储文件的大小。
• FULL：导入源中的所有对象 [YES]。
• INCLUDE：包括特定对象类型。例如, INCLUDE=TABLE_DATA。
• LOGFILE：日志文件名 [import.log]。
• LOGTIME：指定要给在导出操作期间显示的消息加时间戳。有效的关键字值为: ALL, [NONE], LOGFILE 和 STATUS。
• NETWORK_LINK：源系统的远程数据库链接的名称。
• NOLOGFILE：不写入日志文件 [NO]。
• PARALLEL：更改当前作业的活动 worker 的数量。
• PARFILE：指定参数文件名。
• QUERY：用于导出表的子集的谓词子句。例如, QUERY=employees:“WHERE department_id > 10”。
• REMAP_DATA：指定数据转换函数。例如, REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO。
• REUSE_DUMPFILES：覆盖目标转储文件 (如果文件存在) [NO]。
• SAMPLE：要导出的数据的百分比。
• SCHEMAS：要导出的方案的列表 [登录方案]。
• SERVICE_NAME：约束 Oracle RAC 资源的活动服务名和关联资源组。
• TABLES：标识要导出的表的列表。例如, TABLES=HR.EMPLOYEES,SH.SALES:SALES_1995。
• TABLESPACES：标识要导出的表空间的列表。
• TRANSPORTABLE：指定是否可以使用可传输方法。有效的关键字值为: ALWAYS 和 [NEVER]。
• TRANSPORT_FULL_CHECK：验证所有表的存储段 [NO]。
• TRANSPORT_TABLESPACES：要从中卸载元数据的表空间的列表。
• VERSION：要导出的对象版本。有效的关键字值为: [COMPATIBLE], LATEST 或任何有效的数据库版本。
• VIEWS_AS_TABLES：标识要作为表导出的一个或多个视图。例如, VIEWS_AS_TABLES=HR.EMP_DETAILS_VIEW。

2.4、spool

编写spool.sql文件，内容如下：

SPOOL D:\bbb.txt
set echo off --不显示脚本中正在执行的SQL语句
set feedback off --不显示sql查询或修改行数
set term off --不在屏幕上显示
set heading off --不显示表头
set linesize 500 --设置行宽，根据需要设置，默认100
select owner||’,’||object_name|| ‘,’ ||object_id FROM dba_objects WHERE rownum<=1000; --需要导出的数据查询sql
SPOOL OFF

执行：（sqlplus或者command窗口）@D:\spool.sql

3、总结

EXP和IMP是客户端工具程序，它们既可以在客户端使用，也可以在服务端使用。
（数据泵）EXPDP和IMPDP是服务端的工具程序，他们只能在ORACLE服务端使用，不能在客户端使用。
IMP只适用于EXP导出的文件，不适用于EXPDP导出文件；IMPDP只适用于EXPDP导出的文件，而不适用于EXP导出文件。

4、set详解

• set colsep ‘|’; --输出分隔符
  eg、
  SQL> set colsep ‘|’;
  SQL> select * from dept;

  DEPTNO|DNAME |LOC
  ———-|————–|————-
  10|ACCOUNTING |NEW YORK

• set echo off;–显示start启动的脚本中的每个sql命令，缺省为on

• set echo on; --设置运行命令是否显示语句

• set feedback on; --设置显示“已选择XX行”

• set feedback off; --回显本次sql命令处理的记录条数，缺省为on

• set heading on; --输出字段标题，缺省为on

• set pagesize 0; --输出每页行数，缺省为24，为了避免分页，可设定为0。

• set linesize 80; --输出一行字符个数，缺省为80

• set numwidth 12; --输出number类型长度，缺省为10

• set termout off; --显示脚本中的命令的执行结果，缺省为on

• set trimout on; --去除标准输出每行的拖尾空格，缺省为off

• set trimspool on; --去除重定向（spool）输出每行的拖尾空格，缺省为off

• set serveroutput on; --设置允许显示输出类似dbms_output

• set timing on; --设置显示“已用时间：XXXX”

• set autotrace on; --设置允许对执行的sql进行分析

• set verify off; --可以关闭和打开提示确认信息old 1和new 1的显示.