oracle imp时表空间,imp时指定表空间

今天终于把imp时指定表空间的问题搞定了！

为了只是看看效果，所以我只导了两张表做实验

摘要：

exp mctest3/mctest file=/home/oracle/order0016.dmp

tables=order0015_purchase_plan_detail,order0014_purchase_plan

select * from user_tables where

table_name='ORDER0015_PURCHASE_PLAN_DETAIL' or

table_name='ORDER0014_PURCHASE_PLAN'

revoke unlimited tablespace from simply

alter user simply quota unlimited on simply

alter user simply quota 0 on system

imp fromuser=mctest3 touser=simply file=/home/oracle/order0016.dmp

TABLESPACES=SIMPLY

步骤一，导出单张表

37服务器上mctest3模式里的

'ORDER0015_PURCHASE_PLAN_DETAIL'

'ORDER0014_PURCHASE_PLAN'

[oracle@meta ~]$ exp mctest3/mctest

file=/home/oracle/order0016.dmp

tables=order0015_purchase_plan_detail,order0014_purchase_plan

在37服务器上查询结果如下

结果：

TABLE_NAME

|

TABLESPACE_NAME

---------------------------------------------------------------------------------

ORDER0014_PURCHASE_PLAN

| SYSTEM

----------------------------------------------------------------------------------

ORDER0015_PURCHASE_PLAN_DETAIL

|

SYSTEM

步骤二：导入数据同时指定表空间

现在我往simply库里面导

首先先设置simply用户对各种表空间的quota

revoke unlimited tablespace from simply

alter user simply quota unlimited on simply

alter user simply quota 0 on system;//将用户在System表空间的配额置为0

实验证明，上面三个缺一不可！！

[oracle@simply ~]$ imp fromuser=mctest3 touser=simply

file=/home/oracle/order0016.dmp TABLESPACES=SIMPLY

. . importing table

"ORDER0015_PURCHASE_PLAN_DETAIL"

1219 rows imported

. . importing table

"ORDER0014_PURCHASE_PLAN"

264 rows imported

Import terminated successfully without

warnings.

在simply服务器上查询结果如下

select * from user_tables where table_name='ORDER0015_PURCHASE_PLAN_DETAIL'

or table_name='ORDER0014_PURCHASE_PLAN'

结果：

TABLE_NAME

|

TABLESPACE_NAME

--------------------------------------------------------------------------------

ORDER0014_PURCHASE_PLAN

| SIMPLY

-------------------------------------------------------------------------------

ORDER0015_PURCHASE_PLAN_DETAIL

|

SIMPLY

ok，宣告成功！