I belive the above post deserve some correction as almost every advice above is wrong (and please, take this intervention in a positive spirit, my intentions here are nothing but good).

Originally posted by oratune
The TEST user is granted DBA privileges, which, I believe, also grants RESOURCE.
DBA role has some other roles granted, but RESOURCE is not among them. Of course it has all the RESOURCE privileges, but they have been granted to DBA explicitely, not through RESOURCE role.

Originally posted by oratune
Revoke RESOURCE from TEST, then grant CREATE SESSION (since CREATE SESSION is part of the RESOURCE privileges) to TEST.
A couple of incorrectnes in this statement. CREATE SESSION is not a part of RESORCE role. Even if it was, there will be no point in granting explicit CREATE SESSION after revoking RESORCE, because if iser test has DBA role then he has CREATE SESSION allreday granted through DBA role. And in the context of the original problem there is no point in revoking any ROLE (even DBA) from that user, as roles have nothing to do with the problem.

Now back to the original problem/question: "Why import utility insist in creating segments in the same tablespace (actually in the tablespace with the same name) as they were when they were exporting, even though the destination user has some other tablespace designated as the default tablespace? Only if there is no tablespace with the same name the segments are created in user's default tablespace."

It is funny, how many times this problem has been discussed in this forum (if I'm not mistaken the same question has allready been answered to sonaliak some time ago), yet there is still so much confusion about that. There are a couple of ways how to got around this problem (hacking the dmp file; creating imp indexfile and manually changing the tablespace in CREATE statements; manualy creating segments; etc...), but if you simply want to do it using imp utility, you only have to make sure one thing: DESTINATION USER MUST HAVE NO QUOTA IN THE "ORIGINAL" TABLESPACE WHEN IMPORTING IS PERFORMED. And of course, one additional requirement - it must have sufficient quota on his default tablespace for imp to create all the segments there.

Basicaly the root of the confusion is in one tiny system privilege: UNLIMITED TABLESPACE. This privilege is not part of any role (unles some wise DBA has manualy granted it to some role), but Oracle "silently" grant it as an explict privilege to any user that has been given DBA or RESOURCE role. Irronicaly enough, even if you later revoke any/both of those roles from that user, Oracle does not implicitely revoke this privilege from that user in the same "silent" way. As a result of this, many DBAs don't even know that most of their users have *unlimited* quota on all tablespaces in the database!

So once again the instrunctions for sonaliak's case:

1. Revoke UNLIMITED TABLESPACE system privilege from user TEST:
REVOKE UNLIMITED TABLESPACE FROM test;

2. Just in case user TEST has any explicit quota set on tablespace PRODUCTION, set it to 0:
ALTER USER test QUOTA 0 ON production;

3. Make sure TEST has sufficient quota on his default tablespace KERMIT - if needed raise him that quota or even set it to UNLIMITED:
ALTER USER test QUOTA UNLIMITED ON kermit;

Now perform your import and *all* of the imported objects will be created in tablespace KERMIT.