如何基于oracle 11.2.0.4数据库版本在数据库间迁移非表特殊对象类比如序列及存储过程

测试结论
1,对于表空间及数据库用户存储特殊对象，即非表的对象类型如：序列及触发器和存储过程


2，在数据库间迁移上述特殊对象类型的expdp以及impdp的语句如下：




---源端数据库
expdp /'sys/system as sysdba/' directory=DATA_PUMP_DIR dumpfile=exp_metadata.dmp CONTENT=metadata_only   schemas=user_obj  include=sequence,procedure




---传输源端数据库的DUMPFILE至目标端数据库
scp /oracle/admin/mygirl/dpdump/exp_metadata.dmp oracle@10.0.0.39:/oracle/admin/esbdb/dpdump/


---目标端数据库
impdp /'sys/system as sysdba/' directory=DATA_PUMP_DIR dumpfile=exp_metadata.dmp CONTENT=metadata_only   schemas=user_obj  include=sequence,procedure








测试明细
1，源端数据库版本




SQL> select * from v$version where rownum=1;


BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production


2，源端数据库用户
SQL> select name from v$tablespace;


NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
TBS_FATHER
TBS_MOTHER
TBS_SUN
TBS_ZXY


9 rows selected.


SQL> select name from v$datafile;


NAME
--------------------------------------------------------------------------------
/oracle/mygirl/system01.dbf
/oracle/mygirl/sysaux01.dbf
/oracle/mygirl/undotbs01.dbf
/oracle/mygirl/users01.dbf
/oracle/admin/mygirl/dpdump/tbs_father01.dbf
/oracle/admin/mygirl/dpdump/tbs_mother01.dbf
/oracle/admin/mygirl/dpdump/tbs_sun01.dbf
/oracle/mygirl/tbs_zxy01.dbf


8 rows selected.






SQL> create tablespace tbs_obj datafile '/oracle/mygirl/tbs_obj01.dbf' size 10m;


Tablespace created.


SQL> create user user_obj identified by system default tablespace tbs_obj account unlock;


User created.


SQL> grant resource,connect,dba to user_obj;


Grant succeeded.


3，创建源端数据库用户的序列及存储过程
SQL> conn user_obj/system
Connected.
SQL> create sequence sequence_zxy;


Sequence created.


SQL> create procedure proc_nb
  2  as
  3  begin 
  4  dbms_output.put_line(1);
  5  end;
  6  /


Procedure created.




SQL> select object_type,object_name,status from user_objects


OBJECT_TYPE         OBJECT_NAME                    STATUS
------------------- ------------------------------ -------
PROCEDURE           PROC_NB                        VALID
SEQUENCE            SEQUENCE_ZXY                   VALID




4,导出源数据库数据库用户的序列及存储过程
SQL> set linesize 300
SQL> col directory_path for a50
SQL> r
  1* select directory_name,directory_path from dba_directories


DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ --------------------------------------------------
XMLDIR                         /oracle/product/11.2.0/db_1/rdbms/xml
ORACLE_OCM_CONFIG_DIR          /oracle/product/11.2.0/db_1/ccr/hosts/mygirl/state
DATA_PUMP_DIR                  /oracle/admin/mygirl/dpdump/
ORACLE_OCM_CONFIG_DIR2         /oracle/product/11.2.0/db_1/ccr/state




[oracle@mygirl ~]$ expdp /'sys/system as sysdba/' directory=DATA_PUMP_DIR dumpfile=exp_metadata.dmp CONTENT=metadata_only   schemas=user_obj  include=sequence,procedure


Export: Release 11.2.0.4.0 - Production on Fri Jun 9 23:16:35 2017


Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  "sys/******** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=exp_metadata.dmp CONTENT=metadata_only schemas=user_obj include=sequence,procedure 
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /oracle/admin/mygirl/dpdump/exp_metadata.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Jun 9 23:16:41 2017 elapsed 0 00:00:04


[oracle@mygirl ~]$ 




5，目标数据库创建数据库用户
SQL> select * from v$version where rownum=1;


BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production




SQL> select name from v$datafile;


NAME
--------------------------------------------------------------------------------
/oracle/esbdb/system01.dbf
/oracle/esbdb/sysaux01.dbf
/oracle/esbdb/undotbs01.dbf
/oracle/esbdb/users01.dbf


SQL> create tablespace tbs_obj datafile '/oracle/esbdb/tbs_obj01.dbf' size 10m;


Tablespace created.




SQL> create user user_obj identified by system default tablespace tbs_obj account unlock;


User created.


SQL> grant resource,connect,dba to user_obj;


Grant succeeded.




6,复制源端数据库DUMP文件到目标端数据库对应目录
SQL> set linesize 300
SQL> col directory_path for a50
SQL> select directory_name,directory_path from dba_directories;


DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ --------------------------------------------------
XMLDIR                         /oracle/product/11.2.0/db_1/rdbms/xml
ORACLE_OCM_CONFIG_DIR          /oracle/product/11.2.0/db_1/ccr/hosts/suse11/state
DATA_PUMP_DIR                  /oracle/admin/esbdb/dpdump/
ORACLE_OCM_CONFIG_DIR2         /oracle/product/11.2.0/db_1/ccr/state


SQL> 




[oracle@mygirl ~]$ scp /oracle/admin/mygirl/dpdump/exp_metadata.dmp oracle@10.0.0.39:/oracle/admin/esbdb/dpdump/
Password: 
exp_metadata.dmp


SQL> host ls -l /oracle/admin/esbdb/dpdump/exp_metadata.dmp
-rw-r----- 1 oracle oinstall 159744 2017-06-09 23:25 /oracle/admin/esbdb/dpdump/exp_metadata.dmp




7,在目标端数据库导入源端导出的元数据DMP FILE


oracle@suse11:~> impdp /'sys/system as sysdba/' directory=DATA_PUMP_DIR dumpfile=exp_metadata.dmp CONTENT=metadata_only   schemas=user_obj  include=sequence,procedure


Import: Release 11.2.0.4.0 - Production on Fri Jun 9 23:27:41 2017


Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_SCHEMA_01":  "sys/******** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=exp_metadata.dmp CONTENT=metadata_only schemas=user_obj include=sequence,procedure 
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Job "SYS"."SYS_IMPORT_SCHEMA_01" successfully completed at Fri Jun 9 23:27:49 2017 elapsed 0 00:00:05


oracle@suse11:~> 




8,在目标端数据库验证数据导入的一致性及完整性
SQL> set linesize 300
SQL> col object_name for a50
SQL> r
  1* select object_type,object_name,status from user_objects


OBJECT_TYPE         OBJECT_NAME                                        STATUS
------------------- -------------------------------------------------- -------
PROCEDURE           PROC_NB                                            VALID
SEQUENCE            SEQUENCE_ZXY                                       VALID