How to move a table from one schema to another
Submitted by John Watson on Thu, 2012-07-12 06:38
articles:
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); commit; 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.
»
- John Watson's blog
- Log in to post comments
Comments
How to move a table from one schema to another
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).
Regards
San
You say "the newly copied
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.
How to move a table from one schema to another
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
You can use TOAD as application to copy tables from one schema to another schema.
Steps to copy a table
Steps to copy a table: