很多人在进行数据迁移时，希望把数据导入不同于原系统的表空间，在导入之后却往往发现，数据被导入了原表空间。本例举例说明解决这个问题:1.如果缺省的用户具有DBA权限那么导入时会按照原来的位置导入数据，即导入到原表空间
$ 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 ProductionWith the Partitioning optionJServer 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 importedAbout to enable constraints...Import terminated successfully without warnings.
查询发现仍然导入了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 ProductionWith the Partitioning optionJServer Release 8.1.7.4.0 - 64bit Production
SQL> select table_name,tablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME------------------------------ ------------------------------HS_ALBUMINBOX USERSHS_ALBUM_INFO USERSHS_CATALOG USERSHS_CATALOGAUTHORITY USERSHS_CATEGORYAUTHORITY USERSHS_CATEGORYINFO USERSHS_DLF_DOWNLOG USERS...JIVEWATCH USERSPLAN_TABLE USERSTMZOLDUSER USERS
TABLE_NAME TABLESPACE_NAME------------------------------ ------------------------------TMZOLDUSER2 USERS
45 rows selected.2.回收用户unlimited tablespace权限这样就可以导入到用户缺省表空间
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> exitDisconnected from Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit ProductionWith the Partitioning optionJServer Release 8.1.7.4.0 - 64bit Production
重新导入数据
$ 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 ProductionWith the Partitioning optionJServer 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 importedAbout to enable constraints...Import terminated successfully without warnings.
SQL> select table_name,tablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME------------------------------ ------------------------------HS_ALBUMINBOX BJBBSHS_ALBUM_INFO BJBBSHS_CATALOG BJBBSHS_CATALOGAUTHORITY BJBBS....JIVETHREAD BJBBSJIVETHREADPROP BJBBSJIVEUSER BJBBSJIVEUSERPERM BJBBSJIVEUSERPROP BJBBSJIVEWATCH BJBBSPLAN_TABLE BJBBSTMZOLDUSER BJBBS
TABLE_NAME TABLESPACE_NAME------------------------------ ------------------------------TMZOLDUSER2 BJBBS
45 rows selected.
现在数据被导入到正确的用户表空间中. -----
