对于Oracle的数据导出和导入,我们之前一直使用exp、imp来处理, 但用imp在导入时经常会要求表空间一致,而博主今天恰恰就遇到了需要变更表空间和用户的情况,这在我们平常的业务场景中也会遇到,那么该如何处理呢?
我们采用Oralce的 “数据泵” Data Dump来处理。
测试环境:Windows Server 2008 R2、Oracle11g
目录
1、数据泵(Data Dump)主要解决问题
①. 比imp/exp更加灵活,支持多种元数据过滤策略,多种导入、导出模式,如将A表空间B用户数据导入C表空间D用户下,则只需REMAP_SCHEMA即可
②. 表空间占用和表空间变更问题,可使用REMAP_TABLESPACE来解决
③. 空表问题,Oracle11g新的表并且表中无数据也为使用过则表的segment空间是不会分配的,这样在使用exp导出时空表便不会被导出,解决这个问题要么给空分配segment或直接使用expdp。
④. 效率问题。普通情况下expdp/impdp比exp/imp效率要高,高多少博主没有测试,有兴趣的不妨验证下,@博主。
2、数据泵使用限制
使用expdp、impdp有条件限制
一是远程使用时必须配置好Database Link,因为expdp、impdp只能在服务端使用,不能再客户端使用;
二是试用前要解决路径配置问题。
3、创建测试数据库
创建源用户和表空间,为了便于操作我们给新创建用户授权的了dba权限,大家在本地使用时注意
/*第1步:创建临时表空间 */
create temporary tablespace test_source_temp
tempfile 'D:\Oracle11g\oradata\test_source_temp.dbf'
size 10m
autoextend on
next 1m maxsize unlimited
extent management local;
/*第2步:创建数据表空间 */
create tablespace test_source
logging
datafile 'D:\Oracle11g\oradata\test_source.dbf'
size 10m
autoextend on
next 1m maxsize unlimited
extent management local;
/*第3步:创建用户并指定表空间 */
create user test_source identified by a123456
default tablespace test_source
temporary tablespace test_source_temp;
/*第4步:给用户授予权限 */
grant connect,resource,dba to test_source;
创建两张测试表,一张基础数据类型,一张含clob、blob、date等复杂数据类型
--表1
create table tab_1
(
tab1_field1 number,
tab1_field2 varchar2(20),
tab1_field3 varchar2(10)
)
--插入测试数据
insert into TAB_1 (tab1_field1, tab1_field2, tab1_field3) values (1, 'a12', 'a13');
insert into TAB_1 (tab1_field1, tab1_field2, tab1_field3) values (2, '张22', '李23');
--表2
create table TAB_2
(
tab2_field1 NUMBER,
tab2_field2 VARCHAR2(20),
tab2_field3 DATE,
tab2_field4 CLOB,
tab2_field5 BLOB
)
--插入测试数据
insert into TAB_2 (tab2_field1,tab2_field2,tab2_field3,tab2_field4,tab2_field5) values ('1','A12',SYSDATE,'测试1','C1');
insert into TAB_2 (tab2_field1,tab2_field2,tab2_field3,tab2_field4,tab2_field5) values ('1','测试22',SYSDATE,'测试2','C2');
insert into TAB_2 (tab2_field1,tab2_field2,tab2_field3,tab2_field4,tab2_field5) values ('1','嘿嘿32',SYSDATE,'测试3','C3');
创建目标用户和表空间
/*第1步:创建临时表空间 */
create temporary tablespace test_target_temp
tempfile 'D:\Oracle11g\oradata\test_target_temp.dbf'
size 10m
autoextend on
next 1m maxsize unlimited
extent management local;
/*第2步:创建数据表空间 */
create tablespace test_target
logging
datafile 'D:\Oracle11g\oradata\test_target.dbf'
size 10m
autoextend on
next 1m maxsize unlimited
extent management local;
/*第3步:创建用户并指定表空间 */
create user test_target identified by a123456
default tablespace test_target
temporary tablespace test_target_temp;
/*第4步:给用户授予权限 */
grant connect,resource,dba to test_target;
4、导出源数据库
这里需要注意:
EXP和IMP是客户端工具程序,它们既可以在客户端使用,也可以在服务端使用。
而EXPDP和IMPDP是服务端的工具程序,他们只能在ORACLE服务端使用,不能在客户端使用。
IMP只适用于EXP导出的文件,不适用于EXPDP导出文件;IMPDP只适用于EXPDP导出的文件,而不适用于EXP导出文件。
我们这里需要使用expdp导出,需在服务端执行。
①.创建逻辑目录
--创建一个dump路径的对象
create directory dump_dir1 AS 'D:\Oracle11g\admin\orcl\dpdump';
当然我们这里也可以使用dump默认目录,查询默认目录,然后执行创建
--查询使用默认dump路径
select * from dba_directories where directory_name='DATA_PUMP_DIR';
--创建一个dump路径的对象
create directory dump_dir1 AS 'D:\Oracle11g\admin\orcl\dpdump';
--查询已创建的dump路径对象
select * from dba_directories where directory_name='DUMP_DIR1';
但正常情况下这个目录只有dba 和system高级用户可以使用,所以我们需要给用户授权,这里我们同时给源数据库用户和目标数据库用户均加上目录授权。
--用户地址读写授权
grant read,write on directory dump_dir1 to test_source;
grant read,write on directory dump_dir1 to test_target;
②.导出源数据库
在数据库服务器上,点击Win+R弹出命令框,输入“cmd”,进入命令操作框,输入以下语句,导出数据文件dmp。
1)按用户导
expdp test_source/a123456 directory=dump_dir1 dumpfile=test_source.dmp schemas=test_source;
2)并行进程parallel
expdp test_source/a123456 directory=dump_dir1 dumpfile=test_source.dmp parallel=40 job_name=test_source
3)按表名导
expdp test_source/a123456 directory=dump_dir1 dumpfile=test_source.dmp tables=tab_1,tab_2;
4)按查询条件导
expdp test_source/a123456 directory=dump_dir1 dumpfile=test_source.dmp tables=tab_1 query='where deptno=20';
5)按表空间导
expdp test_source/a123456 directory=dump_dir1 dumpfile=test_source.dmp tablespaces=test_source;
6)导整个数据库
expdp test_source/a123456 directory=dump_dir1 dumpfile=test_source.dmp full=y;
我采用按用户导出测试,发现报以下错误
然后打开目录“”发现该目录为只读目录,去掉只读选项,确认,同时增加日志文件指向
再次尝试,成功!
5、导入目标数据库
上面我们已经给目标数据库用户加上了目录授权,可以通过下面语句进行导入:
1)导到指定用户下
impdp test_target/a123456 directory=dump_dir1 dumpfile=test_source.dmp schemas=test_target;
2)改变表的owner
impdp test_target/a123456 directory=dump_dir1 dumpfile=test_source.dmp tables=test_target.tab_1,test_target.tab_2 remap_schema=test_target;
3)导入表空间
impdp test_target/a123456 directory=dump_dir1 dumpfile=test_source.dmp tablespaces=test_target;
4)导入数据库
impdb test_target/a123456 directory=dump_dir1 dumpfile=test_source.dmp full=y;
5)追加数据
impdp test_target/a123456 directory=dump_dir1 dumpfile=test_source.dmp schemas=test_target table_exists_action
我们采用导入指定用户下,同时改变他的owner和表空间
impdp test_target/a123456 directory=dump_dir1 dumpfile=test_source.dmp remap_schema=test_source:test_target
remap_tablespace=test_source:test_target
导入完成!
6、验证导入后表空间
我们验证下导入后的表所属用户和表空间是否有变化
所属用户和表空间已发生改变。
为了确保blob、clob等字段所属表空间已改变,我们删除源数据库用户和表空间,测试目标数据库是否正常
--删除源数据库用户
drop user test_source;
--因为所删表空间非空,所以我们加上including contents进行表空间删除
drop tablespace test_source including contents and datafiles;
然后我们查询下带有clob和blob字段的表数据是否正常
成功,完美!