使用expdp/impdp迁移schema

今天要把一个db_1上某个用户的表结构迁移到另一个db_2上，两者的用户名要保持相同，表空间也必须相同。



先说下我尝试过的方法：
1.使用下面的脚本通过dbms_metadata.get_ddl过程直接获取test用户所有表的DDL语句。
set long 100000 linesize 130 pagesize 1000                                                                                                  
col schema_ddl for a1000                                                                                                                         
spool schema_ddl.txt
select replace(to_char(dbms_lob.substr(dbms_metadata.get_ddl('TABLE',table_name,'TEST'), 4000, 1)),'"','')||';' schema_ddl  from dba_tables  WHERE owner='TEST';
spool off
但是，你看到脚本中的dbms_lob.substr()就发现了，有些表（分区表，特别是那些有n个分区的分区表）的DDL语句就毫不犹豫地超过了4000个字符，所以，这个方法经过简单测试后因不实用而放弃。不过，好像dbms_metadata.get_ddl()有个参数可以把storage那些信息不输出，我懒得测试了，不知道具体效果如何。试验过的朋友记得留言告诉我下。



2.使用exp/imp进行只导schema定义
导出使用以下脚本（direct=y 表示使用直接路径， rows=n 表示不导出数据，只导定义； 而且还发现exp所使用的用户不能使用sysdba角色）
exp test/test direct=y rows=n file=/oracle/test_schema_20130802.dmp owner=test log=/oracle/exp.log
导入使用以下脚本（buffer=20480000 一定要加上，否则可能报错）
imp test/test buffer=20480000 ignore=y  fromuser=test touser=test file=/oracle/test_schema_20130802.dmp log=/oracle/imp.log

经过实验测试，exp耗时5分钟左右，导出的文件大小为100M，而imp的时候就蛋疼了，耗时2小时才导入完成，而且还报一堆错。报错也就算了，还莫名其妙地把一个tablespace搞得在dba_free_space视图中查询不到了，v$tablespace中却还可以看到。被搞伤了，果断换方案！



3.最后决定使用expdp/impdp来完成

前期准备工作，在db_2上建立相应的表空间，建立相应的test用户，然后授予test用户在各表空间的磁盘配额。

create tablespace ts_work
logging
datafile '/oradata/ts_work01.dbf'
size 2g
autoextend on
next 2g maxsize 30g
extent management local;

--重做时，使用下面的语句删除表空间
--drop tablespace ts_work including contents and datafiles cascade constraints;

create user test
default tablespace ts_work
identified by "test";

grant unlimited tablespace to test;



使用expdp/impdp方式来导出和导入表结构（其实也包括其它object的定义），具体步骤如下：

--建立directory并授权
create directory dump_file as '/oracle/datapump';
grant read,write on directory dump_file to test;

--编辑参数文件
$ vi expdp_test.par
DIRECTORY=DUMP_FILE --datapump所使用的目录
DUMPFILE=expdp_test_ddl.dmp --expdp数据文件
LOGFILE=expdp_test_ddl.log --expdp日志文件
schemas=test --只导出这个表空间的对象
CONTENT=METADATA_ONLY --只导出各类object的定义


--导出
$ expdp test/test PARALLEL=2 parfile=expdp_test.par

--收回权限
revoke read,write on directory dump_file from test;



--复制expdp_test_ddl.dmp文件
scp

--建立directory并授权
create directory dump_file as '/oracle/datapump';
grant read,write on directory dump_file to test;

--导入
$ impdp test/test PARALLEL=2 directory=dump_file dumpfile=expdp_test_ddl.dmp logfile=impdp_test_ddl.log table_exists_action=replace

--收回权限
revoke read,write on directory dump_file from test;

在db_1上进行expdp及db_2上进行impdp操作时，可以通过以下SQL语句查询expdp/impdp的进度
SELECT owner_name owr, job_name jbn, operation ope, job_mode jbm, state, degree, attached_sessions atts, datapump_sessions dats
FROM   dba_datapump_jobs;

在db_2上进行impdp操作时，Oracle是报错了的，不过都是小问题，只有3类错误，一是object已经存在（这是因为我之前用其它方法导入过），二是INDEX_STATISTICS导入出错（统计信息而已，impdp之后再重新收集就OK），三是几个job创建失败（也就几个，手动处理呗）。

导入完成后，比对一下两边db的test用户的schema中的table或object数量是否相同。

SQL> select count(*) from dba_tables where owner='TEST';
  COUNT(*)
----------
       317

SQL> select count(*) from dba_objects where owner='TEST';
  COUNT(*)
----------
     19412

手动处理创建失败的job。

最后重新收集统计信息。


使用expdp(PARALLEL=2)耗时3分钟不到，导出的文件大小48M，impdp(PARALLEL=2)耗时还是比较长的，40分钟，不过相比imp好多了。



OK，现在schema的定义迁移完了，下一步就是迁移数据了。初步计划使用procedure来搞，到时候再说了。