oracle remap schema,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;

阅读(678) | 评论(0) | 转发(0) |