Wednesday, December 20, 2006

Export/Import Vs Copy command

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 SQLPLUS utility which cannot be used for any other interface.

3 comments:

lana_jana said...

Hi,
Does the copy cammand do an MD5 or a checksum validation to make sure the data has been correctly copied?

Thanks,
Sriram

Sachin said...

Copy is an Oracle command and all the data transfer happen over SQL*Net. So I don't think any MD5/checksum is involved, however SQL*Net uses TCP/IP protocol which sends ack back once the data packets are recvd.

lana_jana said...

So can we consider the Copy as a reliable command. Our client is very specific about data loss, which is why we thougt of checksum. Or is there any other oracle utility that performs teh checksum. I checked out owa_opt_lock.checksum but that only does row level checksum which is overhead for about 5 million records