Well, sometimes you need to do it. My case is simple, a lot of tables created without specifying a tablespace, by a DBA-granted database user.
The procedure is quite straight forward, although it took me some time to figure it out. Because we were migrating to a new server, I considered it a good time to clean up the tablespaces, add some new indexes, partition some tables, so, the first step was to exp/imp the new data. So it’s tablespace time…
I created the new tablespaces, added the fat datafiles, and created the users. As the data was previously dumped, using the DBA user/s previously mentioned, and because a full dump takes about 4 hours, I was stuck using those dump files.
Because only a DBA user can import what another DBA user has previously exported, I had to grant my users DBA privileges. I imported the dump for the first user, giving him quota on his own tablespace, everything went OK. Moving on to importing the second user’s data I noticed something strange. The user’s tablespace was still empty, while the first user’s tablespace was filling up.
This was strange, as the second user didn’t have quota on the first user’s tablespace. I stopped the import, dropped the user and recreated. It took me 4 import attempts to figure out what was happening. Because the second user was a DBA, although he had no quota on the first user’s tablespace, he had unlimited tablespace system privilege. I revoked it and it magically worked.
So, the lesson I learned today is:
If you want to move tables from one tablespace/schema to another, using Oracle:
* export the data using exp
* create the new tablespace
* create the new user, assigning him the new tablespace, and correctly setting the quota to none on all other tablespaces and to unlimited on the target tablespace
* make sure he doesn’t have unlimited tablespace system privilege
* import using the new user using imp
Anyway, if you have a lot of data (in the millions of records), cleaning up your tablespaces, separating your indexes in another tablespace and partitioning your tables and indexes can improve performance by an order of magnitude.