5

How do I move a table from one schema to another? It's a fairly large table, so the following query would take ages to complete and need huge disk space:

CREATE TABLE newschema.mytable AS SELECT * from oldschema.mytable;

I tried to rename the table:

ALTER TABLE oldschema.mytable RENAME TO newschema.mytable;

but it seems Oracle doesn't permit that (I get an ORA-14047).

What are my options? exp/imp is probably faster than the "simple" copy, but would still need lots of free disk space.

random
  • 450
  • 1
  • 9
  • 16
Henning
  • 213
  • 1
  • 3
  • 7

4 Answers4

4

I would be shocked if there was a faster solution than the CREATE TABLE AS SELECT. Exporting and importing, whether you are using the classic version or the DataPump version, is going to require that Oracle read the entire table from disk and write the entire table to disk in the new schema, just like CTAS, but with an interim step of writing the data to the dump file and reading it from the dump file. You could get creative and try to pipe the ouput of the export utility to the import utility and have both the export and import running simultaneously to potentially avoid writing all the data to disk, but then you're just working to eliminate part of the I/O's that make export and import inherently slower. Plus, it's generally easier to parallelize a CTAS than to try to parallelize both the export and the import.

The benefit of doing export and import, on the other hand, is that you can move the constraints, indexes, and other dependent objects automatically. If you do a CTAS, you'll have to create the indexes and constraints separately after the data is populated.

Justin Cave
  • 978
  • 7
  • 11
1

Export the schema on oldschema but set rows=n so that no data is exported. Use the DDL to create the new table. Then use select into repeatedly to move chunks of data when it won't impact performance too much. When all the data is moved use the DDL to create the indexes.

Michael Dillon
  • 1,809
  • 13
  • 16
1

Assuming you're using 10g or higher, I would use Data Pump for it's efficiency. Here is a nice, concise overview of the utilities. There is no simple rename operation to do what you want to do - you're going to have to move data. I did come across this interesting method.

DCookie
  • 2,098
  • 17
  • 18
0

Here they have an answere for: How to move a table to another schema? the author creates a create a range partitioned table, then creates creating the layout for new table. Then he : 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

Its not as easy as in SQL Server where you would to move persons.Address to Humanresources by : ALTER SCHEMA HumanResources TRANSFER Person.Address;

Patrik Lindström
  • 591
  • 4
  • 5