expdp / impdp 之 remap_schema remap_tablespace

改变一个对象的 owner ， 以及对象所在的 tablespace ， oracle 的 exp/imp 提供了以下两种方式：

A. exp/imp 的 fromuser touser 参数

B.expdpimpdp 的 remap_schema remap_tablespace

但是对于跨字符集平台，以及迁移速度个人更倾向于expdp/impdp 具体实例如下：

[@more@]

改变一个对象的owner， 以及对象所在的tablespace， oracle 的exp/imp提供了以下两种方式：

A. exp/imp 的 fromuser touser 参数

B.expdpimpdp 的 remap_schema remap_tablespace

但是对于跨字符集平台，以及迁移速度个人更倾向于expdp/impdp 具体实例如下：

(1) exp/imp

[oracle@stdtdb2 expback]$ exp p_stdt/gabriel$0814 file=/home/oracle/backup/expback/a.dmp tables=OS_USERBILL_ACTIVE_USER_MONTH log=/home/oracle/backup/expback/a.log

Export: Release 10.2.0.4.0 - Production on Tue Aug 24 09:36:26 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
server uses ZHS16CGB231280 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table OS_USERBILL_ACTIVE_USER_MONTH 47831014 rows exported
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.

[oracle@stdtdb2 expback]$ imp p_tdzx02/qazxsw21 file=/home/oracle/backup/expback/a.dmp fromuser=p_stdt touser=p_tdzx02 tables=OS_USERBILL_ACTIVE_USER_MONTH log=/home/oracle/backup/expback/b.log

Import: Release 10.2.0.4.0 - Production on Tue Aug 24 09:43:24 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by P_STDT, not by you

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
import server uses ZHS16CGB231280 character set (possible charset conversion)
. importing P_STDT's objects into P_TDZX02
. . importing table "OS_USERBILL_ACTIVE_USER_MONTH" 47831014 rows imported
Import terminated successfully without warnings.

SQL> select * from nls_database_parameters where parameter='NLS_CHARACTERSET';

PARAMETER
------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_CHARACTERSET
ZHS16CGB231280

******************************************************************************************

(2) expdp/impdp

SQL> create tablespace gabriel datafile '/home/oracle/oradata/mostdt2/gabriel01.dbf' size 5G;

Tablespace created.

SQL> create user gabriel identified by gabriel$0814 account unlock;

User created.

SQL> grant connect,resource to gabriel;

Grant succeeded.

SQL> grant exp_full_database,imp_full_database to gabriel;

Grant succeeded.

SQL> create directory expdir as '/home/oracle/backup/expback/';--注意在设置directory时，末尾的/ 一定要加上，否则会出现找不到文件的情况

Directory created.

SQL> grant read,write on directory expdir to gabriel;

Grant succeeded.

SQL> alter user gabriel quota unlimited on gabriel;

User altered.

[oracle@stdtdb2 expback]$ expdp p_stdt/gabriel$0814 directory=expdir tables=OS_USERBILL_ACTIVE_USER_MONTH dumpfile=p_stdt.dump

logfile=p_stdt.log parallel=2

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 2.937 GB

Processing object type TABLE_EXPORT/TABLE/TABLE

. . exported "P_STDT"."OS_USERBILL_ACTIVE_USER_MONTH" 2.489 GB 47831014 rows

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Master table "P_STDT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for P_STDT.SYS_EXPORT_TABLE_01 is:

/home/oracle/backup/expback/p_stdt.dump

Job "P_STDT"."SYS_EXPORT_TABLE_01" successfully completed at 16:51:23

[oracle@stdtdb2 expback]$ ll

total 2617088

-rw-r--r-- 1 oracle oinstall 771 Aug 23 17:51 import.log

-rw-r----- 1 oracle oinstall 2673270784 Aug 25 16:51 p_stdt.dump

-rw-r--r-- 1 oracle oinstall 1140 Aug 25 16:51 p_stdt.log

[oracle@stdtdb2 expback]$ impdp gabriel/gabriel$0814 directory=expdir tables=OS_USERBILL_ACTIVE_USER_MONTH remap_schema=p_stdt:gabriel remap_tablespace=stdt:gabriel

dumpfile=p_stdt.dump logfile=p_stdt.log parallel=2

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "GABRIEL"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "GABRIEL"."SYS_IMPORT_TABLE_01": gabriel/******** directory=expdir tables=OS_USERBILL_ACTIVE_USER_MONTH remap_schema=p_stdt:gabriel remap_tablespace=stdt:gabriel dumpfile=p_stdt.dump logfile=p_stdt.log parallel=2

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "GABRIEL"."OS_USERBILL_ACTIVE_USER_MONTH" 2.489 GB 47831014 rows

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "GABRIEL"."SYS_IMPORT_TABLE_01" successfully completed at 17:02:01

SQL> conn gabriel/gabriel$0814

Connected.

SQL> select tname from tab;

TNAME

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

OS_USERBILL_ACTIVE_USER_MONTH

SQL> select count(*) from OS_USERBILL_ACTIVE_USER_MONTH;

COUNT(*)

----------

47831014

[oracle@stdtdb2 expback]$ rm -rf p_stdt.*

[oracle@stdtdb2 expback]$ ll

total 3912

-rw-r--r-- 1 oracle oinstall 771 Aug 23 17:51 import.log

-rw-r----- 1 oracle oinstall 3989504 Jul 6 11:53 p_tdzx.dump

-rw-r--r-- 1 oracle oinstall 5098 Jul 6 11:53 p_tdzx.log

SQL> drop user gabriel cascade;

User dropped.

SQL> drop tablespace gabriel including contents and datafiles;

Tablespace dropped.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8117479/viewspace-1052106/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8117479/viewspace-1052106/