Oracle的导出和导入

实战过程中对Oracle导出/导入的总结

1.Oracle导出/导入数据库 #

导入导出关键字说明

关键字	说明（默认）	关键字	说明（默认）
USERID	用户名/口令	RECORDLENGTH IO	记录的长度
FULL	导入整个文件 (N)	GRANTS	导入权限 (Y)
BUFFER	数据缓冲区大小	INCTYPE	增量导入类型
FROMUSER	所有人用户名列表	INDEXES	导入索引 (Y)
FILE	输入文件 (EXPDAT.DMP)	COMMIT	提交数组插入 (N)
TOUSER	用户名列表	ROWS	导入数据行 (Y)
SHOW	只列出文件内容 (N)	PARFILE	参数文件名
TABLES	表名列表	LOG	屏幕输出的日志文件
IGNORE	忽略创建错误 (N)	CONSTRAINTS	导入限制 (Y)

关键字	说明（默认）	关键字	说明（默认）
DESTROY	覆盖表空间数据文件 (N)	INDEXFILE	将表/索引信息写入指定的文件
SKIP_UNUSABLE_INDEXES	跳过不可用索引的维护 (N)	FEEDBACK	每 x 行显示进度 (0)
TOID_NOVALIDATE	跳过指定类型 ID 的验证	FILESIZE	每个转储文件的最大大小
STATISTICS	始终导入预计算的统计信息	RESUMABLE	遇到与空格有关的错误时挂起 (N)
RESUMABLE_NAME	用来标识可恢复语句的文本字符串	RESUMABLE_TIMEOUT	RESUMABLE 的等待时间
COMPILE	编译过程, 程序包和函数 (Y)

下列关键字仅用于可传输的表空间

关键字	说明（默认）
TRANSPORT_TABLESPACE	导入可传输的表空间元数据 (N)
TABLESPACES	将要传输到数据库的表空间
DATAFILES	将要传输到数据库的数据文件
TTS_OWNERS	拥有可传输表空间集中数据的用户

以下为经常用到的关键字

--如果要对dmp文件进行压缩，可以加上compress=y
--STATISTICS 始终导入预计算的统计信息。如果表很大，导入统计信息会花很长时间。如果不想导入导出统计信息，可以在语句后追加statistics=none。比如在导入数据库的过程中，出现“IMP-00003：遇到ORACLE错误20001  Invalid or inconsistent input values”的错误。加上了“statistics=none”之后，就没有问题了。
--IGNORE   忽略创建错误 (N)
--GRANTS 导入权限 (Y)

1.1导出 #

模式一：全量导出（需要具备dba权限，慎用）

--如果要对dmp文件进行压缩，可以加上compress=y
--STATISTICS 始终导入预计算的统计信息。如果表很大，导入统计信息会花很长时间。如果不想导入导出统计信息，可以在语句后追加statistics=none
--full=y是导出整个文件
exp 用户名/密码@数据库实例 owner=用户名 file=文件存储路径 log=日志存储路径 full=y

--例子
exp nnjy/nnjy@10.4.131.30:1521/ora11g file=d:/export/nnjy.emp log=d:/export/nnjy.log full=y

exp nnjy/nnjy@dl30 file=d:/export/nnjy.emp log=d:/export/nnjy.log full=y compress=y

模式二：导出指定用户（推荐使用）（可以导出一个或多个用户）

--如果要对dmp文件进行压缩，可以加上compress=y
--导出一个用户
exp 用户名/密码@数据库实例 owner=用户名 file=文件存储路径 log=日志存储路径 owner=指定用户

--导出多个用户（需具备dba权限）
exp 用户名/密码@数据库实例 owner=用户名 file=文件存储路径 log=日志存储路径 owner=(指定用户1,指定用户2,指定用户3)

--例子
exp nnjyu33/nnjyu33@10.4.131.30:1521/ora11g owner=nnjyu33 file=d:/nnjyu33.emp log=d:/nnjyu33.log

exp nnjyu33/nnjyu33@dl30 owner=nnjyu33 file=d:/nnjyu33.emp log=d:/nnjyu33.log compress=y

--将数据库中system用户与sys用户导出(需要具备dba权限)
exp system/manager@TestDB file=E:\sampleDB.dmp owner=(system,sys)

模式三：导出指定表（可以导出一个或多个表）

--导出一个表
exp 用户名/密码@数据库实例 owner=用户名 file=文件存储路径 log=日志存储路径 owner=指定用户 tables=(指定表)

--导出多个表
exp 用户名/密码@数据库实例 owner=用户名 file=文件存储路径 log=日志存储路径 owner=指定用户 statistics=none tables=(指定表1,指定表2,指定表3)

exp nnjy/nnjy@dl30 file=E:\TFF1.dmp log=E:\TFF1.log tables=(TFF1)

exp nnjy/nnjy@dl30 file=E:\TFF1F2.dmp log=E:\TFF1F2.log tables=(TFF1,TFF2)

模式四：导出指定表的指定数据

