Question: I need to copy a set of tables (along with their indexes and constraints) from one schema to another schema. The schemas are on different databases so perhaps by question is best posed as “how do I copy tables between databases?” I know that there are several methods for copying schema tables, but I wanted to know the best practices for moving tables between database schemas. Thank you.
Answer: Yes, you are correct there are several methods for copying tables between schemas, some direct and some indirect. If the schemas are within the same database the “create table as select” is a simple way to copy a table from one schema to another. Below we copy a table between the smith and jones schemas:
select * from smith.fred1;
Use CTAS to copy the tables, and then punch & re-add constraints and indexes using dbms_metadata. Parallelized CTAS is a faster way to copy a table.
Use the SQL*Plus copy command
When the schemas are within different databases then we have two approaches to copying table between schemas:
– Direct table copying: This requires the use of a database link
(insert into local_table select * from table@database_link; ). Here is a syntax I find very useful when it comes to creating DB links. This works even without the
TNS entry on the server.
create database link <db link name>
connect to <username>
identified by <password>
using ‘(DESCRIPTION =
(ADDRESS =(PROTOCOL = TCP)
(HOST = <your host>)
(PORT = 1521)))
(CONNECT_DATA = (SID = <your sid> )))’
– Indirect data copying: This requires using the database pump utility, a three-step
copy process. This has the advantage because you can also copy the table indexes and
constraints between the schemas:
a) use expdp to export the table(s)
b) Use FTP to move the export file to the new database server
c) Use impdp to import the tables into the new database schema
Generally, in a support environment, when DBA gets requests like refreshing a DEV/QA table from production, they use Oracle provided utilities like export/ import. In fact i was also using the same for long time, but we realized, not only it takes more time; it is more cumbersome as well.
To successfully complete the whole exercise
– You need to export table(s)
– FTP/transfer the export dump to target location (although this can be avoided by making connection from place where the dump is lying)
– Drop the table
– Import the table
Now this looks to me as multiple step process. (which is erroneous also)
Now look at COPY command. It is much easier and provides less resolution time.
Truncate table (source_table);
set arraysize 1000
set copyc 100copy from (username/pwd)@(tns_of_remote) insert (target_table) using select /*+ parallel(a,2) */ * from (source_table);
The copy command will make a connection to remote (may be prod) database and using the connection, it will populate the local table.
Sqlplus commits after each successful “copy”. And each “copy” is n (number for copyc) number of batches where arraysize represents the size of each batch.
According to the above example, SQLPLUS will perform commit after 100 (copyc – number of batches) x 1000(arraysize – size of one batch)
There are several other forms in which COPY command can be used.
You may want to read the full usage here.
Note: COPY is a SQL PLUS utility which cannot be used for any other interface.
reference:// Sachin Arora’s Blog