export import tablespace problem

Here is what has happened -

Server 1- JUPITER
Tablespace - KERMIT, PRODUCTION etc
Users - Only one user TEST on KERMIT tablespace with DBA priviledge. Tablespace PRODUCTION is
used for some other users but not TEST

Server 2- EARTH
Tablespace - PRODUCTION, CYCLONE etc
Users - User UPPROD on PRODUCTION tablespace with DBA priviledge

So when the export was done from Server 2- EARTH, Tablespace - PRODUCTION, User - UPPROD
and then was imported to Server 1- JUPITER, User - TEST with DEFAULT Tablespace - KERMIT,
Oracle started to insert something in Tablespace - PRODUCTION on Server 1- JUPITER for user TEST
even though the default tablespace for user TEST was KERMIT and I had used fromuser touser in import
and also it had enough space in it for the import.
After the import was done I saw tablespace PRODUCTION being used instead of KERMIT as KERMIT was empty.
I even tried taking out the DBA priviledge but nothing seems to work.
So I imported user TEST with show = y and editing the INDEX file for tablespace ( changing it from PRODUCTION
to KERMIT) and then doing the import.
This worked for me. This time it did use KERMIT tablespace and not PRODUCTION on server1.
Similar thing happened with some other users with tablespace PRODUCTION being imported to server1 to some other tablespaces.

My questions are :
1. Why would this happen, what can I do other than creating an INDEX file everytime this happens and PRODUCTION
tablespace getting filled up with something.
2. Is there any query or way to know what all users have used PRODUCTION tablespace on server1 by mistake ?
In other words to see whats in that PRODUCTION tablespace ?

Thanks
Sonali

1. That's the way I would do it, with INDEXFILE.

2. select owner, segment_name, tablespace_name
from dba_segments
where tablespace_name = 'PRODUCTION'
order by owner

or

select owner, tablespace_name, segment_type, count(*)
from dba_segments
group by owner, tablespace_name, segment_type
order by tablespace_name, owner

Depending on the release of Oracle, prior to import you can revoke all privileges on Production tablespace from the test id. Make sure KERMIT is the default tablespace for the test id. Then when the import fails to create the table in Production it will fall back to Kermit for it's creates.

At least I have seen this solution posted elsewhere.

Thanks marist, I will try to see whats in that tablespace now.
Jrpm- I am using Oracle 8.0.6. I also have Oracle 8.1.7 but I think the same thing happens with these 2 releases.
I have tried revoking all the privileges for TEST user on production like I said before that did not work either.

Thanks

Hi, Sonali.

Why don´t you try setting the PRODUCTION tablespace offline and repeat all the steps? Maybe an error will occur and it's gonna be easier to understand why that happens.

Best regards,

Luis Nobre, from Brazil.

The TEST user is granted DBA privileges, which, I believe, also grants RESOURCE. Revoke RESOURCE from TEST, then grant CREATE SESSION (since CREATE SESSION is part of the RESOURCE privileges) to TEST. THEN try your import again after revoking all privileges to PRODUCTION. You'll find, I believe, that the import will finally use the KERMIT tablespace to store the objects imported into TEST.

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).

Quote:

---

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.

Quote:

---

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.