exp nnjy/nnjy@dl30 file=文件存储路径 log=日志存储路径 tables=(指定表1) query=\where条件\"

--需要使用\转义
exp nnjy/nnjy@dl30 file=E:\conTFF1.dmp log=E:\conTFF1.log tables=(TFF1) query=\"where ATFF11='A01'\"

1.2导入 #

模式一：全量导入（需要具备dba权限，慎用）

--ignore:是否忽略创建错误(已存在的表不重复导入)
imp 用户名/密码@数据库实例 file=文件存储路径 full=y ignore=y

--例子
imp nnjy/nnjy@@dl30 file=e:/nnjy_db.dmp full=y ignore=y

模式二：导入指定用户（推荐）

imp 用户名/密码@数据库实例 file=文件存储路径 fromuser=资源用户名 touser=目标用户名 ignore=y

--例子
--grants=n 不导入权限
imp nnjy/nnjy@dl30 file=E:\nnjy_db.dmp fromuser=nnjy touser=nnjy grants=n

模式三：导入指定表

imp 用户名/密码@数据库实例 file=文件存储路径 owner=指定用户 tables=(指定表)

imp nnjy/nnjy@dl30 file=e:/TFF1_db.dmp owner=nnjy tables=(TFF1)

2.一些实战总结 #

实战中，数据库上有些用户是从现场或者其他用户导过来的，表、大字段、索引表空间都还是原先的表空间，需要改到现在用户的表空间下，导完用户或者表看看是不是都在默认的表空间，不是的话参考下面的语句重建一下即可。

2.1表不在默认表空间的重建语句 #

select u.username,
       u.default_tablespace,
       s.segment_name,
       s.segment_type,
       s.tablespace_name,
       'alter table ' || segment_name || ' move tablespace ' ||
       u.default_tablespace || ';'
  from dba_users u, dba_segments s
 where u.username = s.owner
   and u.default_tablespace != s.tablespace_name
   and u.username ='CCRCWEB'
   and s.segment_type = 'TABLE';

2.2大字段表空间不在默认表空间中的重组语句 #

SELECT T.TABLE_NAME,
       T.COLUMN_NAME,
       T.SEGMENT_NAME,
       T.TABLESPACE_NAME,
       'ALTER TABLE ' || T.TABLE_NAME || ' MOVE LOB(' || T.COLUMN_NAME || ') STORE AS ' || T.SEGMENT_NAME || ' (TABLESPACE ' || 'CCRCDATA' || ' DISABLE STORAGE IN ROW  CHUNK 16384  RETENTION  FREEPOOLS 1  NOCACHE);'
        FROM USER_LOBS T
 WHERE T.TABLESPACE_NAME <> 'CCRCDATA';

2.3索引重建语句 #

select s.segment_name,
       s.segment_type,
       s.tablespace_name,
       'alter index ' || segment_name || ' rebuild tablespace CCRCDATA;'
  from user_segments s
 where s.segment_type = 'INDEX' and tablespace_name<>'CCRCDATA';

--实战使用的重建索引语句
select 'alter index ' || index_name ||
       ' rebuild tablespace WHRSRCDATA;'
  from user_indexes;

3.数据泵导入及一些问题 #

--新建directory并授权
create directory dirdp as 'E:\nmhf';
grant read,write on directory dirdp to system;

--nmgjy是dmp中的用户，nmgjy_cs是要导入的用户
impdp nmgjy_cs/nmgjy_cs DIRECTORY=dirdp dumpfile=nmgrlzy_cs.dmp REMAP_SCHEMA=nmgjy:nmgjy_cs

impdp nmgjy/nmgjy DIRECTORY=dirdp dumpfile=nmgrlzy_cs.dmp REMAP_SCHEMA=nmgjy:nmgjy

impdp nmgrlzy_cs/nmgrlzy_cs DIRECTORY=dirdp dumpfile=nmgrlzy_cs.dmp REMAP_SCHEMA=nmgrlzy:nmgrlzy_cs

impdp nmgrlzy_zs/nmgrlzy_zs DIRECTORY=dirdp dumpfile=nmgrlzy.dmp REMAP_SCHEMA=nmgrlzy:nmgrlzy_zs

--还可以切换表空间
impdp nmgjy_cs/nmgjy_cs DIRECTORY=dirdp dumpfile=nmgrlzy_cs.dmp REMAP_SCHEMA=nmgjy:nmgjy_cs remap_tablespace=EXAMPLE:newtablespace,EXAMPLE_TEMP:newtablespace_temp

--也可以使用系统自带的directory
impdp system/admin@DNACLIENT directory=DATA_PUMP_DIR dumpfile=example.DMP REMAP_SCHEMA=olduser:newuser remap_tablespace=EXAMPLE:newtablespace,EXAMPLE_TEMP:newtablespace_temp

--注意：
1、此处directory使用了系统自带的，如果需要自定义，请使用 create directory命令创建;

2、remap_tablespace多个表空间转换用逗号隔开。