expdp / impdp 之 remap_schema remap_tablespace

     改變一個物件的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.