逻辑导入导出exp/imp与数据泵expdp/impdp
传统的导入导出exp/imp
1.概述：
传统的导出导入程序指的是exp/imp,用于实施数据库的逻辑备份和恢复。
导出程序exp将数据库中对象的定义和数据备份到一个操作系统二进制文件中。
导入程序imp读取二进制导出文件并将对象定义和数据载入数据库中
2.
导出和导入数据库对象的四种模式是：
1，数据库模式：导出和导入整个数据库中的所有对象
2，表空间模式：导出和导入一个或多个指定的表空间中的所有对象，10g新增添可传输表空间。
3，用户模式：导出和导入一个用户模式中的所有对象
4，表模式：导出和导入一个或多个指定的表或表分区
实验：
1>scott表的导入导出自己的表
SYS@ PROD>conn scott/tiger
Connected.
SCOTT@ PROD>create table emp1 as select * from emp;
Table created.
SCOTT@ PROD>create table dept1 as select * from dept;
Table created.
quit
drop table dept1 purge;
drop table emp1 purge;
exp scott/tiger file=/home/oracle/backup/empdept1.dmp tables="(emp1,dept1)";
当在tables后面不加双引号时会报错：
-bash: syntax error near unexpected token `('
SQL> drop table emp1 purge;
SQL> drop table dept1 purge;
imp scott/tiger file=/home/oracle/backup/empdept1.dmp
查看数据验证
2>sys导出scott表
exp \'sys/system as sysdba\' file=/home/oracle/backup/sysscott.dmp tables="(scott.emp1,scott.dept1)";
drop table dept1 purge;
drop table emp1 purge;
imp \'sys/system as sysdba\' file=/home/oracle/backup/sysscott.dmp fromuser=scott;
3>导入导出用户
exp test/test file=/home/oracle/backup/test.dmp owner=test;
drop user test cascade;
grant connect,resource to test identified by test;
grant dba to test;
imp test/test file=/home/oracle/backup/test.dmp full=y;
如果用sys来完成也可以使用如下命令：
imp 'sys/system@prod as sysdba' file=/home/oracle/backup/scott.dmp fromuser=scott touser=scott
sys用户也可以将导出的scott的内容导入给其他用户
imp 'sys/system@prod as sysdba' file=/home/oracle/backup/scott.dmp fromuser=scott touser=tim
4>导入导出表空间
SYS@ PROD>create tablespace tb1 datafile '/u01/app/oracle/oradata/PROD/mytb1.dbf' size 5M;
scott用户下:
create table t1(year number(4),month number(2),amount number(2,1)) tablespace tb1;
insert into t1 values(1991,1,1.1);
insert into t1 values(1991,2,1.2);
insert into t1 values(1991,3,1.3);
insert into t1 values(1991,4,1.4);
commit;
alter tablespace tb1 read only;
exp \'/ as sysdba\' tablespaces=tb1 transport_tablespace=y file=/home/oracle/backup/exp_tb1.dmp；
将数据文件/u01/app/oracle/oradata/PROD/mytb1.dbf和/u01/app/oracle/oradata/PROD/mytb1.dbf复制到另一台设备进行导入,这也是远程导入导出
imp userid=\'/ as sysdba\' tablespaces=tb1 transport_tablespace=y file=/home/oracle/backup/exp_tb1.dmp datafiles=/u01/app/oracle/oradata/PROD/mytb1.dbf;
验证在这台设备有没有T1。
SQL>select tablespace_name,status from dba_tablespaces;
SQL>select * from scott.t1;
重设回读写方式
SQL>alter tablespace tb1 read write;
5>
什么叫自包含：
当前表空间中的对象不依赖该表空间之外的对象。
例如：有TEST表空间,里面有个表叫T1，如果在T1上建个索引叫T1_idx,而这个索引建在USERS表空间上，由于T1_idx索引是依赖T1表的，
那么，TEST表空间是自包含的，可以迁移，但会甩掉T1_idx索引，USERS表空间不是自包含的，不符合迁移条件。
检查表空间是否自包含可以使用程序包
如上面的例子
SQL> execute dbms_tts.transport_set_check('USERS');
SQL> select * from TRANSPORT_SET_VIOLATIONS;
VIOLATIONS
------------------------------------------------------------------------------------------------------------------------
ORA-39907: 索引 SCOTT.EMP1_IDX (在表空间 TEST 中) 指向表 SCOTT.EMP1 (在表空间 USERS 中)。
6>导出整个数据库
exp 'sys/system@prod as sysdba' file=/home/oracle/backup/full.dmp full=y
补充1：使用Query选项选择性导出数据
exp n1/n1 file=query_bak.dmp query=\"where object_type=\'TABLE\'\" tables=test
补充2：可以得到DDL语句
exp n1/n1 rows=n compress=n tables=test file=test.dmp log=exp_test.log buffer=10240000
imp n1/n1 rows=n full=y ignore=y show=y file=test.dmp log=imp_test.log buffer=10240000
rows 导出的数据行
compress 是否压缩导出的文件
log 屏幕输出的日志文件
ignore 忽略创建错误
buffer 数据缓冲区大小
数据泵expdp/impdp
数据泵优点：
1）改进性能（较传统的exp/imp速度提高1-2个数量级）
2）重启作业能力
3）并行执行能力
4）关联运行作业能力
5）估算空间需求能力
6）操作网络方式
mkdir -p /home/oracle/dp_dump
chmod 777 /home/oracle/dp_dump
sqlplus / as sysdba
create directory dump_dir as '/home/oracle/dp_dump';
grant read,write on directory dump_dir to scott;
1>导出用户users
expdp scott/tiger dumpfile=scott.dmp directory=dump_dir schemas=scott;
导入到同用户下面
impdp scott/scott directory=dump_dir dumpfile=scott.dmp schemas=scott;
导入到指定用户下test下
impdp test/test directory=dump_dir dumpfile=scott.dmp remap_schema=scott:test;
比如是自己的创建的用户不是系统scott用户的话，在导出的时候可能会报错说此用户已存在，这是在语句后面加exclude=user即可。
注意：解决10g IMPDP导入JOB失败的方法,事先创建用户，赋予imp_full_database权限：grant imp_full_database to test;
2>导出tables
expdp scott/scott dumpfile=emp1.dmp directory=dump_dir tables=scott.emp1;
从一个用户导出的表导入到不同用户下
impdp test/test dumpfile=emp1.dmp directory=dump_dir tables=scott.emp1 remap_schema=scott:test;
3>通过query查询条件导出
expdp scott/tiger dumpfile=test_query.dmp directory=dump_dir tables=scott.emp query='" where rownum < 5"';
4>将tablespaces导出
expdp \'sys/oracle as sysdba\' dumpfile=users.dmp directory=dump_dir tablespaces=users,system;
5>将数据库导出
expdp system/oracle dumpfile=full.dmp directory=dump_dir full=y;
expdp \'sys/oracle as sysdba\' dumpfile=dump_dir:full.dmp full=y;
6>
导出dblink
expdp \'sys/oracle as sysdba\' dumpfile=dumpdir:db_link.dmp full=y include=db_link
job
expdp \'sys/oracle as sysdba\' dumpfile=dumpdir:job.dmp full=y include=job
存储过程
expdp \'sys/oracle as sysdba\' dumpfile=dumpdir:procedure.dmp full=y include=procedure
视图
expdp \'sys/oracle as sysdba\' dumpfile=dumpdir:view.dmp full=y include=view
导出物化视图
expdp \'sys/oracle as sysdba\' dumpfile=dumpdir:mview.dmp full=y include=materialized_view
包
expdp \'sys/oracle as sysdba\' dumpfile=dumpdir:package.dmp full=y include=package
触发器
expdp \'sys/oracle as sysdba\' dumpfile=dumpdir:trigger.dmp full=y include=trigger
序列
expdp \'sys/oracle as sysdba\' dumpfile=dumpdir:sequence.dmp full=y include=sequence
索引
expdp \'sys/oracle as sysdba\' dumpfile=dumpdir:index.dmp full=y include=index
约束
expdp \'sys/oracle as sysdba\' dumpfile=dumpdir:constraint.dmp full=y include=constraint
函数
expdp \'sys/oracle as sysdba\' dumpfile=dumpdir:function.dmp full=y include=function
同义词
expdp \'sys/oracle as sysdba\' dumpfile=dumpdir:synonym.dmp full=y include=synonym
导出权限
expdp \'sys/oracle as sysdba\' dumpfile=dumpdir:grant.dmp full=y include=grant
导出以上所有对象
expdp \'sys/oracle as sysdba\' dumpfile=dumpdir:object.dmp full=y include=function,package,view,sequence,trigger,index,constraint,procedure,job,db_link
导出固定的两张表
expdp \'sys/oracle as sysdba\' dumpfile=dumpdir:emp_dept.dmp schemas=scott include=table:\"IN \(\'EMP\',\'DEPT\'\)\"
不导出这两张表
expdp \'sys/oracle as sysdba\' dumpfile=dumpdir:par_scott.dmp schemas=scott exclude=table:\"IN \(\'EMP\',\'DEPT\'\)\"
导出全库数据以外的所有对象定义
expdp \'sys/oracle as sysdba\' dumpfile=dumpdir:full_structure.dmp full=y content=metadata_only
只导出数据
expdp \'sys/oracle as sysdba\' dumpfile=dumpdir:full_structure.dmp full=y content=data_only
4路并行导出
expdp \'sys/oracle as sysdba\' dumpfile=dumpdir:full_structure_%U.dmp full=y content=metadata_only parallel=4 filesize=30m
排除导出
expdp \'/ as sysdba\' dumpfile=dump_dir:full.dmp exclude=audit,tablespace,profile,sys_user,role,grant,resource_cost,trusted_db_link,directory,context,system_procobjact,schema reuse_dumpfiles=y full=y
7>远程导入导出（从一台设备到另一台设备上）
192.168.3.88这台设备作为源端：
mkdir -p /home/oracle/dp_dump
chmod 777 /home/oracle/dp_dump
sqlplus / as sysdba
create directory dump_dir as '/home/oracle/dp_dump';
grant read,write on directory dump_dir to scott;
expdp scott/tiger dumpfile=scott.dmp directory=dump_dir schemas=scott;
192.168.3.99这台设备作为目标端
mkdir -p /home/oracle/dp_dump
chmod 777 /home/oracle/dp_dump
sqlplus / as sysdba
create directory dump_dir as '/home/oracle/dp_dump';
grant read,write on directory dump_dir to public;
创建远程连接
相关的查询字典：dba_db_links
create public database link test_link connect to scott identified by tiger using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.88)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = prod)))';
注：using后面是tns服务名也可指定服务名的内容像我写的一样。
impdp test1/test directory=dump_dir schemas=scott network_link=test_link remap_schema=scott:test1 exclude=user;
注：其中test1是我自己创建的用户，要把88端的scott用户下的表远程导入到99端test1用户下。
配置完以后可以用sqlplus sys/oracle@网络服务名(源端):1521/实例名试连一下源端，如果失败，把using配置的这个tns复制配置到99上即可。
完，验证成功。
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
exp/imp使用常见的问题
1.在用exp导出报错是：EXP-00091：Exporting questionable statistics
解决思路：
首先用过查询字符集来判断是否和客户端有差异。
SYS@ PROD>select * from v$nls_parameters where parameter='NLS_CHARACTERSET';
PARAMETER VALUE
-------------------------------- --------------------------------------------------
NLS_CHARACTERSET AL32UTF8
然后再操作系统层面上查看$NLS_LANG的设置如下：一般来说结果要么没有设置，要么就是设置有问题。
echo $NLS_LANG
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
再次运行导出语句成功
exp n1/n1 tables=t file=t.dmp rows=n
那么要是在有警告的情况下能否导入。
这就要在导入的例如test0用户下创建一个和导出的表的表结构一样的一张表，然后
imp test0/oracle file=t.dmp tables=t statistics=always;
发现导入成功。
2.IMP-00013的问题更改dba，和imp_full_database权限即可，不建议更改dba权限。
3.有时用strace exp...可以查看追踪哪里出错
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
得到一个dump怎么查看里面有哪些表，可以使用strings，awk,sed结合
strings test.dmp |grep "create table "|awk '{print $3}'|sed 's/"//g'
