Oracle: How to move a table to another schema?

A client asked me, “How can I move a table to another schema in Oracle?” The quick answer I gave him is, “not possible”. You have to rebuild it via “create table as select”. You might ask, justifiably, why would you want to do that anyway? His problem was that the application has been split into 2 parts, and he wanted to have separate schemas for each part, to ensure that there is no cross-schema table access.

The way this should work is like this:

SQL> rename t1 to kutrovsky.t1;

ORA-01765: specifying table's owner name is not allowed

Oops! How could you do that without rebuilding the segments, I was wondering. And here’s what came up.

It’s not exactly rename t1 to kutrovsky.t1;, but it gets pretty close.

Let’s assume that t1 is the table we want to move. For demonstration purposes, allow me to create a simple table t1:

create table t1 ( column1 ) as select rownum from user_tables where rownum <=10;

The first step in our process is to create a range partitioned table, in the following example named t1_temp, based on the structure of our table. The name is of no importance, it is only temporary. We use any existing number, date or varchar2 column of our table for the partition key. The ranges also do not matter, since we’re not validating them.

create table t1_temp partition by range (column1)
(partition dummy values less than (-1),partition t1 values less than (MAXVALUE))
as select * from t1 where rownum <=0;

As the second step, we create the new table, which will hold the data. Note that we’re only creating the layout, no data.

create table kutrovsky.t1 as select * from t1 where rownum <=0;

And now here comes the magical third step:

alter table t1_temp exchange partition dummy with table t1 including indexes without validation;
alter table t1_temp exchange partition dummy with table kutrovsky.t1 including indexes without validation;

The first command “assigns” the data segment to the t1_temp table. The second command “assigns” the data segment to the t1 table in the new owner.

Magic? I don’t think so. Here’s how and why it works.

When you create a normal table, Oracle creates two items. The logical object (object_id) and the data segment (data_object_id). When you create a partitioned table, Oracle creates 1 logical object (the table) and multiple data segments (each partition). In a partitioned table, the logical object (the table) has no physical segment, only it’s partitions have them.

Oracle has a command to re-assign physical objects (data segments) to compatible logical objects, but it only works between a partition of a table and a non-partitioned table. When you use that command in the two step process as shown above, you essentially re-assign between two tables, by using a partitioned table to work around the limitation of the exchange command.

I took three snapshots of our objects of interest.

1. Before executing any exchanges
2. After 1st exchange
3. After 2nd exchange

Notice how the data_object_id travels “down” and gets re-assigned.

This approach also works when the table has indexes. The little detail is that you have to create the same indexes on both the temp partitioned table (as local indexes!) and the final destination table.

Those of you who have been following along attentively will know that you can now drop the t1_temp table.

Happy moving!