How to move a table from one schema to another


The other day, I was asked how to move a table from one schema to another. The answer, as we all know, is "you can't do that: you have to create a new table as a copy of the old one, or use export/import. It will take a long long time." Not true.

In fact you can move a table from one schema to another, because partition exchange DDL works across schemas. Like this:

drop user a cascade;
drop user b cascade;
grant dba to a identified by a;
grant dba to b identified by b;

create table a.t(c integer);
insert into a.t values(1);

create table b.t(c integer) partition by hash (c) (partition t1);

alter table b.t exchange partition t1 with table a.t;

And a.t is now b.t with no down time, undo, or redo.
You do need to be licensed for partitioning, of course.


Hi John,

Thanks for sharing this. Can a vice versa is also possible, that is if i update some rows in the newly copied table in b and i want to again move this info to a, so can this be done using the same integer value (c).


You say "the newly copied table". That tells me that you have missed the point, which is that nothing is copied. Do some experiments and read up on partitioning, and all will become clear.

shashanksangal's picture

Vice versa is not possible as before partitioning table b.t, table a.t should be partioned first.
As shown in example of at the time of creating the table.

You can use TOAD as application to copy tables from one schema to another schema.

Steps to copy a table:

grant dba a to identified by a;
grant dba b to identified by b;

conn a/a
create table t(a number);
insert into t values(1);
conn b/b
create table t as select * from a.t;(with in the same database)

copy from a/a@db_name(source database) to b/b@db_name(destination database) create or replace t(table_name) using select * from t(source table_nmae);--different database's