ITPUB论坛 » Oracle新技术/11g » 使用impdp复制oracle的schema
2007-2-28 18:01
ZALBB
使用impdp复制oracle的schema
环境 windwos 20003 oracle10202
数据库中的用户数
[php]
SQL> select * from all_users order by 1;
USERNAME USER_ID CREATED
------------------------------ ---------- ------------
ANONYMOUS 36 19-APR-06
DBSNMP 24 19-APR-06
DEP 49 10-MAY-06
DIP 19 19-APR-06
EXFSYS 34 19-APR-06
INFO 60 09-AUG-06
MARKETING 48 20-APR-06
MDSYS 43 19-APR-06
MGMT_VIEW 46 19-APR-06
NCTS10G 51 23-JUN-06
ORDPLUGINS 41 19-APR-06
USERNAME USER_ID CREATED
------------------------------ ---------- ------------
ORDSYS 40 19-APR-06
OUTLN 11 19-APR-06
SCOTT 50 08-JUN-06
SI_INFORMTN_SCHEMA 42 19-APR-06
SYS 0 19-APR-06
SYSMAN 44 19-APR-06
SYSTEM 5 19-APR-06
TSMSYS 21 19-APR-06
WMSYS 25 19-APR-06
XDB 35 19-APR-06
21 rows selected.
1、一开始没使用network_link, impdp自动查找在dump_data_dir 目录下的expdat.dmp 文件
Microsoft Windows [版本 5.2.3790]
(C) 版权所有 1985-2003 Microsoft Corp.
d:\oracle\product\10.2.0\db_2\bin>impdp '/ as sysdba' logfile=duplicate_scott.log remap_schema=sco
tt:scott2
Import: Release 10.2.0.2.0 - Production on Wednesday, 28 February, 2007 17:23:05
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "E:\temp\expdat.dmp" for read
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) 系统找不到指定的文件。
2、创建要使用的数据库链
SQL> create public database link to71 connect to system identified by "1" using '71';
Database link created.
3、之后没有说明要复制的schema，结果系统似乎把system要处理的schema。错误连连。
d:\oracle\product\10.2.0\db_2\bin>impdp '/ as sysdba' logfile=duplicate_scott.log remap_schema=sco
tt:scott2 network_link=to71
Import: Release 10.2.0.2.0 - Production on Wednesday, 28 February, 2007 17:30:04
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYS"."SYS_IMPORT_SCHEMA_01": '/******** AS SYSDBA' logfile=duplicate_scott.log remap_sc
hema=scott:scott2 network_link=to71
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 320 KB
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/SYNONYM/SYNONYM
ORA-31684: Object type SYNONYM:"SYSTEM"."CATALOG" already exists
ORA-31684: Object type SYNONYM:"SYSTEM"."COL" already exists
ORA-31684: Object type SYNONYM:"SYSTEM"."PRODUCT_USER_PROFILE" already exists
ORA-31684: Object type SYNONYM:"SYSTEM"."PUBLICSYN" already exists
ORA-31684: Object type SYNONYM:"SYSTEM"."SYSCATALOG" already exists
ORA-31684: Object type SYNONYM:"SYSTEM"."SYSFILES" already exists
ORA-31684: Object type SYNONYM:"SYSTEM"."TAB" already exists
ORA-31684: Object type SYNONYM:"SYSTEM"."TABQUOTAS" already exists
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
ORA-31684: Object type TYPE:"SYSTEM"."REPCAT$_OBJECT_NULL_VECTOR" already exists
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
ORA-31684: Object type SEQUENCE:"SYSTEM"."MVIEW$_ADVSEQ_GENERIC" already exists
ORA-31684: Object type SEQUENCE:"SYSTEM"."MVIEW$_ADVSEQ_ID" already exists
ORA-31684: Object type SEQUENCE:"SYSTEM"."REPCAT$_EXCEPTIONS_S" already exists
ORA-31684: Object type SEQUENCE:"SYSTEM"."REPCAT$_FLAVORS_S" already exists
ORA-31684: Object type SEQUENCE:"SYSTEM"."REPCAT$_FLAVOR_NAME_S" already exists
ORA-31684: Object type SEQUENCE:"SYSTEM"."REPCAT$_REFRESH_TEMPLATES_S" already exists
ORA-31684: Object type SEQUENCE:"SYSTEM"."REPCAT$_REPPROP_KEY" already exists
ORA-31684: Object type SEQUENCE:"SYSTEM"."REPCAT$_RUNTIME_PARMS_S" already exists
ORA-31684: Object type SEQUENCE:"SYSTEM"."REPCAT$_TEMPLATE_OBJECTS_S" already exists
ORA-31684: Object type SEQUENCE:"SYSTEM"."REPCAT$_TEMPLATE_PARMS_S" already exists
ORA-31684: Object type SEQUENCE:"SYSTEM"."REPCAT$_TEMPLATE_REFGROUPS_S" already exists
ORA-31684: Object type SEQUENCE:"SYSTEM"."REPCAT$_TEMPLATE_SITES_S" already exists
ORA-31684: Object type SEQUENCE:"SYSTEM"."REPCAT$_TEMP_OUTPUT_S" already exists
ORA-31684: Object type SEQUENCE:"SYSTEM"."REPCAT$_USER_AUTHORIZATIONS_S" already exists
ORA-31684: Object type SEQUENCE:"SYSTEM"."REPCAT$_USER_PARM_VALUES_S" already exists
ORA-31684: Object type SEQUENCE:"SYSTEM"."REPCAT_LOG_SEQUENCE" already exists
ORA-31684: Object type SEQUENCE:"SYSTEM"."TEMPLATE$_TARGETS_S" already exists
Processing object type SCHEMA_EXPORT/TABLE/TABLE
^C
4、再次复制，这次成功。
d:\oracle\product\10.2.0\db_2\bin>impdp '/ as sysdba' logfile=duplicate_scott.log schemas=scott re
map_schema=scott:scott2 network_link=to71
Import: Release 10.2.0.2.0 - Production on Wednesday, 28 February, 2007 17:33:09
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYS"."SYS_IMPORT_SCHEMA_01": '/******** AS SYSDBA' logfile=duplicate_scott.log schemas=
scott remap_schema=scott:scott2 network_link=to71
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 320 KB
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/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported "SCOTT2"."TAB1" 20000 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
Job "SYS"."SYS_IMPORT_SCHEMA_01" successfully completed at 17:33:39
5、查看用户数。
SQL> select * from all_users order by 1;
USERNAME USER_ID CREATED
------------------------------ ---------- ------------
ANONYMOUS 36 19-APR-06
DBSNMP 24 19-APR-06
DEP 49 10-MAY-06
DIP 19 19-APR-06
EXFSYS 34 19-APR-06
INFO 60 09-AUG-06
MARKETING 48 20-APR-06
MDSYS 43 19-APR-06
MGMT_VIEW 46 19-APR-06
NCTS10G 51 23-JUN-06
ORDPLUGINS 41 19-APR-06
USERNAME USER_ID CREATED
------------------------------ ---------- ------------
ORDSYS 40 19-APR-06
OUTLN 11 19-APR-06
SCOTT 50 08-JUN-06
SCOTT2 61 28-FEB-07
SI_INFORMTN_SCHEMA 42 19-APR-06
SYS 0 19-APR-06
SYSMAN 44 19-APR-06
SYSTEM 5 19-APR-06
TSMSYS 21 19-APR-06
WMSYS 25 19-APR-06
XDB 35 19-APR-06
22 rows selected.
6、检查两用户里的对象，一模一样，说明复制成功。
SQL> connect / as sysdba
Connected.
SQL> alter user scott2 identified by "1";
User altered.
SQL> connect scott/1
Connected.
SQL> select * from user_objects;
OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ------------------------------ ---------- -------------- ------------
TAB1 52198 52198 TABLE
TAB1_ID2 52199 52199 INDEX
PRO_FAILURES 54830 PROCEDURE
SCHEDULE_MONTH_BY_1950 54831 SCHEDULE
P_PRO_FAILURES 54832 PROGRAM
J_PRO_FAILURES 54833 JOB
6 rows selected.
SQL> connect scott2/1
Connected.
SQL> select * from user_objects;
OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ------------------------------ ---------- -------------- ------------
TAB1 75271 75271 TABLE
TAB1_ID2 75272 75272 INDEX
PRO_FAILURES 75273 PROCEDURE
P_PRO_FAILURES 75274 PROGRAM
SCHEDULE_MONTH_BY_1950 75275 SCHEDULE
J_PRO_FAILURES 75276 JOB
6 rows selected.
[/php]
7、我估计当network_link指向另外数据库的时，也可以把schema参数里指定的用户复制
到network_link所指的数据库，若两者用的表空间不一样，则可使用remap_tablespace
一一对应起来即可。当然前提是network_link的数据库链所使用的用户，权限应该足够大。
否则，无法在目标数据库中提供所需要的权限。
环境 windwos 20003 oracle10202
数据库中的用户数
[php]
SQL> select * from all_users order by 1;
USERNAME USER_ID CREATED
------------------------------ ---------- ------------
ANONYMOUS 36 19-APR-06
DBSNMP 24 19-APR-06
DEP 49 10-MAY-06
DIP 19 19-APR-06
EXFSYS 34 19-APR-06
INFO 60 09-AUG-06
MARKETING 48 20-APR-06
MDSYS 43 19-APR-06
MGMT_VIEW 46 19-APR-06
NCTS10G 51 23-JUN-06
ORDPLUGINS 41 19-APR-06
USERNAME USER_ID CREATED
------------------------------ ---------- ------------
ORDSYS 40 19-APR-06
OUTLN 11 19-APR-06
SCOTT 50 08-JUN-06
SI_INFORMTN_SCHEMA 42 19-APR-06
SYS 0 19-APR-06
SYSMAN 44 19-APR-06
SYSTEM 5 19-APR-06
TSMSYS 21 19-APR-06
WMSYS 25 19-APR-06
XDB 35 19-APR-06
21 rows selected.
1、一开始没使用network_link, impdp自动查找在dump_data_dir 目录下的expdat.dmp 文件
Microsoft Windows [版本 5.2.3790]
(C) 版权所有 1985-2003 Microsoft Corp.
d:\oracle\product\10.2.0\db_2\bin>impdp '/ as sysdba' logfile=duplicate_scott.log remap_schema=sco
tt:scott2
Import: Release 10.2.0.2.0 - Production on Wednesday, 28 February, 2007 17:23:05
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "E:\temp\expdat.dmp" for read
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) 系统找不到指定的文件。
2、创建要使用的数据库链
SQL> create public database link to71 connect to system identified by "1" using '71';
Database link created.
3、之后没有说明要复制的schema，结果系统似乎把system要处理的schema。错误连连。
d:\oracle\product\10.2.0\db_2\bin>impdp '/ as sysdba' logfile=duplicate_scott.log remap_schema=sco
tt:scott2 network_link=to71
Import: Release 10.2.0.2.0 - Production on Wednesday, 28 February, 2007 17:30:04
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYS"."SYS_IMPORT_SCHEMA_01": '/******** AS SYSDBA' logfile=duplicate_scott.log remap_sc
hema=scott:scott2 network_link=to71
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 320 KB
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/SYNONYM/SYNONYM
ORA-31684: Object type SYNONYM:"SYSTEM"."CATALOG" already exists
ORA-31684: Object type SYNONYM:"SYSTEM"."COL" already exists
ORA-31684: Object type SYNONYM:"SYSTEM"."PRODUCT_USER_PROFILE" already exists
ORA-31684: Object type SYNONYM:"SYSTEM"."PUBLICSYN" already exists
ORA-31684: Object type SYNONYM:"SYSTEM"."SYSCATALOG" already exists
ORA-31684: Object type SYNONYM:"SYSTEM"."SYSFILES" already exists
ORA-31684: Object type SYNONYM:"SYSTEM"."TAB" already exists
ORA-31684: Object type SYNONYM:"SYSTEM"."TABQUOTAS" already exists
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
ORA-31684: Object type TYPE:"SYSTEM"."REPCAT$_OBJECT_NULL_VECTOR" already exists
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
ORA-31684: Object type SEQUENCE:"SYSTEM"."MVIEW$_ADVSEQ_GENERIC" already exists
ORA-31684: Object type SEQUENCE:"SYSTEM"."MVIEW$_ADVSEQ_ID" already exists
ORA-31684: Object type SEQUENCE:"SYSTEM"."REPCAT$_EXCEPTIONS_S" already exists
ORA-31684: Object type SEQUENCE:"SYSTEM"."REPCAT$_FLAVORS_S" already exists
ORA-31684: Object type SEQUENCE:"SYSTEM"."REPCAT$_FLAVOR_NAME_S" already exists
ORA-31684: Object type SEQUENCE:"SYSTEM"."REPCAT$_REFRESH_TEMPLATES_S" already exists
ORA-31684: Object type SEQUENCE:"SYSTEM"."REPCAT$_REPPROP_KEY" already exists
ORA-31684: Object type SEQUENCE:"SYSTEM"."REPCAT$_RUNTIME_PARMS_S" already exists
ORA-31684: Object type SEQUENCE:"SYSTEM"."REPCAT$_TEMPLATE_OBJECTS_S" already exists
ORA-31684: Object type SEQUENCE:"SYSTEM"."REPCAT$_TEMPLATE_PARMS_S" already exists
ORA-31684: Object type SEQUENCE:"SYSTEM"."REPCAT$_TEMPLATE_REFGROUPS_S" already exists
ORA-31684: Object type SEQUENCE:"SYSTEM"."REPCAT$_TEMPLATE_SITES_S" already exists
ORA-31684: Object type SEQUENCE:"SYSTEM"."REPCAT$_TEMP_OUTPUT_S" already exists
ORA-31684: Object type SEQUENCE:"SYSTEM"."REPCAT$_USER_AUTHORIZATIONS_S" already exists
ORA-31684: Object type SEQUENCE:"SYSTEM"."REPCAT$_USER_PARM_VALUES_S" already exists
ORA-31684: Object type SEQUENCE:"SYSTEM"."REPCAT_LOG_SEQUENCE" already exists
ORA-31684: Object type SEQUENCE:"SYSTEM"."TEMPLATE$_TARGETS_S" already exists
Processing object type SCHEMA_EXPORT/TABLE/TABLE
^C
4、再次复制，这次成功。
d:\oracle\product\10.2.0\db_2\bin>impdp '/ as sysdba' logfile=duplicate_scott.log schemas=scott re
map_schema=scott:scott2 network_link=to71
Import: Release 10.2.0.2.0 - Production on Wednesday, 28 February, 2007 17:33:09
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYS"."SYS_IMPORT_SCHEMA_01": '/******** AS SYSDBA' logfile=duplicate_scott.log schemas=
scott remap_schema=scott:scott2 network_link=to71
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 320 KB
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/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported "SCOTT2"."TAB1" 20000 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
Job "SYS"."SYS_IMPORT_SCHEMA_01" successfully completed at 17:33:39
5、查看用户数。
SQL> select * from all_users order by 1;
USERNAME USER_ID CREATED
------------------------------ ---------- ------------
ANONYMOUS 36 19-APR-06
DBSNMP 24 19-APR-06
DEP 49 10-MAY-06
DIP 19 19-APR-06
EXFSYS 34 19-APR-06
INFO 60 09-AUG-06
MARKETING 48 20-APR-06
MDSYS 43 19-APR-06
MGMT_VIEW 46 19-APR-06
NCTS10G 51 23-JUN-06
ORDPLUGINS 41 19-APR-06
USERNAME USER_ID CREATED
------------------------------ ---------- ------------
ORDSYS 40 19-APR-06
OUTLN 11 19-APR-06
SCOTT 50 08-JUN-06
SCOTT2 61 28-FEB-07
SI_INFORMTN_SCHEMA 42 19-APR-06
SYS 0 19-APR-06
SYSMAN 44 19-APR-06
SYSTEM 5 19-APR-06
TSMSYS 21 19-APR-06
WMSYS 25 19-APR-06
XDB 35 19-APR-06
22 rows selected.
6、检查两用户里的对象，一模一样，说明复制成功。
SQL> connect / as sysdba
Connected.
SQL> alter user scott2 identified by "1";
User altered.
SQL> connect scott/1
Connected.
SQL> select * from user_objects;
OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ------------------------------ ---------- -------------- ------------
TAB1 52198 52198 TABLE
TAB1_ID2 52199 52199 INDEX
PRO_FAILURES 54830 PROCEDURE
SCHEDULE_MONTH_BY_1950 54831 SCHEDULE
P_PRO_FAILURES 54832 PROGRAM
J_PRO_FAILURES 54833 JOB
6 rows selected.
SQL> connect scott2/1
Connected.
SQL> select * from user_objects;
OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ------------------------------ ---------- -------------- ------------
TAB1 75271 75271 TABLE
TAB1_ID2 75272 75272 INDEX
PRO_FAILURES 75273 PROCEDURE
P_PRO_FAILURES 75274 PROGRAM
SCHEDULE_MONTH_BY_1950 75275 SCHEDULE
J_PRO_FAILURES 75276 JOB
6 rows selected.
[/php]
7、我估计当network_link指向另外数据库的时，也可以把schema参数里指定的用户复制
到network_link所指的数据库，若两者用的表空间不一样，则可使用remap_tablespace
一一对应起来即可。当然前提是network_link的数据库链所使用的用户，权限应该足够大。
否则，无法在目标数据库中提供所需要的权限。
2007-2-28 18:08
ZALBB
今后数据迁移又多了个方法。
2007-3-1 15:04
ZALBB
7、我估计当network_link指向另外数据库的时，也可以把schema参数里指定的用户复制
到network_link所指的数据库，若两者用的表空间不一样，则可使用remap_tablespace
一一对应起来即可。当然前提是network_link的数据库链所使用的用户，权限应该足够大。
否则，无法在目标数据库中提供所需要的权限。
**********************************************************************************************************************
猜反了，应该是把network_link所指的数据库中schema用户导入到本地数据库来。如：
F:\oracle\product\10.2.0\db_1\BIN>impdp logfile=dup_scott.log network_link=to0271 schemas=scott re
map_schema=scott:scott2 remap_tablespace=users:users,temp:temp
Import: Release 10.2.0.3.0 - Production on 星期四, 01 3月, 2007 14:49:46
Copyright (c) 2003, 2005, Oracle. All rights reserved.
用户名: / as sysdba
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
启动 "SYS"."SYS_IMPORT_SCHEMA_03": /******** AS SYSDBA logfile=dup_scott.log network_link=to0271
schemas=scott remap_schema=scott:scott2 remap_tablespace=users:users,temp:temp
正在使用 BLOCKS 方法进行估计...
处理对象类型 SCHEMA_EXPORT/TABLE/TABLE_DATA
使用 BLOCKS 方法的总估计: 320 KB
处理对象类型 SCHEMA_EXPORT/USER
处理对象类型 SCHEMA_EXPORT/SYSTEM_GRANT
处理对象类型 SCHEMA_EXPORT/ROLE_GRANT
处理对象类型 SCHEMA_EXPORT/DEFAULT_ROLE
处理对象类型 SCHEMA_EXPORT/TABLESPACE_QUOTA
处理对象类型 SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
处理对象类型 SCHEMA_EXPORT/TABLE/TABLE
. . 导入了 "SCOTT2"."TAB1" 20000 行
. . 导入了 "SCOTT2"."TAB2" 0 行
处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/INDEX
处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
处理对象类型 SCHEMA_EXPORT/PROCEDURE/PROCEDURE
处理对象类型 SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
处理对象类型 SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
处理对象类型 SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
作业 "SYS"."SYS_IMPORT_SCHEMA_03" 已于 14:50:10 成功完成
这里的SCOTT用户实际是network_link所指的数据库的用户，scott2 建在本地数据库。
我上面理解成：把本地的 scott 的对象导入到network_link所指的数据库新建的scott2 中。
到network_link所指的数据库，若两者用的表空间不一样，则可使用remap_tablespace
一一对应起来即可。当然前提是network_link的数据库链所使用的用户，权限应该足够大。
否则，无法在目标数据库中提供所需要的权限。
**********************************************************************************************************************
猜反了，应该是把network_link所指的数据库中schema用户导入到本地数据库来。如：
F:\oracle\product\10.2.0\db_1\BIN>impdp logfile=dup_scott.log network_link=to0271 schemas=scott re
map_schema=scott:scott2 remap_tablespace=users:users,temp:temp
Import: Release 10.2.0.3.0 - Production on 星期四, 01 3月, 2007 14:49:46
Copyright (c) 2003, 2005, Oracle. All rights reserved.
用户名: / as sysdba
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
启动 "SYS"."SYS_IMPORT_SCHEMA_03": /******** AS SYSDBA logfile=dup_scott.log network_link=to0271
schemas=scott remap_schema=scott:scott2 remap_tablespace=users:users,temp:temp
正在使用 BLOCKS 方法进行估计...
处理对象类型 SCHEMA_EXPORT/TABLE/TABLE_DATA
使用 BLOCKS 方法的总估计: 320 KB
处理对象类型 SCHEMA_EXPORT/USER
处理对象类型 SCHEMA_EXPORT/SYSTEM_GRANT
处理对象类型 SCHEMA_EXPORT/ROLE_GRANT
处理对象类型 SCHEMA_EXPORT/DEFAULT_ROLE
处理对象类型 SCHEMA_EXPORT/TABLESPACE_QUOTA
处理对象类型 SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
处理对象类型 SCHEMA_EXPORT/TABLE/TABLE
. . 导入了 "SCOTT2"."TAB1" 20000 行
. . 导入了 "SCOTT2"."TAB2" 0 行
处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/INDEX
处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
处理对象类型 SCHEMA_EXPORT/PROCEDURE/PROCEDURE
处理对象类型 SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
处理对象类型 SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
处理对象类型 SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
作业 "SYS"."SYS_IMPORT_SCHEMA_03" 已于 14:50:10 成功完成
这里的SCOTT用户实际是network_link所指的数据库的用户，scott2 建在本地数据库。
我上面理解成：把本地的 scott 的对象导入到network_link所指的数据库新建的scott2 中。
2007-3-7 07:53
dawuyu
通过dblink impdp的时候 是不是源db还是先做了expdp
这样的效率和直接expdp再impdp是不是相同
这样的效率和直接expdp再impdp是不是相同
2007-3-7 09:13
ZALBB
看来你没看明白我的文章
[QUOTE][i]最初由 dawuyu 发布[/i]
[B]通过dblink impdp的时候 是不是源db还是先做了expdp
这样的效率和直接expdp再impdp是不是相同 [/B][/QUOTE]
肯定不同，EXPDP/IMPDP 所操作的是连接上的数据库，
也就是，连接上哪个数据库，就对哪个数据库作EXPDP/IMPDP
操作。因此，若先用EXPDP把数据导出来，则还得需要搬到
IMPDP所操纵的数据库。而采用DB LINK的意思，就是让这些
数据通过LINK来传送到目标数据库，避免人为的去搬迁这些数据。
这正是本文的核心。
[QUOTE][i]最初由 dawuyu 发布[/i]
[B]通过dblink impdp的时候 是不是源db还是先做了expdp
这样的效率和直接expdp再impdp是不是相同 [/B][/QUOTE]
肯定不同，EXPDP/IMPDP 所操作的是连接上的数据库，
也就是，连接上哪个数据库，就对哪个数据库作EXPDP/IMPDP
操作。因此，若先用EXPDP把数据导出来，则还得需要搬到
IMPDP所操纵的数据库。而采用DB LINK的意思，就是让这些
数据通过LINK来传送到目标数据库，避免人为的去搬迁这些数据。
这正是本文的核心。
2007-3-7 12:07
kickster
学习。
2007-3-7 23:11
peak98371033
学习！
2007-3-16 17:13
cyr1974
没错 少了一步搬移操作 而且不需要创建目录 再授权
2007-3-27 17:07
bbkingbbking
ZALBB斑竹，你这个是针对IMPDP的，那IMP和EXP可以这样用数据链吗
2007-3-27 20:07
warehouse
不错!
2007-3-27 20:13
ZALBB
[QUOTE][i]最初由 bbkingbbking 发布[/i]
[B]ZALBB斑竹，你这个是针对IMPDP的，那IMP和EXP可以这样用数据链吗 [/B][/QUOTE]
若也有这个功能，早10年就被发现了。:confused:
[B]ZALBB斑竹，你这个是针对IMPDP的，那IMP和EXP可以这样用数据链吗 [/B][/QUOTE]
若也有这个功能，早10年就被发现了。:confused:
2007-5-25 10:13
qsxing
还有，好像用这个方法不支持long列的数据
报错:
无法使用网络链接来加载/卸载long列
报错:
无法使用网络链接来加载/卸载long列
2008-6-18 15:04
pulf
bucuo
2008-8-5 16:14
owlstudio
不错，试一下
2008-8-5 21:58
football2006
学习了
页: [1]
查看完整版本: 使用impdp复制oracle的schema
Powered by ITPUB论坛