Although not a “new” command in SQL*Plus, it’s very cool.
Recently I had to move some data from one table to another in a different database. The Source table structure included a LONG column. In the Target Database the column was a CLOB.
Luckily for me Oracle provides a TO_LOB function. However… LONG columns and Database Links don’t mix. There must be another way…
There are restrictions to the TO_LOB function, taken from the documentation:
You can apply this function only to a LONG or LONG RAW column, and only in the SELECT list of a subquery in an INSERT statement
Of course I tried that, but like I said earlier LONG columns and Database Links don’t mix.
PL/SQL could be used, in this case you don’t need the TO_LOB function. Surprisingly Native Dynamic SQL (Execute Immediate) allows you to “Insert into CLOB select LONG from table” but straight SQL does not.
PL/SQL could be used, but the performance is poor…
If I could just get the Source Table over to the Target Database without too much fuss….
Once the table is “on the other side”, meaning in the Target Database, I could use the TO_LOB function to fill up the Target Table.
Database Link doesn’t work… PL/SQL is not a great option… EXP/IMP, I would have to bother the DBA… what about COPY? (act surprised now, like you didn’t see this coming :))
The Syntax
What does COPY do? Well, it copies… that’s obvious. Is it SQL? No, it’s SQL*Plus.
Syntax (I wish I could find a nice picture to show you):
COPY {FROM username[/password]@database_specification| TO username[/password]@database_specification| FROM username[/password]@database_specification TO username[/password]@database_specification} {APPEND|CREATE|INSERT|REPLACE} destination_table [(column, column, column …)] USING query
I think the username, password and database_specification are clear.
With the FROM and TO keywords you control the Source and Destination databases. The next command can be either APPEND, CREATE, INSERT, REPLACE
APPEND | Inserts records into the target table. If the target table doesn’t exist it’s created for you. |
CREATE | Creates a target table for you and Inserts records into it. If the target table already exists, you will receive an error. |
INSERT | Inserts records into the target table. If the target table doesn’t exist, you will receive an error. |
REPLACE | If the target table exists, it will be dropped and recreated prior to loading the table. |
USING query | Lets you specify which rows and columns you wish to copy. |
One thing you gotta keep in mind though, COPY is a one-line command. You cannot hit the “Enter” key and continue on the next line. If you do hit the “Enter” key too soon, when you’re not done typing all necessary commands, you will receive an error.
You can continue typing on the next line if you use the “continue” character, that is the hyphen (-)
I just found out, if you type COPY in SQL*Plus and nothing else, it will some you the syntax to use.
COPY is really cool!
The COPY command is great. Just to show you a little example:
copy from scott/tiger@orcl -
to tiger/scott@lcro INSERT new_emp -
(EMPNO -
,ENAME-
,SAL-
,COMM -
) -
using select EMPNO -
,ENAME -
,SAL -
,COMM -
from emp
As you can see, it’s easy to use. The syntax is pretty straightforward. And it’s fast too! I ran some test with different loads, and here are the results:
No. of Records | Elapsed Time | |
---|---|---|
2434089 | 00:39:04 | * |
2435006 | 00:18:24 | |
2476055 | 00:05:46 | |
8403 | 00:00:01 | |
8623 | 00:00:02 | |
1414956 | 00:05:24 | * |
20384678 | 01:25:17 | * |
91154 | 00:00:15 | * |
822102 | 00:02:32 | |
108550 | 00:00:22 | * |
41938 | 00:00:23 | * |
*) LONG column in the Source Table
The settings I used in SQL*Plus were: ARRAYSIZE 1000, COPYCOMMIT 10, LONG 9000. The results shown here concerned different tables with varying amount of columns.
Elapsed time included the use of the TO_LOB function described earlier to load the target tables.
All in all: COPY is COOL!
Even though the COPY command is not being updated, it’s still available in the Oracle 11g database. You can find the documentation here
JHReddy,
Maybe the best option is to use Oracle Streams, take a look at this tread on askTom . He discussed something similar to your requirement.
hi,
I have requirement to make sync of 30 tables in one database with the same 30 tables in different database on daily basis. can any one suggest me how to do it. Is Copy command the best approach or is there any other method to do it.
Shubha,
The Copy-command is SQL*Plus only. Therefore it can’t be used from Java.
Is COPY command can be execute from java programs
Good News: The rumours said COPY was removed from SQL*Plus, but in the Oracle 10g documentation COPY still exists!
A nice post was written on aMr. Ed’s weblog: http://www.edhanced.com/ask-mred/?q=oracle-blog