How to move a table from one schema to another

Many times I've seen the question on forums "How can I move a table from one schema to another?" and the answer is always that you can't. You have to copy it. Or export/import it. Well, here's a way. It assumes that you are on release 12.x and have the partitioning option.

My schemas are jack and jill. Create the table and segment in jack:

orclz> create table jack.t1(c1) as select 1 from dual;

Table created.

orclz>

Convert it to a partitioned table, and see what you've got:

orclz> alter table jack.t1 modify partition by hash (c1) partitions 1;

Table altered.

orclz> select segment_name,segment_type,partition_name,header_file,header_block from dba_Segments where owner='JACK';

SEGMENT_NAME                   SEGMENT_TYPE       PARTITION_NAME                 HEADER_FILE HEADER_BLOCK
------------------------------ ------------------ ------------------------------ ----------- ------------
T1                             TABLE PARTITION    SYS_P5443                               12           66

orclz>

Create an empty table (by default, no segment) for jill:

orclz> create table jill.t1 as select * from jack.t1 where 1=2;

Table created.

orclz>

And now move the segment from jack to jill:

orclz> alter table jack.t1 exchange partition sys_p5443 with table jill.t1;

Table altered.

orclz>

and now (woo-hoo!) see what we have:

orclz> select segment_name,segment_type,partition_name,header_file,header_block from dba_Segments where owner='JILL';

SEGMENT_NAME                   SEGMENT_TYPE       PARTITION_NAME                 HEADER_FILE HEADER_BLOCK
------------------------------ ------------------ ------------------------------ ----------- ------------
T1                             TABLE                                                      12           66

orclz>

It isn't only Father Christmas who can do impossible things :)

--
John Watson
Oracle Certified Master DBA
http://skillbuilders.com