如何基於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
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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-2140554/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 資料庫物件遷移表空間資料庫物件
- 【資料庫】資料庫儲存過程(一)資料庫儲存過程
- Oracle遷移資料庫過程記錄Oracle資料庫
- 資料庫儲存過程資料庫儲存過程
- 資料庫開發---常用物件-儲存過程資料庫物件儲存過程
- 達夢資料庫系統表空間資料檔案遷移過程資料庫
- 基於redhat 6.5 oracle 11.2.0.4測試tts傳輸表空間之資料庫遷移之一RedhatOracleTTS資料庫
- Mysql 資料庫水平分表 儲存過程MySql資料庫儲存過程
- MySql資料庫——儲存過程MySql資料庫儲存過程
- 資料庫-oracle-資料庫遷移資料庫Oracle
- 使用儲存過程(PL/SQL)向資料庫中儲存BLOB物件儲存過程SQL資料庫物件
- 資料庫設計：儲存過程資料庫儲存過程
- 儲存過程呼叫不同資料庫的資料儲存過程資料庫
- 達夢資料庫資料檔案遷移過程資料庫
- ORACLE資料庫遷移Oracle資料庫
- RMAN COPY實現ORACLE資料庫儲存遷移的方案Oracle資料庫
- 自動檢測兩個資料庫之間物件的儲存過程資料庫物件儲存過程
- Oracle資料庫資料遷移流程Oracle資料庫
- MySQL 資料庫儲存 Emoji 表情及特殊符號MySql資料庫符號
- 資料庫許可權-儲存過程資料庫儲存過程
- oracle資料庫停止方法及過程Oracle資料庫
- 同/不同庫遷移資料(在同使用者及表空間)測試
- SQL Server資料庫遠端更新目標表資料的儲存過程SQLServer資料庫儲存過程
- 在資料庫之間移動表空間資料庫
- oracle資料庫損壞的恢復過程-基於IBM伺服器儲存Oracle資料庫IBM伺服器
- 淺談資料庫中的儲存過程資料庫儲存過程
- 資料庫設計：儲存過程主體資料庫儲存過程
- geoserver資料儲存遷移Server
- oracle RAC 更換儲存遷移資料Oracle
- Oracle資料庫（資料泵）遷移方案（上）Oracle資料庫
- Oracle資料庫（資料泵）遷移方案（下）Oracle資料庫
- oracle 遷移資料庫到asmOracle資料庫ASM
- 資料庫遷移資料庫
- 【資料庫】資料庫儲存元素型別基礎資料庫型別
- 只把Oracle的儲存過程或者函式全部遷移到其他的資料庫中Oracle儲存過程函式資料庫
- 使用SQL SERVER儲存過程實現歷史資料遷移SQLServer儲存過程
- 報表資料分庫儲存
- sqlserver資料庫還原儲存過程指令碼SQLServer資料庫儲存過程指令碼