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.
Best Answer
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.