oracle 10g DATA PUMP 的REMAP_SCHEMA和REMAP_TABLESPA

REMAP_SCHEMAP Objects from one schema are loaded into another schema.

CREATE OR REPLACE DIRECTORY test_dir AS ’c:\expdp’;
GRANT READ, WRITE ON DIRECTORY test_dir TO test;
GRANT READ, WRITE ON DIRECTORY test_dir TO test1;
expdp test/test directory=test_dir dumpfile=test.dmp logfile=imp.log
impdp system/system directory=test_dir repmap_schema=test:test1 dumpfile=test.dmp logfile=imp.log
这里需要注意的是repmap_schema=RESOURCE:TARGET
REMAP_TABLESPACE Tablespace object are remapped to another tablespace.
expdp system/system directory=test_dir tablespaces=users dumpfile=tablespace.dmp logfile=tlog.log
impdp system/system directory=test_dir remap_tablespace=zhang:users dumpfile=tablespace.dmp logfile=timp.log
实验完成.这个实验可以方便的帮助DBA将一个用户下的所有对象转移为另外一个用户,可以将一个表空间内的内容转移到另外一个表空间.
继续更新２个脚本
这个脚本是的作用是将ＴＥＳＴ中的ＥＭＰ表导入到ＴＥＳＴ１３中．
impdp system/system schemas=test directo
ry=test_dir remap_schema=test:test13 dumpfile=test12.dmp include = TABLE:" in (’EMP’) " logfile=0824log1.log;
另外这个是将ＴＥＳＴ用户ＲＥＭＡＰ为ＴＥＳＴ１３
但是发现一个问题
SQL> select username,default_tablespace from dba_users;

USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
MGMT_VIEW SYSTEM
SYS SYSTEM
SYSTEM SYSTEM
DBSNMP SYSAUX
SYSMAN SYSAUX
SCOTT USERS
PART USERS
OSS USERS
TEST USERS
TEST12 ZHANG
HR USERS

USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
OUTLN SYSTEM
MDSYS SYSAUX
ORDSYS SYSAUX
EXFSYS SYSAUX
DMSYS SYSAUX
WMSYS SYSAUX
CTXSYS SYSAUX
ANONYMOUS SYSAUX
XDB SYSAUX
ORDPLUGINS SYSAUX
SI_INFORMTN_SCHEMA SYSAUX

USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
OLAPSYS SYSAUX
TSMSYS USERS
BI USERS
PM USERS
MDDATA USERS
IX USERS
SH USERS
DIP USERS
OE USERS

已选择31行。

SQL> connect test/test
已连接。
SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
SALARY
AUTHORS
BOOKS
TEST
A
B
TEMP
T
FLASH
FLASH1
INVENTORY

TABLE_NAME
------------------------------
T_NEW
DEPT
EMP
DEMO
TEST1
TEST2
TEST3
TEST_LOG
EMP_TEST
PART
PARTEST

TABLE_NAME
------------------------------
SYS_TEMP_FBT

已选择23行。

SQL> connect / as sysdba
已连接。
SQL> desc dba_segments
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
SEGMENT_NAME VARCHAR2(81)
PARTITION_NAME VARCHAR2(30)
SEGMENT_TYPE VARCHAR2(18)
TABLESPACE_NAME VARCHAR2(30)
HEADER_FILE NUMBER
HEADER_BLOCK NUMBER
BYTES NUMBER
BLOCKS NUMBER
EXTENTS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
RELATIVE_FNO NUMBER
BUFFER_POOL VARCHAR2(7)

SQL> select owner,segment_name,tablespace_name from dba_segments where segment_name=’EMP’;

OWNER
------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
TEST
EMP
USERS

TEST12
EMP
USERS

OWNER
------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------

SCOTT
EMP
USERS
为了解决这个问题，修正脚本为
impdp system/system schemas=test directo
ry=test_dir remap_schema=test:test13 dumpfile=test12.dmp remap_tablespace=users:zhang logfile=0824log1.log;