问：我在进行数据迁移时，本来希望把数据导入到不同于原系统的表空间，在导入之后意外的发现数据却被导入了原表空间。请问应给怎么解决这个问题？
答：我来举一个简单的例子:
首先如果缺省的用户具有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 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.
|
查询发现仍然导入了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.
|
1
2
下一页>>
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> 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
|
最后重新导入数据
$ 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.
|
至此数据即被导入到正确的用户表空间中了。
闽公网安备 35060202000074号