IMP导入分区表

EXP一个库（表空间是TEST1,USER1）
IMP到另一个库里面（表空间是TEST2,USER2）
在要导入的用户(也就是USER2) 的系统权限中取消掉 unlimited tablespace ,然后指定限额， 指定要导入的表空间（TEST2）。
执行
imp test/test@test2 fromuser=USER1 touser=USER2 file=D:\exp\TEST1.dmp

结果：所有普通的表都导入成功，只有几个分区表没有导入，提示报错说对TEST1表空间没有操作权限。

 

解决方法：

1. 导入时生成转储文件(indexfile=...),然后手工编辑这个文件,
将tablespace 参数后面的值替换成users

参考:
Q1: Can one import tables to a different tablespace?

A1:
Oracle offers no parameter to specify a different tablespace
to import data into. Objects will be re-created in the tablespace
they were originally exported from.

One can alter this behaviour by following one of these
procedures:

1. Pre-create the table(s) in the correct tablespace:

Import the dump file using the INDEXFILE= option

Edit the indexfile. Remove remarks and specify the correct
tablespaces.

Run this indexfile against your database, this will create the
required tables in the appropriate tablespaces

Import the table(s) with the IGNORE=Y option.

2.Change the default tablespace for the user:

Revoke the "UNLIMITED TABLESPACE" privilege from the
user

Revoke the user's quota from the tablespace from where the
object was exported. This forces the import utility to create
tables in the user's default tablespace.

Make the tablespace to which you want to import the default
tablespace for the user

Import the table

Q2: Import user to another tablespace
A2:

Here is how I move users to another tablespace. I will use the
schema scott as an example and the tablespace will be
DATA (was in USERS).

exp userid=system/manager parfile=exp_user.par
file=exp_scott.dmp log=exp_scott.log
owner="(scott)"

Parameter File:

BUFFER=4096000
COMPRESS=Y
GRANTS=Y
INDEXES=Y
ROWS=Y
CONSTRAINTS=Y
DIRECT=Y

Enter the following at the SQL prompt:

revoke DBA,RESOURCE from scott;
alter user scott quota unlimited on DATA;
alter user scott quota 0M on USERS;
alter user scott quota 0M on SYSTEM;
grant create procedure to scott;
grant create trigger to scott;
grant create type to scott;

Now import the user:

imp userid=system/manager parfile=imp_user.par
file=exp_scott.dmp log=imp_scott.log
fromuser="(scott)" touser="(scott)"

Parameter File:

BUFFER=4096000
GRANTS=Y
INDEXES=N
IGNORE=Y
ROWS=Y

I realize "Y" is the default for the parameters in the par file
but there are times you want to change them to "N" so I
leave them in. Once you have moved the user you can
grant the privileges back to them.

 

分区表在导入时，一般来说，有两种情况
1、在导入数据库中，与被导入表的分区相关的表空间已对等建立好（表空间名也是与exp数据库中一致的），那么这种情况，与普通表的imp操作是一样的

2、但多数情况下，我们不知道exp数据库中该表的物理结构，此时，可以执行以下步骤
（1）、从dmp文件中获取分区表的物理结构，执行如下命令
C:\Documents and Settings\qcui>imp qcui/qcui@ora9 file=d:\temp\p.dmp fromuser=bi
touser=qcui ignore=y tables=(dhfa_revenue_base) indexfile=d:\temp\dhfa.sql
（2）、根据需要编辑该表的DDL脚本（在本例中就是 d:\temp\dhfa.sql）。
- 如果希望重新规划该表的物理存储结构，则可以先创建好表空间，然后直接编辑DDL脚本，并创建该表
- 如果希望分区表相关的分区表空间就按照exp库组织，那么创建好相关的表空间，直接执行该DDL脚本，即可

3、执行分区表的导入，导入时比一般的imp操作，参数上多增加一个 ignore=y 就可以了