当我们开发完成一个项目/产品，到客户现场进行培训和基础数据的准备时，往往服务器尚在采购之中。这时我们往往会随便找一台配置相对好一点的计算机充当服务器。服务器到货之后，就需要把临时数据库服务器之上的数据迁移到真正的服务器之中。源数据库可能是 Windows 系统、单机、使用文件系统存储数据文件，而目标数据库则可能是 Linux、配置了集群（RAC）、使用ASM存储数据文件。这时就需要进行逻辑数据迁移。
逻辑数据迁移，一般只需要使用 exp 和 imp 这对导出/导入命令即可。但是，某些大型系统，往往有几十个表空间、几十种角色、上千用户（因为它使用了数据库级别的用户管理），每个用户都拥有不同的对象权限，甚至不同用户之间还会相互做外键引用。这时，单纯使用 exp imp 命令无论如何也不能确保一次将所有数据库对象正确导入。本文将以 Step by Step 的方式提供一系列步骤和脚本完成逻辑数据迁移工作，不敢保证考虑了所有情况，仅供各位童鞋参考。
先给出2个脚本：
导出表空间和用户的创建脚本.sql
declare is_asm number(1); -- 是否使用ASM管理。如果为1，会忽略表空间文件名，交由Oracle自动管理 target_datafile_path varchar(2000); -- 目标数据库的数据文件路径。例：D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\ 或 +DATA v_dbuser_names varchar2(32767); -- 接收用户名列表 source_db_conn varchar2(200); -- 源数据库连接 target_db_conn varchar2(200); -- 目标数据库连接 l_file utl_file.file_type; -- 输出文件 begin --DBMS_OUTPUT.ENABLE(10000000); execute immediate 'create or replace directory D_OUTPUT as ''D:\TEMP'''; -- 路径必须是数据库服务器上的硬盘！！！ -- 表空间常量设定 Begin --is_asm := 0; --target_datafile_path := 'E:\oracle_data\zhis\'; is_asm := 1; target_datafile_path := '+DATA'; -- 表空间常量设定 End -- 数据库连接 source_db_conn := '''sys/密码@orcl as sysdba'''; -- 导出时最好用sys用户 target_db_conn := '''sys/密码@zhis as sysdba'''; -- 导入时要用system用户 -- + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + -- + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + -- + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + l_file := utl_file.fopen('D_OUTPUT', '1_creation.sql', 'A'); -- 创建表空间 utl_file.put_line(l_file, '-- 创建表空间'); -- 永久表空间 for c in (select max(tf.tablespace_name) tablespace_name, max(ts.contents) ts_contents, wm_concat('''' || target_datafile_path || case when is_asm=1 then '' else Substr(tf.file_name, Instr(tf.file_name, '\', -1)+1) end || '''' || ' SIZE ' || tf.user_bytes || ' AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED ') datafile_creations from dba_data_files tf left join dba_tablespaces ts on tf.tablespace_name = ts.tablespace_name where ts.tablespace_name not in ('SYSTEM','UNDOTBS1','SYSAUX','TEMP','USERS') -- 排除自带的表空间 and ts.contents ='PERMANENT' group by ts.tablespace_name) loop utl_file.put_line(l_file, 'CREATE TABLESPACE "' || c.tablespace_name || '"'); utl_file.put_line(l_file, ' DATAFILE ' || c.datafile_creations); utl_file.put_line(l_file, ' LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; '); end loop; -- 临时表空间 for c in (select max(tf.tablespace_name) tablespace_name, max(ts.contents) ts_contents, wm_concat('''' || target_datafile_path || case when is_asm=1 then '' else Substr(tf.file_name, Instr(tf.file_name, '\', -1)+1) end || '''' || ' SIZE ' || tf.user_bytes || ' AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED ') datafile_creations from dba_temp_files tf left join dba_tablespaces ts on tf.tablespace_name = ts.tablespace_name where ts.tablespace_name not in ('SYSTEM','UNDOTBS1','SYSAUX','TEMP','USERS') -- 排除自带的表空间 and ts.contents = 'TEMPORARY' group by ts.tablespace_name) loop utl_file.put_line(l_file, 'CREATE TEMPORARY TABLESPACE "' || c.tablespace_name || '"'); utl_file.put_line(l_file, ' TEMPFILE ' || c.datafile_creations); utl_file.put_line(l_file, ' EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M; '); end loop; -- 创建角色 utl_file.put_line(l_file, '-- 创建角色'); for rc in ( select * from dba_roles r where r.role not in ('CONNECT','RESOURCE','DBA','SELECT_CATALOG_ROLE','EXECUTE_CATALOG_ROLE','DELETE_CATALOG_ROLE', 'EXP_FULL_DATABASE','IMP_FULL_DATABASE','RECOVERY_CATALOG_OWNER','GATHER_SYSTEM_STATISTICS', 'LOGSTDBY_ADMINISTRATOR','AQ_ADMINISTRATOR_ROLE','AQ_USER_ROLE','GLOBAL_AQ_USER_ROLE', 'SCHEDULER_ADMIN','HS_ADMIN_ROLE','AUTHENTICATEDUSER','OEM_ADVISOR','OEM_MONITOR', 'WM_ADMIN_ROLE','JAVAUSERPRIV','JAVAIDPRIV','JAVASYSPRIV','JAVADEBUGPRIV','EJBCLIENT', 'JAVA_ADMIN','JAVA_DEPLOY','CTXAPP','XDBADMIN','XDBWEBSERVICES','OLAP_DBA','OLAP_USER','MGMT_USER')) loop utl_file.put_line(l_file, 'create role ' || rc.role || ';'); utl_file.put_line(l_file, 'revoke ' || rc.role || ' from SYS;'); -- 因为创建了角色的用户会自动拥有该角色的权限，而Oracle默认又有启用角色不允许超过148个的限制，所以这里在创建了之后要把角色从当前用户里面移除。 -- 授权 for rrpc in ( select * from role_role_privs rrp where rrp.role = rc.role) loop utl_file.put_line(l_file, 'grant ' || rrpc.granted_role || ' to ' || rc.role || case when rrpc.admin_option = 'YES' then ' with admin option' else '' end || ';'); end loop; for rspc in ( select * from role_sys_privs rsp where rsp.role = rc.role) loop utl_file.put_line(l_file, 'grant ' || rspc.privilege || ' to ' || rc.role || case when rspc.admin_option = 'YES' then ' with admin option' else '' end || ';'); end loop; end loop; -- 创建用户 utl_file.put_line(l_file, '-- 创建用户'); for c in ( select u.* from dba_users u where u.username not in ('ANONYMOUS','CTXSYS','DBSNMP','DIP','DMSYS','EXFSYS','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORDPLUGINS', 'ORDSYS','OUTLN','SCOTT','SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','TSMSYS','WMSYS','XDB')) loop utl_file.put_line(l_file, 'create user ' || c.username); utl_file.put_line(l_file, ' identified by values ''' || c.password || ''''); utl_file.put_line(l_file, ' default tablespace ' || c.default_tablespace); utl_file.put_line(l_file, ' temporary tablespace ' || c.temporary_tablespace || ';'); -- 授权 for spc in ( select sp.* from dba_sys_privs sp where sp.grantee = c.username) loop utl_file.put_line(l_file, 'grant ' || spc.privilege || ' to ' || c.username || ';'); end loop; for rpc in ( select rp.* from dba_role_privs rp where rp.grantee = c.username) loop utl_file.put_line(l_file, 'grant ' || rpc.granted_role || ' to ' || c.username || ';'); end loop; end loop; utl_file.fflush(l_file); utl_file.fclose(l_file); l_file := utl_file.fopen('D_OUTPUT', '3_grand_obj_privs.sql', 'A'); -- 角色的对象授权 utl_file.put_line(l_file, '-- 角色的对象授权'); for rc in ( select * from dba_roles r where r.role not in ('CONNECT','RESOURCE','DBA','SELECT_CATALOG_ROLE','EXECUTE_CATALOG_ROLE','DELETE_CATALOG_ROLE', 'EXP_FULL_DATABASE','IMP_FULL_DATABASE','RECOVERY_CATALOG_OWNER','GATHER_SYSTEM_STATISTICS', 'LOGSTDBY_ADMINISTRATOR','AQ_ADMINISTRATOR_ROLE','AQ_USER_ROLE','GLOBAL_AQ_USER_ROLE', 'SCHEDULER_ADMIN','HS_ADMIN_ROLE','AUTHENTICATEDUSER','OEM_ADVISOR','OEM_MONITOR', 'WM_ADMIN_ROLE','JAVAUSERPRIV','JAVAIDPRIV','JAVASYSPRIV','JAVADEBUGPRIV','EJBCLIENT', 'JAVA_ADMIN','JAVA_DEPLOY','CTXAPP','XDBADMIN','XDBWEBSERVICES','OLAP_DBA','OLAP_USER','MGMT_USER')) loop for rtpc in ( select * from role_tab_privs rtp where rtp.role = rc.role) loop utl_file.put_line(l_file, 'grant ' || rtpc.privilege || ' on ' || rtpc.owner || '.' || rtpc.table_name || ' to ' || rc.role || ';'); end loop; end loop; -- 用户的对象授权 utl_file.put_line(l_file, '-- 用户的对象授权'); for c in ( select u.* from dba_users u where u.username not in ('ANONYMOUS','CTXSYS','DBSNMP','DIP','DMSYS','EXFSYS','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORDPLUGINS', 'ORDSYS','OUTLN','SCOTT','SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','TSMSYS','WMSYS','XDB')) loop for tpc in ( select tp.* from dba_tab_privs tp where tp.grantee = c.username) loop utl_file.put_line(l_file, 'grant ' || tpc.privilege || ' on ' || case when tpc.privilege in ('READ','WRITE') then ' directory ' else '' end || tpc.owner || '.' || tpc.table_name || ' to ' || c.username || case when tpc.grantable = 'YES' then ' with grant option ' else '' end || ';'); end loop; end loop; utl_file.fflush(l_file); utl_file.fclose(l_file); l_file := utl_file.fopen('D_OUTPUT', '2_exp_imp.txt', 'A'); -- 导出和导入命令 select wm_concat('''' || u.username || '''') into v_dbuser_names from dba_users u where u.username not in ('ANONYMOUS','CTXSYS','DBSNMP','DIP','DMSYS','EXFSYS','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORDPLUGINS', 'ORDSYS','OUTLN','SCOTT','SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','TSMSYS','WMSYS','XDB') and u.username in (select distinct o.owner from dba_objects o); -- 拥有对象的用户 utl_file.put_line(l_file, 'exp ' || source_db_conn || ' owner=(' || v_dbuser_names || ') file="D:\sourcedb.dmp" log="D:\sourcedbexp.log" GRANTS=N COMPRESS=N'); /*for uc in ( select u.username from dba_users u where u.username not in ('ANONYMOUS','CTXSYS','DBSNMP','DIP','DMSYS','EXFSYS','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORDPLUGINS', 'ORDSYS','OUTLN','SCOTT','SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','TSMSYS','WMSYS','XDB') and u.username in (select distinct o.owner from dba_objects o)) loop utl_file.put_line(l_file, 'imp ' || target_db_conn || ' file="D:\sourcedb.dmp" log="D:\imp_' || uc.username || '.log" GRANTS=N' || ' fromuser=' || uc.username || ' touser=' || uc.username); end loop;*/ utl_file.put_line(l_file, 'imp ' || target_db_conn || ' full=Y file="D:\sourcedb.dmp" log="D:\sourcedbimp.log" GRANTS=N'); utl_file.fflush(l_file); utl_file.fclose(l_file); end;
创建缺失的对象.sql
declare l_file utl_file.file_type; -- 输出文件 begin execute immediate 'create or replace directory D_OUTPUT as ''D:\TEMP'''; -- 路径必须是数据库服务器上的硬盘！！！ l_file := utl_file.fopen(location => 'D_OUTPUT', filename => '4_create_objs19.sql', open_mode => 'A', max_linesize => 32767); -- 创建外键 utl_file.put_line(l_file, '-- 创建缺失的外键'); for c in ( SELECT DBMS_METADATA.get_ddl('REF_CONSTRAINT', sc.constraint_name, sc.owner) ddl_text FROM dba_constraints sc where sc.constraint_type='R' and sc.owner || sc.constraint_name not in (select tc.owner || tc.constraint_name from dba_constraints@targetdb tc where tc.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','DIP','DMSYS','EXFSYS','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORDPLUGINS', 'ORDSYS','OUTLN','SCOTT','SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','TSMSYS','WMSYS','XDB')) and sc.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','DIP','DMSYS','EXFSYS','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORDPLUGINS', 'ORDSYS','OUTLN','SCOTT','SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','TSMSYS','WMSYS','XDB')) loop utl_file.put_line(l_file, c.ddl_text || ';'); utl_file.fflush(l_file); end loop; -- 创建缺失的视图 utl_file.put_line(l_file, '-- 创建缺失的视图'); for c in (select DBMS_METADATA.get_ddl('VIEW', v.view_name, v.owner) ddl_text from dba_views v where v.owner || v.view_name not in (select tv.owner || tv.view_name from dba_views@targetdb tv where tv.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','DIP','DMSYS','EXFSYS','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORDPLUGINS', 'ORDSYS','OUTLN','SCOTT','SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','TSMSYS','WMSYS','XDB')) and v.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','DIP','DMSYS','EXFSYS','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORDPLUGINS', 'ORDSYS','OUTLN','SCOTT','SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','TSMSYS','WMSYS','XDB')) loop utl_file.put_line(l_file, c.ddl_text || ';'); utl_file.fflush(l_file); end loop; -- 创建缺失的函数和存储过程 utl_file.put_line(l_file, '-- 创建缺失的函数和存储过程'); for c in (select DBMS_METADATA.get_ddl(o.object_type, o.object_name, o.owner) ddl_text from dba_objects o where o.object_type in ('PACKAGE','FUNCTION','PROCEDURE') and o.owner || o.object_name not in (select t.owner || t.object_name from dba_objects@targetdb t where t.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','DIP','DMSYS','EXFSYS','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORDPLUGINS', 'ORDSYS','OUTLN','SCOTT','SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','TSMSYS','WMSYS','XDB')) and o.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','DIP','DMSYS','EXFSYS','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORDPLUGINS', 'ORDSYS','OUTLN','SCOTT','SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','TSMSYS','WMSYS','XDB')) loop utl_file.put_line(l_file, c.ddl_text); utl_file.fflush(l_file); end loop; -- 创建缺失的同义词 utl_file.put_line(l_file, '-- 创建缺失的同义词'); for c in ( select s.*, DBMS_METADATA.get_ddl('SYNONYM', s.synonym_name, s.owner) ddl_text from dba_synonyms s where s.owner || s.synonym_name not in (select ts.owner || ts.synonym_name from dba_synonyms@targetdb ts where ts.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','DIP','DMSYS','EXFSYS','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORDPLUGINS', 'ORDSYS','OUTLN','SCOTT','SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','TSMSYS','WMSYS','XDB')) and s.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','DIP','DMSYS','EXFSYS','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORDPLUGINS', 'ORDSYS','OUTLN','SCOTT','SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','TSMSYS','WMSYS','XDB')) loop utl_file.put_line(l_file, c.ddl_text || ';'); utl_file.fflush(l_file); end loop; utl_file.fflush(l_file); utl_file.fclose(l_file); end;
Step1：以 sys 用户登录源数据库，执行“导出表空间和用户的创建脚本.sql”。
在执行之前，要确认脚本中的几个参数：
D_OUTPUT 参数是服务器硬盘上路径，默认为d:\temp，确保文件夹已创建。
source_db_conn 和 target_db_conn 为源数据库和目标数据库的用户名、密码和服务命名，要使用 sys 用户。
is_asm 表示目标数据库是否使用ASM管理数据文件。
target_datafile_path 是目标数据库数据文件的路径。
执行此脚本会在数据库所在服务器硬盘 d:\temp 创建3个文件：
1_creation.sql 创建表空间和用户的数据库脚本。
2_exp_imp.txt 导出和导入命令。
3_grand_obj_privs.sql 给对象赋予权限的数据库脚本。
Step2：以 sys 用户登录目标数据库，执行 “1_creation.sql”，创建表空间和用户。
Step3：打开命令行，执行exp_imp.txt里的导出命令。
Step4：打开命令行，执行exp_imp.txt里的导入命令。注：在Windows下，如果想要以sys用户导入导出，需要加引号，例如 imp 'sys/Zhf-1980@testdb as sysdba' ...；而在 linux 系统下，则要写成 imp \'sys/Zhf-1980@testdb as sysdba\' ...
Step5：打开 Step4 生成的 d:\sourcedbimp.log，搜索IMP开头的信息，确认有哪些对象导入失败。
Step6：以sys登录源数据库，创建指向目标数据库的数据链接，然后执行“创建缺失的对象.sql”。此脚本将生成“4_create_objs.sql”。
Step7：以sys登录目标数据库，执行4_create_objs.sql，可以将 Step5 导入失败的外键、视图、存储过程和同义词等再创建一遍。
Step8：使用PL/SQL Developer，以sys登录目标数据库，使用菜单 Tools|Compile Invalid Objects...，编译所有无效对象。
