通過oracle10g exp/imp在不同表空間間遷移資料

寫這個文章，主要是以前對於quota on語法掌握不牢固，聖人有言，溫故而知新，故寫此文;

大體分為如下幾步：
1,利用exp對zxy使用者進行邏輯資料匯出
[oracle@capitek3 ~]$ exp userid=zxy/system file=~/export_zxy.dmp statistics=none

Export: Release 10.2.0.1.0 - Production on Wed Mar 17 08:00:20 2010

Copyright (c) 1982, 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
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user ZXY
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user ZXY
About to export ZXY's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export ZXY's tables via Conventional Path ...
. . exporting table                             MM          0 rows exported
. . exporting table                             T1    2097152 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

2,執行imp，準備把第1步匯出的資料遷移到另一個表空間part1,在在此之前，請進行如下準備工作，不然imp會把資料匯入原來的表空間喲
   可分下面幾小步:
       a,以sysdba進入資料庫，alter user zxy  quota 0 on zxy;--quota表明是對錶空間的配額許可權，on所跟的zxy為表空間（也就是要遷移使用者所對應的原來表空間）；大家發現沒，把quota 設定成0,這樣zxy就對錶空間zxy沒有操作許可權了喲
 
3,開始進行把zxy使用者（對應表空間zxy)遷移到part 使用者（對應表空間為part),輸入如下命令

[oracle@capitek3 ~]$ imp userid=zxy/system fromuser=zxy touser=part1 file=export_zxy.dmp log=imp_sex.log --切記：imp使用者必須要用system使用者或具有dba角色的使用者

Import: Release 10.2.0.1.0 - Production on Wed Mar 17 08:05:40 2010

Copyright (c) 1982, 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

Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
IMP-00007: must be a DBA to import objects to another user's account
IMP-00000: Import terminated unsuccessfully
[oracle@capitek3 ~]$ imp userid=system/system fromuser=zxy touser=part1 file=export_zxy.dmp log=imp_sex.log  --記得新增log選項，這樣一旦出錯，便於你分析問題

Import: Release 10.2.0.1.0 - Production on Wed Mar 17 08:06:02 2010

Copyright (c) 1982, 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

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

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

import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing ZXY's objects into PART1
. . importing table                           "MM"          0 rows imported
. . importing table                           "T1"    2097152 rows imported
Import terminated successfully without warnings.
[oracle@capitek3 ~]$ sqlplus part1/system  --到遷移目標使用者進行查詢工作成果

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Mar 17 08:06:25 2010

Copyright (c) 1982, 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

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
N1                             TABLE
MM                             TABLE
T1                             TABLE

SQL> select count(*) from t1;

  COUNT(*)
----------
   2097152

SQL>
  

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-629849/，如需轉載，請註明出處，否則將追究法律責任。