SQL Server and Sybase provide a system procedure to do this job, while Oracle hasn't it. And now I receive a task to move few tables (totally 80gb) from one schema to another schema. I tested this using transportable tablespace this morning with the following steps.
In the test case, I want to move tables from schema "test1" to schema "scott", all the tables are created in tablespace "USERS01", and self-contained for tablespace transport. The first command I run is :
ALTER TABLESPACE USERS01 READ ONLY;
The second step is to do a transport tablespace export.
exp TRANSPORT_TABLESPACE=y TABLESPACES=USERS01 TRIGGERS=y CONSTRAINTS=y GRANTS=n FILE=users01.dmp
The third step is to drop the tablespace without put it back to read write mode.
drop tablespace users01 including contents;
The forth step is to import the tablespace back.
imp TRANSPORT_TABLESPACE=y TABLESPACES=USERS01 FILE=users01.dmp FROMUSER=test1 TOUSER=SCOTT DATAFILES=...
The last step is put the tablespace back to read write mode.
ALTER TABLESPACE USERS01 READ WRITE;
I did a test on local database, everything seems ok, but I still filed a Oracle Tar (6488769.994) for any possible issue.
Comments (3)
I Prefer to Use trigger or Mview to move table cross different schemas.
Posted by jametong | September 19, 2007 3:45 PM
These are busy tables, trigger or mview will add extra load on the server.
Maybe not good for us.
Posted by anysql | September 19, 2007 3:55 PM
Somebody went ahead of me by this method (AskTOM Post).
Posted by anysql | September 19, 2007 7:33 PM