Life as Code

refactoring my life

Moving tables between tablespaces in Oracle

with one comment

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.

About these ads

Written by Bogdan

October 15, 2007 at 7:46 PM

Posted in Development, RDBMS

One Response

Subscribe to comments with RSS.

  1. cleaning a database is always essential for even the smallest of websites, thanks for the info

    Chris Vincent

    November 3, 2007 at 12:07 AM


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 260 other followers

%d bloggers like this: