Moving a table to a different schema in Oracle

Solution 1:

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.

Solution 2:

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.