oracle导入导出dmp文件,即是对数据库的还原及备份。导入导出是通过在命令窗口执行命令实现(EXP/IMP 或 EXPDP/IMPDP)。
1、EXP
使用exp导出表时,若表未分配空间则该表可能无法导出来(跟数据库版本相关)。所以先使用脚本手动给表分配空间。
----执行查询出来的sql语句即可
SELECT 'ALTER TABLE ' || A.TABLE_NAME || ' ALLOCATE EXTENT;'
FROM USER_TABLES A
WHERE NOT EXISTS (
SELECT 1 FROM USER_SEGMENTS B WHERE A.TABLE_NAME = B.SEGMENT_NAME
) AND A.TABLESPACE_NAME IS NOT NULL;
- exp命令参数详情
执行命令 exp help=y ,查看exp的所有参数以及使用方法。常用命令参数如下
关键字 说明(默认值) USERID 用户名/口令 BUFFER 数据缓冲区大小 FILE 输出文件(EXPDAT.DMP) LOG 屏幕输出的日志文件 FULL 导出整个文件(N) OWNER 所有者用户名列表 TABLES 表名列表 PARFILE 参数文件名 FILESIZE 每个转储文件的最大大小 QUERY 用于导出表的子集的select子句 GRANTS 导出权限(Y) INDEXES 导出索引(Y)
- exp命令及参数使用
导出用户:登录sys用户导出/登录自身用户导出
exp userid=\"sys/123@127.0.0.1:1521/orcl as sysdba\" file=d:/daochu.dmp owner=(username) log=d:/daochu.log
exp userid=username/password@127.0.0.1:1521/orcl file=d:/daochu.dmp log=d:/daochu.log
导出表:登录sys用户导出/登录自身用户导出
exp userid=\"sys/123@127.0.0.1:1521/orcl as sysdba\" file=d:/daochu.dmp log=d:daochu.log tables=(username.tablename) QUERY='where id = 10'
exp userid=username/password@10.0.0.1:1521/ORCL file=d:/daochu.dmp log=d:/daochu.log tables=(tablename) QUERY='where id = 10'
导出整个库:登录sys用户导出
exp userid=\"sys/123@127.0.0.1:1521/orcl as sysdba\" file=d:/daochu.dmp log=d:daochu.log FULL=y
2、IMP
把通过exp导出的dmp文件,导入到数据库。
执行命令 imp help=y ,查看imp 的所有参数以及使用方法。
导入用户:登录sys用户导入/登录自身用户导入
imp userid=\"sys/123@127.0.0.1:1521/orcl as sysdba\" file=d:/daochu.dmp owner=(username) log=d:/daochu.log
imp userid=username/password@127.0.0.1:1521/orcl file=d:/daochu.dmp log=d:/daochu.log
导入表:登录sys用户导入/登录自身用户导入
imp userid=\"sys/123@127.0.0.1:1521/orcl as sysdba\" file=d:/daochu.dmp log=d:daochu.log tables=(username.tablename)
imp userid=username/password@10.0.0.1:1521/ORCL file=d:/daochu.dmp log=d:/daochu.log tables=(tablename)
导入整个库:登录sys用户导入
imp userid=\"sys/123@127.0.0.1:1521/orcl as sysdba\" file=d:/daochu.dmp log=d:daochu.log FULL=y
3、EXPDP
--1、创建逻辑目录,该命令不会在操作系统创建真正的目录,最好以system等管理员创建。
create directory dump_test as 'd:\test\dump';
--2、查看管理理员目录
select * from dba_directories;
--3、给用户赋予在指定目录的操作权限,最好以system等管理员赋予。
grant read,write on directory dump_test to username;
导出用户:使用一般用户/使用sys用户
expdp username/password@127.0.0.1:1521/orcl DIRECTORY=dump_test DUMPFILE=expdp.dmp logfile=expdp.log
expdp userid=\"sys/123@127.0.0.1:1521/orcl as sysdba\" DIRECTORY=dump_test DUMPFILE=expdp.dmp
logfile=expdp.log schema=(username1,username2)
导出表:
expdp username/password@127.0.0.1:1521/orcl TABLES=(table1,table2) dumpfile=expdp.dmp DIRECTORY=dump_test logfile=expdp.log QUERY='where id = 10'
导出整库
expdp system/manager DIRECTORY=dump_test DUMPFILE=full.dmp FULL=y filelog=full.log
多进程并发加速
expdp username/password@127.0.0.1:1521/orcl directory=dump_test dumpfile=expdp.dmp parallel=4 job_name=scott3
--parallel=4 表示 4个进程同时执行
4、IMPDP
导入到指定用户
impdp username/password@127.0.0.1:1521/orcl DIRECTORY=dump_test DUMPFILE=expdp.dmp logfile=impdp.log
remap_schema=username:usename2 remap_tablespace=db_data1:db_data2
--remap_schema 当用户不一样时需要做映射;同理表空间也需要映射
导入数据库:
impdb system/manager DIRECTORY=dump_test DUMPFILE=full.dmp FULL=y;
5、使用parfile参数
创建文件 expdp.par,内容如下:
userid='username/password@127.0.0.1:1521/orcl'
DIRECTORY=dump_testtables=(table1,table2)
dumpfile=expdp_tables_%U.dmp --使用通配符 %U
buffer=40000000 --缓冲区大小
parallel=4 --4个工作进程同时运作,加速效率,生成4个dmp文件
filesize=1073741824 --1024*1024*1024B,每个dmp的文件大小
content=all
LOGFILE=expdp_dcdb.logJOB_NAME=expdp_dcdb_job1
导出表则输入命令 expdp parfile=d:/expdp.par
6、EXP/IMP 和 EXPDP/IMPDP 对比
- 速度:expdp/impdp 更快
- 限制:exp/imp 可以导出到本地,或从本地导入;而expdp/impdp 只能在服务器操作。
- 并发:expdp/impdp 支持多进程并发
7、注意事项
- USERID 必须是命令行中的第一个参数。
- 使用sys用户导出导入,必须用 \"\" 进行转义,否则无法识别 as。
- 若想把数据库全部导出,然后还原到另一个机器上。建议先在机器上建好对应的表空间和用户。然后导入各个用户的dmp文件。使用 full=y 参数有缺陷,例如从windows上导出的dmp文件导入到linux上,表空间的地址则不一样,则无法创建成功。