很多人在进行数据迁移时希望把数据导入不同于原系统的表空间在导入之后却往往发现数据被导入了原表空间
本例举例说明解决这个问题:
如果缺省的用户具有DBA权限
那么导入时会按照原来的位置导入数据即导入到原表空间
$ imp bjbbs/passwd file=bj_bbsdmp fromuser=jive touser=bjbbs grants=n
Import: Release Production on Mon Sep ::
(c) Copyright Oracle CorporationAll rights reserved
Connected to: Oraclei Enterprise Edition Release bit Production
With the Partitioning option
JServer Release bit Production
Export file created by EXPORT:V via conventional path
Warning: the objects were exported by JIVE not by you
import done in ZHSGBK character set and ZHSGBK NCHAR character set
importing tableHS_ALBUMINBOX rows imported
importing tableHS_ALBUM_INFO rows imported
importing table HS_CATALOG rows imported
importing tableHS_CATALOGAUTHORITY rows imported
importing table HS_CATEGORYAUTHORITY rows imported
importing table JIVEUSERPROP rows imported
importing tableJIVEWATCH rows imported
importing table PLAN_TABLE rows imported
importing table TMZOLDUSER rows imported
importing tableTMZOLDUSER rows imported
About to enable constraints
Import terminated successfully without warnings
查询发现仍然导入了USER表空间
$ sqlplus bjbbs/passwd
SQL*Plus: Release Production on Mon Sep ::
(c) Copyright Oracle CorporationAll rights reserved
Connected to:
Oraclei Enterprise Edition Release bit Production
With the Partitioning option
JServer Release bit Production
SQL> select table_nametablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME
HS_ALBUMINBOXUSERS
HS_ALBUM_INFOUSERS
HS_CATALOG USERS
HS_CATALOGAUTHORITYUSERS
HS_CATEGORYAUTHORITY USERS
HS_CATEGORYINFOUSERS
HS_DLF_DOWNLOG USERS
JIVEWATCHUSERS
PLAN_TABLE USERS
TMZOLDUSER USERS
TABLE_NAME TABLESPACE_NAME
TMZOLDUSERUSERS
rows selected
回收用户unlimited tablespace权限
这样就可以导入到用户缺省表空间
SQL> create user bjbbs identified by passwd
default tablespace bjbbs
temporary tablespace temp
/
User created
SQL> grant connectresource to bjbbs;
Grant succeeded
SQL> grant dba to bjbbs;
Grant succeeded
SQL> revoke unlimited tablespace from bjbbs;
Revoke succeeded
SQL> alter user bjbbs quota on users;
User altered
SQL> alter user bjbbs quota unlimited on bjbbs;
User altered
SQL> exit
Disconnected from Oraclei Enterprise Edition Release bit Production
With the Partitioning option
JServer Release bit Production
重新导入数据
$ imp bjbbs/passwd file=bj_bbsdmp fromuser=jive touser=bjbbs grants=n
Import: Release Production on Mon Sep ::
(c) Copyright Oracle CorporationAll rights reserved
Connected to: Oraclei Enterprise Edition Release bit Production
With the Partitioning option
JServer Release bit Production
Export file created by EXPORT:V via conventional path
Warning: the objects were exported by JIVE not by you
import done in ZHSGBK character set and ZHSGBK NCHAR character set
importing tableHS_ALBUMINBOX rows imported
importing tableHS_ALBUM_INFO rows imported
importing table HS_CATALOG rows imported
importing tableHS_CATALOGAUTHORITY rows imported
importing table HS_CATEGORYAUTHORITY rows imported
importing tableHS_CATEGORYINFO rows imported
importing table HS_DLF_DOWNLOG rows imported
importing table JIVEUSER rows imported
importing table JIVEUSERPERM rows imported
importing table JIVEUSERPROP rows imported
importing tableJIVEWATCH rows imported
importing table PLAN_TABLE rows imported
importing table TMZOLDUSER rows imported
importing tableTMZOLDUSER rows imported
About to enable constraints
Import terminated successfully without warnings
SQL> select table_nametablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME
HS_ALBUMINBOXBJBBS
HS_ALBUM_INFOBJBBS
HS_CATALOG BJBBS
HS_CATALOGAUTHORITYBJBBS
JIVETHREAD BJBBS
JIVETHREADPROP BJBBS
JIVEUSER BJBBS
JIVEUSERPERM BJBBS
JIVEUSERPROP BJBBS
JIVEWATCHBJBBS
PLAN_TABLE BJBBS
TMZOLDUSER BJBBS
TABLE_NAME TABLESPACE_NAME
TMZOLDUSERBJBBS
rows selected
现在数据被导入到正确的用户表空间中
