很多人在進(jìn)行數(shù)據(jù)遷移時(shí)，希望把數(shù)據(jù)導(dǎo)入不同于原系統(tǒng)的表空間，在導(dǎo)入之后卻往往發(fā)現(xiàn)，數(shù)據(jù)被導(dǎo)入了原表空間。
本例舉例說明解決這個(gè)問題:
1.如果缺省的用戶具有DBA權(quán)限
那么導(dǎo)入時(shí)會(huì)按照原來的位置導(dǎo)入數(shù)據(jù)，即導(dǎo)入到原表空間
$ imp bjbbs/passwd file=bj_bbs.dmp fromuser=jive touser=bjbbs grants=n
Import: Release 8.1.7.4.0 - Production on Mon Sep 22 11:49:41 2003
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to: Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production
Export file created by EXPORT:V08.01.07 via conventional path
Warning: the objects were exported by JIVE, not by you
import done in ZHS16GBK character set and ZHS16GBK NCHAR character set
. . importing table "HS_ALBUMINBOX" 12 rows imported
. . importing table "HS_ALBUM_INFO" 47 rows imported
. . importing table "HS_CATALOG" 13 rows imported
. . importing table "HS_CATALOGAUTHORITY" 5 rows imported
. . importing table "HS_CATEGORYAUTHORITY" 0 rows imported
....
. . importing table "JIVEUSERPROP" 4 rows imported
. . importing table "JIVEWATCH" 0 rows imported
. . importing table "PLAN_TABLE" 0 rows imported
. . importing table "TMZOLDUSER" 3 rows imported
. . importing table "TMZOLDUSER2" 3 rows imported
About to enable constraints...
Import terminated successfully without warnings.
查詢發(fā)現(xiàn)仍然導(dǎo)入了USER表空間
$ sqlplus bjbbs/passwd
SQL*Plus: Release 8.1.7.0.0 - Production on Mon Sep 22 11:50:03 2003
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production
SQL> select table_name,tablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
HS_ALBUMINBOX USERS
HS_ALBUM_INFO USERS
HS_CATALOG USERS
HS_CATALOGAUTHORITY USERS
HS_CATEGORYAUTHORITY USERS
HS_CATEGORYINFO USERS
HS_DLF_DOWNLOG USERS
...
JIVEWATCH USERS
PLAN_TABLE USERS
TMZOLDUSER USERS
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TMZOLDUSER2 USERS
45 rows selected.
2.回收用戶unlimited tablespace權(quán)限
這樣就可以導(dǎo)入到用戶缺省表空間
SQL> create user bjbbs identified by passwd
2 default tablespace bjbbs
3 temporary tablespace temp
4 /
User created.
SQL> grant connect,resource to bjbbs;
Grant succeeded.
SQL> grant dba to bjbbs;
Grant succeeded.
SQL> revoke unlimited tablespace from bjbbs;
Revoke succeeded.
SQL> alter user bjbbs quota 0 on users;
User altered.
SQL> alter user bjbbs quota unlimited on bjbbs;
User altered.
SQL> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production
重新導(dǎo)入數(shù)據(jù)
$ imp bjbbs/passwd file=bj_bbs.dmp fromuser=jive touser=bjbbs grants=n
Import: Release 8.1.7.4.0 - Production on Mon Sep 22 12:00:51 2003
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to: Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production
Export file created by EXPORT:V08.01.07 via conventional path
Warning: the objects were exported by JIVE, not by you
import done in ZHS16GBK character set and ZHS16GBK NCHAR character set
. . importing table "HS_ALBUMINBOX" 12 rows imported
. . importing table "HS_ALBUM_INFO" 47 rows imported
. . importing table "HS_CATALOG" 13 rows imported
. . importing table "HS_CATALOGAUTHORITY" 5 rows imported
. . importing table "HS_CATEGORYAUTHORITY" 0 rows imported
. . importing table "HS_CATEGORYINFO" 9 rows imported
. . importing table "HS_DLF_DOWNLOG" 0 rows imported
....
. . importing table "JIVEUSER" 102 rows imported
. . importing table "JIVEUSERPERM" 81 rows imported
. . importing table "JIVEUSERPROP" 4 rows imported
. . importing table "JIVEWATCH" 0 rows imported
. . importing table "PLAN_TABLE" 0 rows imported
. . importing table "TMZOLDUSER" 3 rows imported
. . importing table "TMZOLDUSER2" 3 rows imported
About to enable constraints...
Import terminated successfully without warnings.
SQL> select table_name,tablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
HS_ALBUMINBOX BJBBS
HS_ALBUM_INFO BJBBS
HS_CATALOG BJBBS
HS_CATALOGAUTHORITY BJBBS
....
JIVETHREAD BJBBS
JIVETHREADPROP BJBBS
JIVEUSER BJBBS
JIVEUSERPERM BJBBS
JIVEUSERPROP BJBBS
JIVEWATCH BJBBS
PLAN_TABLE BJBBS
TMZOLDUSER BJBBS
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TMZOLDUSER2 BJBBS
45 rows selected.
現(xiàn)在數(shù)據(jù)被導(dǎo)入到正確的用戶表空間中
開心過好每一天。。。。。