【impdp方式简单复制一个schema到另一个schema到】

最新推荐文章于 2022-04-19 15:33:01 发布

comk08263401

最新推荐文章于 2022-04-19 15:33:01 发布

阅读量663

点赞数

文章标签：数据库网络操作系统

本次实验用到impdp的NETWORK_LINK参数

1、实验环境

1）同一台linux系统，oracle10g

2）源ccod，需要被复制的schema为ccod，所属表空间为ccod

目的gc，复制过来后的目的schema为tt，所属表空间为ocm

3）本地监听配置文件tnsnames.ora

[oracle@databak admin]$ cat tnsnames.ora

CCOD=

(DESCRIPTION=

(ADDRESS=(PROTOCOL=TCP)(HOST=10.130.41.180)(PORT=1521))

(CONNECT_DATA=

(SERVER=DEDICATED)

(SERVICE_NAME=ccod)

)

GC=

(DESCRIPTION=

(ADDRESS=(PROTOCOL=TCP)(HOST=10.130.41.180)(PORT=1521))

(CONNECT_DATA=

(SERVER=DEDICATED)

(SERVICE_NAME=gc)

)

2、实验目的

将ccod库下的ccod用户下对象，导入gc库中的tt用户

3、操作步骤

1）在gc库中建立到ccod库的dblink.

连到gc库执行

SQL> create public database link dblink connect to ccod identified by ccod using 'CCOD';

Database link created.

SQL> set pagesize 1000

SQL> select * from tab@dblink;

TNAME TABTYPE CLUSTERID

------------------------------ ------- ----------

SUBO TABLE

CCOD_SYS_MENU TABLE

CCOD_SYS_MENU_ROLE TABLE

CCOD_SYS_ROLE TABLE

CCOD_SYS_USER TABLE

CCOD_SYS_USER_ROLE TABLE

CFG_ALTER_INFO TABLE

CFG_NODE TABLE

CFG_SMS_LOG TABLE

DCMS_DOMAIN_MANAGER TABLE

DCMS_LOGIN_USER TABLE

DCMS_MANAGEMENT TABLE

DCMS_MENU TABLE

DCMS_USER_MENU TABLE

EC_CLUSTER_AREA TABLE

EC_CLUSTER_INFO TABLE

EC_CLUSTER_RELA TABLE

EC_EXCEL_INFO TABLE

EC_EXCEL_RELA TABLE

EC_RI_CONFIG TABLE

EC_VGPROXY_INFO TABLE

EC_VGPROXY_TAC TABLE

EC_VGPT_RELA TABLE

GLS_ACCOUNT_NUMBER TABLE

GLS_C_E_RELATION TABLE

GLS_DATA_CLEAN TABLE

GLS_DB_AGENT_RELATE TABLE

GLS_DB_ENT_RELATE TABLE

GLS_DB_SG_RELATE TABLE

GLS_DB_SOURCE TABLE

GLS_ENTERPRISE_INFO TABLE

GLS_ILDTLICENSE TABLE

GLS_INT_COUNTRY_PHONE TABLE

GLS_INT_PROVINCE_PHONE TABLE

GLS_LOG_FILTER_COLUMN TABLE

GLS_LOG_FILTER_OBJ TABLE

GLS_MONITOR_CONFIG TABLE

GLS_OPERATION_LOG TABLE

GLS_OPERATION_LOG_DETAIL TABLE

GLS_PARAMETER TABLE

GLS_RESOURCE_NUM TABLE

GLS_SERVICE_ROUTE TABLE

GLS_SERVICE_UNIT TABLE

GLS_SS_CONFIG TABLE

GLS_VIRTUAL_ENT TABLE

GLS_VIRTUAL_ENT_RELATION TABLE

OCX_UPDATE_LOG TABLE

PROXY_AGENT_INFO TABLE

48 rows selected.

以上说明dblink已ok。

2）执行impdp命令，进行复制

在这种情况下执行impdp命令，目标用户tt可以不存在而由impdp自动创建，并授予与源schema相同的权限。

不过对于impdp导入过程中创建的schema，需要在导入完成后，手工修改密码。

[oracle@databak ~]$ export ORACLE_SID=gc

[oracle@databak ~]$ impdp system/oracle network_link=dblink SCHEMAS=ccod REMAP_SCHEMA=ccod:tt REMAP_TABLESPACE=ccod:ocm

Import: Release 10.2.0.1.0 - Production on Wednesday, 28 November, 2012 16:37:36

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** network_link=dblink SCHEMAS=ccod REMAP_SCHEMA=ccod:tt REMAP_TABLESPACE=ccod:ocm

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 1.625 MB

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE

. . imported "TT"."CCOD_SYS_MENU" 38 rows

. . imported "TT"."CCOD_SYS_ROLE" 2 rows

. . imported "TT"."CCOD_SYS_USER" 1 rows

. . imported "TT"."CFG_NODE" 2 rows

. . imported "TT"."DCMS_DOMAIN_MANAGER" 1 rows

. . imported "TT"."DCMS_LOGIN_USER" 3 rows

. . imported "TT"."DCMS_MANAGEMENT" 1 rows

. . imported "TT"."DCMS_MENU" 82 rows

. . imported "TT"."DCMS_USER_MENU" 189 rows

. . imported "TT"."GLS_ACCOUNT_NUMBER" 1 rows

. . imported "TT"."GLS_DB_AGENT_RELATE" 202 rows

. . imported "TT"."GLS_DB_ENT_RELATE" 1 rows

. . imported "TT"."GLS_DB_SG_RELATE" 4 rows

. . imported "TT"."GLS_DB_SOURCE" 1 rows

. . imported "TT"."GLS_ENTERPRISE_INFO" 1 rows

. . imported "TT"."GLS_LOG_FILTER_COLUMN" 4 rows

. . imported "TT"."GLS_LOG_FILTER_OBJ" 1 rows

. . imported "TT"."GLS_MONITOR_CONFIG" 1 rows

. . imported "TT"."GLS_OPERATION_LOG" 92 rows

. . imported "TT"."GLS_OPERATION_LOG_DETAIL" 12 rows

. . imported "TT"."GLS_PARAMETER" 3 rows

. . imported "TT"."GLS_RESOURCE_NUM" 2 rows

. . imported "TT"."GLS_SERVICE_ROUTE" 2 rows

. . imported "TT"."GLS_SERVICE_UNIT" 8 rows

. . imported "TT"."GLS_SS_CONFIG" 1 rows

. . imported "TT"."SUBO" 10 rows

. . imported "TT"."CCOD_SYS_MENU_ROLE" 0 rows

. . imported "TT"."CCOD_SYS_USER_ROLE" 0 rows

. . imported "TT"."CFG_ALTER_INFO" 0 rows

. . imported "TT"."CFG_SMS_LOG" 0 rows

. . imported "TT"."EC_CLUSTER_AREA" 0 rows

. . imported "TT"."EC_CLUSTER_INFO" 0 rows

. . imported "TT"."EC_CLUSTER_RELA" 0 rows

. . imported "TT"."EC_EXCEL_INFO" 0 rows

. . imported "TT"."EC_EXCEL_RELA" 0 rows

. . imported "TT"."EC_RI_CONFIG" 0 rows

. . imported "TT"."EC_VGPROXY_INFO" 0 rows

. . imported "TT"."EC_VGPROXY_TAC" 0 rows

. . imported "TT"."EC_VGPT_RELA" 0 rows

. . imported "TT"."GLS_C_E_RELATION" 0 rows

. . imported "TT"."GLS_DATA_CLEAN" 0 rows

. . imported "TT"."GLS_ILDTLICENSE" 0 rows

. . imported "TT"."GLS_INT_COUNTRY_PHONE" 0 rows

. . imported "TT"."GLS_INT_PROVINCE_PHONE" 0 rows

. . imported "TT"."GLS_VIRTUAL_ENT" 0 rows

. . imported "TT"."GLS_VIRTUAL_ENT_RELATION" 0 rows

. . imported "TT"."OCX_UPDATE_LOG" 0 rows

. . imported "TT"."PROXY_AGENT_INFO" 0 rows

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/TYPE/TYPE_BODY

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at 16:37:54

[oracle@databak ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Nov 28 16:42:56 2012

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production