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 ?