Oracle: Move Table to new Schema and Tablespace

A Toad user posed this question today:

Is there a way to move a (marked) TABLE mytable123 from Tablespace AAA and Schema BBB to another Tablespace USERS and Schema MYSCH?

Let’s tackle the 2nd and easiest part first!

Moving a table or partition to a new tablespace is pretty straightforward:

ALTER TABLE table_name MOVE
TABLESPACE tablespace_name;

The ALTER TABLE MOVE clause is documented and diagrammed in the Oracle Docs.

However, the user ALSO wants to change the owner of the table.

Now how do we go about doing that?

ALTER TABLE CHANGE OWNER…no, can’t do that. Darn! For the morbidly curious, DB2 DOES make that pretty easy with TRANSFER OWNERSHIP. However, my user isn’t willing to port his application to DB2 probably, so we’re back to where we started.

Just brainstorming for a few minutes produced these ideas:

Create Table As Select

Create a copy of the table fresh in the ‘new’ SCHEMA. You can specify storage in this command, so the tablespace piece can be addressed here too. This is one of my favorite ‘tricks’, but it does have its drawbacks. It won’t grab the triggers, synonyms, and other objects associated with the table. It leaves the old table in its place. The data could change while it’s being ‘copied.’

Export + Import (Datapump)

Get a logical backup of the data and then import it into the new schema. Again, the data could change after your copy is made, and then you’ll need to drop the ‘old’ object(s) afterwards.

In my original post, I recommended a third method – using DBMS_REDEFINITION to move the table painlessly in production. Turns out that I was W-R-O-N-G.

Here’s a technical blogging tip: try testing the stuff you advocate as a solution first! I saw a colleague make the same mistake as me – he assumed that DBMS_REDEF was ‘flexible’ enough to allow this ‘moving’ of tables between schemas. If you actually read the docs, you can see they take great pains to mention that these scenarios are ALWAYS “in the same schema.” Doh!

Whatever You Do, Don’t Do it this Way!

Just so we’re clear on the matter, here is what the good folks at AskTom have to say on the subject:

that is the worst thing you can do.

You lose, game over player one.

That is the best way to totally corrupt your database.

Never, not under any circumstances — without support telling you “do this update” — will you EVER
update the data dictionary.

It is the worst thing you can do (besides not using bind variables).

Don’t do that, EVER. Period. Anytime you are tempted to update a SYS. table — stop, don’t.

It could be fun to try in a test environment though. Ha! Seriously though, don’t do it.

Now if you want to move a picnic table, that’s something that is much easier!

“Now One Person Can Move Heavy Picnic Tables!”