锐客网

  1. 首页 > it技术 > >

数据库导入导出逻辑备份方法总结

【EXP常用参数】
USERID即用户名/密码该参数就必须且为第一个；
FILE指定数据输出文件路径；
LOG指定日志输出文件路径；
TABLES导出指定表数据；
FULL完全导出整个文件(N)；
ROWS导出数据行(Y)；
QUERY用于导出表的子集的 select 子句；

【示例】
1、【全库模式】将数据库orcl完全导出,用户名scott密码scott数据文件导出到D:/orcl/scott.dmp，日志文件到出到D:/orcl/scott.log
exp scott/scott@orcl file = D:/orcl/scott.dmp log =D:/orcl/scott.log full = y
2、【表模式】将数据库中的表emp、dept导出到D:/orcl/scott_empdept.dmp
exp scott/scott@orcl file = D:/orcl/scott_empdept.dmptables = (emp,dept)
3、【用户模式】将243数据库中用户ng_lxj1和ng_lxj2的所有数据导出到D:/orcltest/ ng_lxj_user.dmp
exp system/manager@243 file = D:/orcltest/ng_lxj_user.dmpowner = (ng_lxj1, ng_lxj2)
4、将数据库中的表emp中SAL字段大于1000的记录导出到D:/orcl/scott_emp.dmp
exp scott/scott@orcl file = D:/orcl/sys_scott.dmptables = (emp) query = \”where sal > 1000\”
5、将数据库中的表emp仅导出建表语句不导出数据行到D:/orcl/scott_empddl.dmp
exp scott/scott@orcl file = D:/orcl/scott_empddl.dmptables = emp rows = n

【全部参数】
USERID用户名/口令FULL导出整个文件 (N)
BUFFER数据缓冲区大小OWNER所有者用户名列表
FILE输出文件TABLES表名列表
COMPRESS导入到一个区 (Y)RECORDLENGTH IO 记录的长度
GRANTS导出权限(Y)INCTYPE增量导出类型
INDEXES导出索引(Y)RECORD跟踪增量导出 (Y)
DIRECT直接路径(N)TRIGGERS导出触发器 (Y)
LOG屏幕输出的日志文件STATISTICS分析对象 (ESTIMATE)
ROWS导出数据行(Y)PARFILE参数文件名
CONSISTENT交叉表的一致性 (N)CONSTRAINTS导出的约束条件 (Y)
OBJECT_CONSISTENT只在对象导出期间设置为只读的事务处理 (N)
FEEDBACK每 x 行显示进度 (0)
FILESIZE每个转储文件的最大大小
FLASHBACK_SCN用于将会话快照设置回以前状态的 SCN
FLASHBACK_TIME用于获取最接近指定时间的 SCN 的时间
QUERY用于导出表的子集的 select 子句
RESUMABLE遇到与空格相关的错误时挂起 (N)
RESUMABLE_NAME用于标识可恢复语句的文本字符串
RESUMABLE_TIMEOUTRESUMABLE 的等待时间
TTS_FULL_CHECK对 TTS 执行完整或部分相关性检查
TABLESPACES要导出的表空间列表
TRANSPORT_TABLESPACE 导出可传输的表空间元数据 (N)
TEMPLATE调用 iAS 模式导出的模板名

【IMP常用参数】
USERID即用户名/密码该参数就必须且为第一个；
FILE指定数据输出文件路径；
LOG指定日志输出文件路径；
IGNORE忽略创建错误(N)，当要导入的表已存在时若该参数为Y则不报错仅导入数据，否则将报错；若导入的表不存在，则将自动新建该表，需要注意的是，该表所建的表空间一般是导出时表所在表空间，若导入的库中无此表空间则会报错；
FROMUSER所有者用户名列表；
TOUSER用户名列表；
TABLES导出指定表数据；
FULL完全导出整个文件(N)；
ROWS导出数据行(Y)；

【示例】
1、【全库模式】将备份数据库文件中的数据导入数据库orcl中,用户名scott密码scott，数据文件路径D:/orcl/scott.dmp，日志文件路径D:/orcl/scott.log
imp scott/scott@orcl file = D:/orcl/scott.dmp log =D:/orcl/scott.log full = y ignore = y
2、【表模式】将备份数据库文件中的表emp、dept数据导入数据库orcl中,用户名scott密码scott，数据文件路径D:/orcl/scott.dmp
imp scott/scott@orcl file = D:/orcl/scott.dmp log =D:/orcl/scott.log ignore = y tables = (emp,dept)
另外，如果导出时使用了表模式，导入时要导入所有表数据，则也可以使用full = y，如：
imp scott/scott@orcl file = D:/orcl/scott_empdept.dmp ignore= y full = y
3、【用户模式】备份数据文件中存在ng_lxj1、ng_lxj2两用户数据，数据文件路径D:/orcltest/ng_lxj_user.dmp
将ng_lxj1的数据导入到ng_lxj中：
imp system/manager@243 file = D:/orcltest/ng_lxj_user.dmpfromuser = ng_lxj1 touser = ng_lxj
将ng_lxj1和ng_lxj2的数据均导入到ng_lxj中：
imp system/manager@243 file = D:/orcltest/ng_lxj_user.dmpfromuser = (ng_lxj1,ng_lxj2) touser = (ng_lxj,ng_lxj)
注意ng_lxj要写两遍与之前的对应，若仅写一个，Oracle会默认没得到对应的将导入到本用户下，如：
imp system/manager@243 file = D:/orcltest/ng_lxj_user.dmpfromuser = (ng_lxj1,ng_lxj2)
该命令将从ng_lxj1导入ng_lxj1，ng_lxj2导入ng_lxj2
将ng_lxj1的数据分别导入ng_lxj1和ng_lxj2两个用户：
不能写成imp system/manager@243 file = D:/orcltest/ng_lxj_user.dmpfromuser = (ng_lxj1,ng_lxj1) touser = (ng_lxj1,ng_lxj2)
否则会报错：
IMP-00034: 警告: 在导出文件中未找到 FromUser "NG_LXJ1"
猜测：Oracle将数据文件抽取到缓存区，一旦使用过就会进行清除。
所以，一个用户导向两个用户需要分开写两条命令语句。
将ng_lxj1用户下的t1表的数据导入ng_lxj2用户中：
imp system/manager@243 file = D:/orcltest/ng_lxj_user.dmpfromuser = ng_lxj1 touser = ng_lxj2 tables = t1
4、从备份数据文件中，仅导入建表语句而不导入数据记录，文件路径D:/orcl/scott.dmp
imp scott/scott@orcl file = D:/orcl/scott.dmp full = yignore = y rows = n
5、使用参数文件
imp system/manager@243 parfile=bible_tables.par
bible_tables.par参数文件：
file = D:/orcltest/ng_lxj_user.dmp fromuser = ng_lxj1touser = ng_lxj

【全部参数】
USERID用户名/口令FULL导入整个文件 (N)
BUFFER数据缓冲区大小FROMUSER所有者用户名列表
FILE输入文件(EXPDAT.DMP)TOUSER用户名列表
SHOW只列出文件内容 (N)TABLES表名列表
IGNORE忽略创建错误 (N)RECORDLENGTH IO 记录的长度
GRANTS导入权限(Y)INCTYPE增量导入类型
INDEXES导入索引(Y)COMMIT提交数组插入 (N)
ROWS导入数据行(Y)PARFILE参数文件名
LOG屏幕输出的日志文件CONSTRAINTS导入限制 (Y)
DESTROY覆盖表空间数据文件 (N)
INDEXFILE将表/索引信息写入指定的文件
SKIP_UNUSABLE_INDEXES 跳过不可用索引的维护 (N)
FEEDBACK每 x 行显示进度 (0)
TOID_NOVALIDATE跳过指定类型 ID 的验证
FILESIZE每个转储文件的最大大小
STATISTICS始终导入预计算的统计信息
RESUMABLE在遇到有关空间的错误时挂起 (N)
RESUMABLE_NAME用来标识可恢复语句的文本字符串
RESUMABLE_TIMEOUTRESUMABLE 的等待时间
COMPILE编译过程, 程序包和函数 (Y)
STREAMS_CONFIGURATION 导入流的一般元数据 (Y)
STREAMS_INSTANTIATION 导入流实例化元数据 (N)
DATA_ONLY仅导入数据 (N)
下列关键字仅用于可传输的表空间
TRANSPORT_TABLESPACE 导入可传输的表空间元数据 (N)
TABLESPACES 将要传输到数据库的表空间
DATAFILES 将要传输到数据库的数据文件
TTS_OWNERS 拥有可传输表空间集中数据的用户
【数据库导入导出逻辑备份方法总结】 【P.S．】
1、高版本的Export导出来的转储文件，低版本的Import读不了；低版本的Export导出来的转储文件，高版本的Import可以进行读取。
2、从Oracle低版本的Export数据可以Import到Oracle高版本中，但限于Oracle的相邻版本，两个不相邻版本间进行转换应借助中间版本。
3、Oracle 10g export的文件且包含大字段通过Oracle 11g客户端Import到Oracle10g数据库时会报“IMP-00058”、“ORA-00942”表不存在的错误，且含几个大字段会报几次，但最终数据会成功导入且无错误。想不报错就换个10g客户端或用11g客户端导入11g服务器数据库。
4、exp/imp可以做到在不同版本Oracle、不同数据库上的迁移，在进行不同数据库上迁移时很可能会出现表空间不存在的报错。解决方法：首先，我们可以尝试先建表且指定新的表空间，然后使用imp且ignore=y仅导入数据；若仍报表空间不存在则最佳方法是改变exp，将需要备份的表的表空间指定为USERS（系统默认表空间）；也可以在要导入的数据库上建一个极小的与导出数据库表空间同名的表空间。
5、在有主外键关键时，若主外键表均存在于备份文件，则imp会自动识别并在导入数据后建立约束，若仅导入外键表而不导入主键表则数据成功导入但会报错且外键约束丢失。
6、多表多条件导出，tables指定多表，而query不能针对不同表做不同条件限制，可以写多条语句分别导出各表，也可以使用expdp来多表多条件导出。
7、导入大表时存储分配失败，默认的EXP时，compress = Y，也就是把所有的数据压缩在一个数据块上。导入时, 如果不存在连续一个大数据块，则会导入失败。导出80M以上的大表时，记得compress= N，则不会引起这种错误。
8、导入导出时有三种可选模式（全库、用户、表），exp/imp使用时必须选用其中一种模式且全文件模式时不能出现其他模式；
9、使用用户模式导入导出时，USERID需要使用高权限用户如system/manager；
10、借助PL/SQL，在表中不存在大字段时，我们还可以借助PL/SQL的导出工具来导入导出；需要注意的是，从一个库中导出的文件可能默认的时间格式与另一个库不同而产生出错，且导出的纯SQL insert语句有时间不能直接用于DB2，里面对于空、大字段等的定义是不同的。
11、从Oracle 10g开始提供了称为数据泵新的工具expdp/impdp，它为Oracle数据提供高速并行及大数据的迁移。imp/exp可以在客户端调用，但是expdp/impdp只能在服务端，因为在使用expdp/impdp以前需要在数据库中创建一个Directory。

数据泵的特点与传统导出导入的区别
1. EXP和IMP是客户端工具程序，它们既可以在客户端使用，也可以在服务端使用；EXPDP和IMPDP是服务端的工具程序，他们只能在ORACLE服务端使用，不能在客户端使用。
2.EXP和IMP效率比较低.EXPDP和IMPDP效率高
3.数据泵功能强大并行、过滤、转换、压缩、加密、交互等等
4.数据泵不支持9i以前版本，EXP/IMP短期内还是比较适用
5.数据泵导出包括导出表,导出方案,导出表空间,导出数据库4种方式，而imp/exp是3种
6.IMP只适用于EXP导出的文件，不适用于EXPDP导出文件；IMPDP只适用于EXPDP导出的文件，而不适用于EXP导出文件
【expdp/impdp】
该命令仅能在服务端使用！也就是说必须在数据库导入或导出的服务器上使用该命令！
使用该命令需要逻辑目录与物理目录均存在！
如，导出本地orcl数据库中soctt用户的emp,dept两表步骤：
1)创建逻辑目录
该命令不会在操作系统创建真正的目录，最好以system等管理员创建
登录SQLPLUS:
sqlplus system/manager@orcl as sysdba
创建逻辑目录：
create directory dptest as ‘d:\dptest’;
2)给导出用户授权
grant read,write on directory dptest to scott;
3)建立物理目录
在D盘新建目录dptest
4)导出emp、dept表
expdp scott/scott@orcl tables = emp,dept dumpfile =empdept_dp.dmp directory = dptest
【expdp常用参数】
DIRECTORY供转储文件和日志文件使用的目录对象，需要在导出前创建；
DUMPFILE指定导出数据文件名，不填写目录路径则默认为direcory指定的目录，也可指定已创建的目录对象；
CONTENT指定要导出的内容，可选参数有all、data_only和metadata_only，默认为all，当参数为all时将导出DDL及其数据，data_only只导出数据，metadata_only只导出DDL；
EXCLUDE排除特定的对象类型，例如 EXCLUDE=TABLE:EMP；
INCLUDE包括特定的对象类型，例如 INCLUDE=TABLE_DATA；
JOB_NAME要创建的导出任务的名称，若不指定则会默认会创建一个类似于SYS_EXPORT_SCHEMA_01的对象作为JOB名；
LOGFILE指定日志文件名(export.log)；
NOLOGFILE不写入日志文件(N)；
FULL导出整个数据库(N)；
SCHEMAS要导出的方案的列表(登录方案)；
TABLES指定要导出的表的列表；
TABLESPACES 指定要导出的表空间的列表；
QUERY用于导出表的子集的谓词子句；
【示例】
1、【全库模式】将本地数据库全库导出到已创建的目录对象dptest中
expdp system/manager@orcl dumpfile = orcl_dp.dmp directory= dptest full = y
2、【表模式】将本地数据库scott中的表emp、dept导出到已创建的目录对象dptest中
expdp scott/scott@orcl tables = emp,dept dumpfile =empdept_dp.dmp directory = dptest
3、【用户模式】将本地数据库scott用户数据导出到已创建的目录对象dptest中
expdp scott/scott@orcl schemas = scott dumpfile = scott_dp.dmpdirectory = dptest
4、【表空间模式】将本地数据库中USERS和TEMP表空间下数据导出到已创建的目录对象dptest中
expdp system/manager@orcl dumpfile = users_temp_dp.dmpdirectory = dptest tablespace = users,temp
5、按表导出且指定条件（可实现多表多条件），将表emp中SAL字段大于1000的记录导出
expdp scott/scott@orcl directory= dptest dumpfile=expdp.dmp tables=empquery=' where sal > 1000'
6、并行进程parallel
expdp scott/scott@orcl directory= dptest dumpfile=scott1.dmp parallel=40 job_name=scott1
【impdp常用参数】
DIRECTORY供转储文件和日志文件使用的目录对象，需要在导入前创建；
DUMPFILE指定导入数据文件名，不填写目录路径则默认为direcory指定的目录，也可指定已创建的目录对象；
CONTENT指定要导入的内容，可选参数有all、data_only和metadata_only，默认为all，当参数为all时将导入DDL及其数据，data_only只导入数据，metadata_only只导入DDL；
EXCLUDE排除特定的对象类型，例如 EXCLUDE=TABLE:EMP；
INCLUDE包括特定的对象类型，例如 INCLUDE=TABLE_DATA；
JOB_NAME要创建的导入任务的名称；
LOGFILE指定日志文件名(import.log)；
NOLOGFILE不写入日志文件(N)；
FULL导出整个数据库(N)；
SCHEMAS要导出的方案的列表(登录方案)；
TABLES指定要导出的表的列表；
TABLESPACES 指定要导出的表空间的列表；
QUERY用于导出表的子集的谓词子句；
REMAP_SCHEMA将一个方案中的对象加载到另一个方案；
REMAP_TABLESPACE将表空间对象重新映射到另一个表空间，在不同数据库导入导出时用于更改表空间；
【示例】
1、【全库模式】将数据文件全库导入本地数据库，数据文件存放于到已创建的目录对象dptest中
impdp system/manager@orcl dumpfile = orcl_dp.dmpdirectory = dptest full = y
2、【表模式】将数据文件中表emp、dept导入本地数据库scott用户下
impdp scott/scott@orcl tables = emp,dept dumpfile =empdept_dp.dmp directory = dptest
将数据文件中表emp导入本地数据库且将其拥有者改为system
impdp system/manager dumpfile = empdept_dp.dmpdirectory = dptest tables=scott.dept remap_schema=scott:system
3、【用户模式】将数据文件导入本地数据库scott用户下
impdp scott/scott@orcldumpfile = scott_dp.dmp directory = dptest SCHEMAS=scott;
4、更换表空间
impdp scott/scott@orcl dumpfile = scott_dp.dmpdirectory = dptestremap_tablespace=users:temp
【大例子】
从一个用户expdp导出再impdp导入到另一个用户
如：Windows为A服务器，Linux为B服务器,数据库用户为test，把A服务器的数据迁移到B服务器中
在A服务器操作：
1、
SQL> create directory expdp_dir as 'D:\mzl\backup';
SQL> grant read,write on directory expdp_dir totest；
2、在windows目录创建目录D:\mzl\backup
3、在DOS命令窗口导出：
expdp test/test DIRECTORY=expdp_dir DUMPFILE=test.dmplogfile=testexpdp.log
在B服务器中操作：
4、SQL> create directory impdp_diras '/home/oracle/impdp_dir';
SQL> grantread,write on directory impdp_dir to test；
1、 在系统中需要有/home/oracle/impdp_dir目录，在impdp_dir目录下必须有读写权限
（chmod 777 impdp_dir）
5、用ftp把A服务器导出的数据上传到B服务器的/home/oracle/impdp_dir目录中
在A服务器中配置好B服务器的服务器名,在A服务器导入数据
6、在DOS命令窗口导出：
imppdp test/test@B_database DIRECTORY=impdp_dirDUMPFILE=test.dmp logfile=testimpdp.log
（这里注意大小写，如果test.dmp在linux中为大写，必须更改为大写。Linux区分大小写）

    推荐阅读


    上一篇：roaming

    下一篇：经典培训小游戏（一）