在import 操作的时候,可以使用REMAP_DATAFILE, REMAP_SCHEMA, REMAP_TABLESPACE, and TRANSFORM命令完成,表空间的映射,以及重新定义对象数据的所属的用户。
REMAP_DATAFILE
可以讲源数据库的数据文件,映射到目标的数据库文件中去。只能用于全库导出,导入到新库的时候
*expdp yyp/oracle directory=dir0 full=y dumpfile=full.dmp parallel=2 job_name=yyp
*impdp yyp/oracle parfile=para.pra
[oracle@localhost dir0]$ cat para.pra
DIRECTORY=dir0
FULL=Y
DUMPFILE=full.dmp
REMAP_DATAFILE=\'/oracle/database/oradata/orcl/p2.dbf\':\'/oracle/database/oradata/orcl/p3.dbf\'
------REMAP_DATAFILE的目标文件不需要已经存在。
SQL> /
FILE_NAME
--------------------------------------------------------------------------------
/oracle/database/oradata/orcl/users01.dbf
/oracle/database/oradata/orcl/sysaux01.dbf
/oracle/database/oradata/orcl/undotbs01.dbf
/oracle/database/oradata/orcl/system01.dbf
/oracle/database/oradata/orcl/example01.dbf
/oracle/database/oradata/orcl/p2.dbf
6 rows selected.
SQL> drop tablespace P2;
Tablespace dropped.
SQL> /
FILE_NAME
--------------------------------------------------------------------------------
/oracle/database/oradata/orcl/users01.dbf
/oracle/database/oradata/orcl/sysaux01.dbf
/oracle/database/oradata/orcl/undotbs01.dbf
/oracle/database/oradata/orcl/system01.dbf
/oracle/database/oradata/orcl/example01.dbf
/oracle/database/oradata/orcl/p3.dbf
6 rows selected.
*REMAP_TABLESPACE
remap_tablespace,可以将源表空间的所有的内容映射到目标表空间中去,要求目标表空间的已经存在。
导出表空间问题:
[oracle@localhost dir0]$ expdp yyp/oracle directory=dir0 dumpfile=tbs2.dmp tablespaces=P2
Export: Release 10.2.0.1.0 - Production on Saturday, 03 November, 2012 12:56:37
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "YYP"."SYS_EXPORT_TABLESPACE_01": yyp/******** directory=dir0 dumpfile=tbs2.dmp tablespaces=P2
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
ORA-39167: Tablespace P2 was not found.
ORA-31655: no data or metadata objects selected for job
Job "YYP"."SYS_EXPORT_TABLESPACE_01" completed with 2 error(s) at 12:57:20
明明能在dba_talespaces视图中看到P2,但是在dba_segments中不存在该tablespace的记录。原因就是表空间P2中为空。
首先创建在表空间P2的表。
SQL> create table testP2(id number) tablespace P2;
Table created.
SQL> select distinct tablespace_name from dba_segments;
TABLESPACE_NAME
------------------------------------------------------------
SYSAUX
UNDOTBS1
USERS
P2
SYSTEM
EXAMPLE
6 rows selected.
在执行导出:
[oracle@localhost dir0]$ expdp yyp/oracle directory=dir0 dumpfile=tbs2.dmp tablespaces=P2
Export: Release 10.2.0.1.0 - Production on Saturday, 03 November, 2012 13:25:05
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "YYP"."SYS_EXPORT_TABLESPACE_01": yyp/******** directory=dir0 dumpfile=tbs2.dmp tablespaces=P2
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "YYP"."TESTP2" 0 KB 0 rows
Master table "YYP"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for YYP.SYS_EXPORT_TABLESPACE_01 is:
/tmp/dir0/tbs2.dmp
Job "YYP"."SYS_EXPORT_TABLESPACE_01" successfully completed at 13:26:00
[oracle@localhost dir0]$
SQL> create tablespace P1 datafile '/oracle/database/oradata/orcl/p1.dbf';
create tablespace P1 datafile '/oracle/database/oradata/orcl/p1.dbf'
*
ERROR at line 1:
ORA-01119: error in creating database file
'/oracle/database/oradata/orcl/p1.dbf'
ORA-17610: file '/oracle/database/oradata/orcl/p1.dbf' does not exist and no
size specified
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
SQL> create tablespace P1 datafile '/oracle/database/oradata/orcl/p1.dbf' size 10M;
Tablespace created.
[oracle@localhost dir0]$ impdp yyp/oracle REMAP_TABLESPACE=P2:P1 directory=dir0 dumpfile=tbs2.dmp
Import: Release 10.2.0.1.0 - Production on Saturday, 03 November, 2012 13:45:56
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "YYP"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "YYP"."SYS_IMPORT_FULL_01": yyp/******** REMAP_TABLESPACE=P2:P1 directory=dir0 dumpfile=tbs2.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39151: Table "YYP"."TESTP2" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Job "YYP"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 13:46:18
----------------该错误的原因是因为表"YYP"."TESTP2"已经存在,这里测试的时候将"YYP"."TESTP2"所在的表空间都删除。
SQL> drop tablespace P2 including contents and datafiles;
[oracle@localhost dir0]$ impdp yyp/oracle REMAP_TABLESPACE=P2:P1 directory=dir0 dumpfile=tbs2.dmp
Import: Release 10.2.0.1.0 - Production on Saturday, 03 November, 2012 13:48:42
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "YYP"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "YYP"."SYS_IMPORT_FULL_01": yyp/******** REMAP_TABLESPACE=P2:P1 directory=dir0 dumpfile=tbs2.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "YYP"."TESTP2" 0 KB 0 rows
Job "YYP"."SYS_IMPORT_FULL_01" successfully completed at 13:49:11
SQL> select tablespace_name from dba_segments where segment_name='TESTP2';
TABLESPACE_NAME
------------------------------------------------------------
P1
REMAP_SCHEMA
语法:REMAP_SCHEMA=source_schema:target_schema
impdp的时候,可以将源schema的对象,映射到目标schema。source_schema必须要不同,但是不同的source_schema可以映射到同一个target_schema中去。target_schema可以预先存在,若不存在,在impdp的时候,target_schema会被自动创建。前提是impdp时的用户有足够的权限。
[oracle@localhost dir0]$ expdp yyp/oracle directory=dir0 dumpfile=rmschema.dmp schemas=scott
[oracle@localhost dir0]$ impdp yyp/oracle directory=dir0 dumpfile=rmschema.dmp remap_schema=scott:scott2
检查scott2用户的对象
SQL> select segment_name from dba_segments where wner='SCOTT2';
SEGMENT_NAME
--------------------------------------------------------------------------------
DEPT
EMP
BONUS
SALGRADE
STAT_TABLE
PK_DEPT
PK_EMP
STAT_TABLE
JOB_IDX
EMP_BM_IDX
10 rows selected.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25651216/viewspace-748259/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25651216/viewspace-748259/