如何imp到指定的user和tablesapce

[oracle@localhost ~]$ exp scott/tiger file=/data/exprt/scottexp.dmp
[oracle@localhost ~]$ imp system/manager file=/data/exprt/scottexp.dmp fromuser=scott touser=newbie

Import: Release 9.2.0.4.0 - Production on Thu Dec 15 12:20:54 2005
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path

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

import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses UTF8 character set (possible charset conversion)
. importing SCOTT's objects into NEWBIE
. . importing table                        "BONUS"          0 rows imported
. . importing table                         "DEPT"          4 rows imported
. . importing table                          "EMP"         14 rows imported
. . importing table                   "PLAN_TABLE"          7 rows imported
. . importing table                     "SALGRADE"          5 rows imported
. . importing table                           "T1"          0 rows imported
About to enable constraints...
Import terminated successfully without warnings.

SQL> select segment_name,owner, tablespace_name 
  2  from dba_segments
  3  where owner='NEWBIE';

SEGMENT_NAME         OWNER                          TABLESPACE_NAME
-------------------- ------------------------------ --------------------
BONUS                NEWBIE                         USERS
DEPT                 NEWBIE                         USERS
EMP                  NEWBIE                         USERS
PLAN_TABLE           NEWBIE                         USERS
SALGRADE             NEWBIE                         USERS
T1                   NEWBIE                         USERS
PK_DEPT              NEWBIE                         USERS
PK_EMP               NEWBIE                         USERS

8 rows selected.

 

需要在create新user的时候，权限要限制。

我们一般在create user的时候都给connect,resource这2个权限，其实resource这个权限是指这个用户能写到任何表空间。
所以我们在create new user的时候，只给connect权限就可，并指定表空间就行。

create user jiayuan2000 identified by passwd default tablespace jiayuan;
grant connect to jiayuan2000;
alter user jiayuan2000 quota unlimited on jiayuan;

这个时候在执行IMP，这样默认就会在指定的表空间。

需要注意分区表的情况，如果导出有分区表（9i分区表只能一个一个表导出，10G可以整体导出），在导入的时候会默认分区表在源表空间，不

知道是否是一个BUG。

这里可以采用先把分区表的表结构建好在IMP使用IGNORE=y来忽略表已存在，可以解决这个问题。