Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question.

Asked: March 04, 2001 - 4:31 am UTC

Last updated: July 15, 2009 - 11:10 am UTC

Version: 8.1.6

Viewed 10K+ times! This question is

You Asked

hello Mr Tom,
i have 5 tables and a stored procedure which collects certain data from all the 5 tables and dumps into a sixth table called export table.
i want to copy this export table data into a dbf file ..

so thru sqlplus i can use the copy command and it works fine...is it possible to use the same copy command from a stored procedure?
if not, is there an alternate way to do this?(like maybe i have to write a batch file?!)

also is there any limit on the length of the copy command?

suppose i use a copy command like the one below:
i get an error saying "missing comma or right paranthesis"

COPY FROM CANTEST/CANCANTEST@CANDB TO SYS/MAN@ODBC:FP INSERT CAN_EXPORT(CONFIGNAME,MSGNAME,MSGID,DLC,SIGNAME,STARTBIT,SIGSIZE,MINVAL,MAXVAL,OFFSET,FACTOR,UOM,SIGCOMMENT,MSGCOMMENT,CODEVALUES,ISMOTOROLA, ISSIGNED,ISMODE,ISMODEDEP,SIGMODE,PRIORITY,NODE1,NODE2,NODE3,NODE4, NODE5,NODE6,NODE7,NODE8,NODE9,NODE10,NODE11,NODE12,NODE13,NODE14,NODE15) USING SELECT CONFIGNAME,MSGNAME,MSGID,DLC,SIGNAME,STARTBIT,SIGSIZE, MINVAL,MAXVAL,OFFSET,FACTOR,UOM,SIGCOMMENT,MSGCOMMENT,CODEVALUES,ISMOTOROLA,ISSIGNED,ISMODE,ISMODEDEP,SIGMODE,PRIORITY,NODE1,NODE2,NODE3,NODE4,NODE5,NODE6,NODE7,NODE8,NODE9,NODE10,NODE11,NODE12,NODE13,NODE14,NODE15,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL FROM CAN_EXPORT;

so i try to spool out the results into a text file

and i see only half of the copy command in the text file which is:

SQL> COPY FROM CANTEST/CANCANTEST@CANDB TO SYS/MAN@ODBC:FP INSERT CAN_EXPORT(CONFIGNAME,MSGNAME,MSGID,DLC,SIGNAME,STARTBIT,SIGSIZE,MINVAL,MAXVAL,OFFSET,FACTOR,UOM,SIGCOMMENT,MSGCOMMENT,CODEVALUES,ISMOTOROLA, ISSIGNED,ISMODE,ISMODEDEP,SIGMODE,PRIORITY,NODE1,NODE
missing comma or right parenthesis
SQL> SPOOL OFF

is there a work around for this ?
please help.
basically i want to export the data in the table into a dbase dbf file..is there any other way to do this?

thankyou,




and Tom said...

the COPY command is a sqlplus command only. It is facilitated by the fact that sqlplus can connect to different databases simultaneously. PLSQL cannot.

If you want to use the COPY command to dbase, you will be using SQLPLUS as that can use ODBC and open a separate connection to ODBC.

As for the line length limit -- yes there is one, and it is about 240 character per line. You would use the line continuation character to get around that:


ops$tkyte@ORA8I.WORLD> copy from scott/tiger@ora8i.us.oracle.com -
> insert foo -
> (the_whole_view, textlength, thetext ) -
> using -
> select view_name, text_length, text -
> from user_views;

Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 5000. (long is 5000)
7 rows selected from scott@ora8i.us.oracle.com.
7 rows inserted into FOO.
7 rows committed into FOO at DEFAULT HOST connection.

ops$tkyte@ORA8I.WORLD>


the script I used was:


copy from scott/tiger@ora8i.us.oracle.com -
insert foo -
(the_whole_view, textlength, thetext ) -
using -
select view_name, text_length, text -
from user_views;

the "-" is a line continuation character. You might have seen the line continuation character pop up if you ever hit the ENTER key after a minus sign:

ops$tkyte@ORA8I.WORLD> select 1-
> 1 from dual;
select 1 1 from dual
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

you might think that should return 0, but it does not. It can be even more confusing if the resulting SQL is valid but not what you wanted:

ops$tkyte@ORA8I.WORLD> select 1-
> dummy from dual;

DUMMY
----------
1

ops$tkyte@ORA8I.WORLD>




Rating

  (81 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, April 23, 2001 - 8:28 am UTC

The answer was extremely helpful.

Copy comand where maximum characters is exceeded

Jiten Padhiar, February 05, 2002 - 12:12 pm UTC

I am trying to use the COPY command and find that the statement is truncated. As part of the 'USING SELECT..' clause i have to list all 150 columns. This is because I have to assign the appropriate column alias before the statement applies an insert from an oracle table to a SQL Server table.

Any thoughts (ps. I have used the continuation character)


Tom Kyte
February 05, 2002 - 6:28 pm UTC

create a view that does the correct conversion of column names on the SQL server table and coyp the view. Then the sqlplus copy command will be very short.

Good tip

Tony, June 04, 2002 - 10:13 am UTC

Tom:

Does the COPY command return an sucess/error code when it is done? What I would like to do is to copy all data from the source to the sink. If the process completes without error, I want to update the status code of the source table so that the script won't pick those records again. Following is a short UNIX shell script:
--------------------------------------------------------
#!/bin/sh

echo "Starting the update process: " `date`

sqlplus -s user/password@sink_db <<EOF

COPY FROM user/pass@source_db INSERT sink_table -
USING SELECT * FROM source_table WHERE status_code = 0

-- If NO error Then
-- UPDATE source_table SET status_code = 1
-- End If

exit

EOF

echo "Finished the update process: " `date`
--------------------------------------------------------

Can I do this? I would appreciate your help.

Thanks,
Tony

Tom Kyte
June 04, 2002 - 9:24 pm UTC

See
</code> http://docs.oracle.com/docs/cd/A87860_01/doc/server.817/a82950/appa.htm#623300 <code>
That is an appendix titled "COPY Command Messages and Codes"...

At the very least, you can "grep" for CPY0 to see if it errored out.

Well I'll be!

DBA, June 05, 2002 - 1:34 am UTC

Like the answer Tom,
didn't know about the continuation character!



copy

mo, November 06, 2002 - 11:26 am UTC

Tom:

I am trying the copy in SQL*plus for windows accessing a unix server and I amgetting an error.

IPST> copy from ipsadmin/ipsadmin@ipsd create test using select * from test;

Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)

ERROR:
ORA--10710048: Message -10710048 not found; product=RDBMS; facility=ORA

IPST> select * from test2@ipsd;

SSN PANTS
---------- ----------
611 RED
456 YELLOW

2 rows selected.

Do you know why?

Thanks,

Tom Kyte
November 06, 2002 - 3:26 pm UTC

try pushing instead -- if I get a problem one way, i always try the other

connect to ipsd and then

copy TO ....




copy

mo, November 06, 2002 - 3:47 pm UTC

Tom:

I did:

IPSD> copy to imsadmin/imsadmin@imst create test using select * from test;

Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 5000. (long is 5000)

select * from test
*
Error in SELECT statement: ORA--9715932: Message -9715932 not found; product=RDBMS; facility=ORA

2. Since the COPY does not copy any table constraints, and if you use the REPLACE option it says they drop table in destination which means I will lose constraints and triggers. IS this correct?

Thank you,

Tom Kyte
November 06, 2002 - 4:47 pm UTC

ok -- i've seen this with that goofy "gui" sqlplus -- use the real one, on the command line -- from a DOS window.

2) correct. if you don't want that, truncate and then copy.

copy

mo, November 06, 2002 - 7:32 pm UTC

do you mean copy using insert rather than replace after truncating table

Tom Kyte
November 06, 2002 - 7:33 pm UTC

yes

copy

mo, November 07, 2002 - 8:16 am UTC

Tom:

why do you call sqlplus for windows 8.1.7 goofy. is not supposed to be the same one run in unix. this is what i use.

C:\OraHome1\BIN\SQLPLUSW.EXE

It is much easier to use windows notepad than unix vi for
queries.

Thanks,

Tom Kyte
November 07, 2002 - 10:30 am UTC

sqlplus.exe is the one I used to use when I ran windows. (good old character mode)

and sorry but when I typed edit on windoze in plus -- it would fire up VI. To say notepad is "easier" then vi -- hmm, sure if "trivial" is what you wanted as well. Vi is a little more powerful perhaps then notepad. I'll stick with vi anyday.

Copy and Insert /* +Append */ ?

Robert, January 25, 2003 - 7:33 pm UTC

Can I

Insert /* +Append */ , or even Insert /* +Append */ ...NOLOGGING

with the copy command ?

Tom Kyte
January 25, 2003 - 7:35 pm UTC

copy command = conventional path (no direct path) loading.

COPY command obsolete?

Arun Gupta, May 27, 2003 - 9:39 pm UTC

Tom
In the Oracle 9i SQL*plus manual, it says that COPY command will become obsolete in future release. What is the alternative if I want to insert from one database to another without using database link?
Thanks


Tom Kyte
May 28, 2003 - 6:46 am UTC

exp/imp i guess.

but -- if you can copy, you can dblink it as well


(but the manuals have been saying that about copy for a while...)

if you can copy, you can dblink it as well?

kassim, May 28, 2003 - 3:11 pm UTC

hi tom --
as of 9.2 i can no longer dblink to tables in a 7.3.4 instance ( icould do it in 9.01 and was able to take advantage of materialized views)
now i'm reduced to

connect to the 9.2 instance
truncate <table> resuse storage
drop indexes

run a script that resets env vars to use the 9.01 client
connect to the 7.3.4 instance
copy to <9.2 instance> insert <tablename> using <select statement>

reconnect to 9.2
build indexes


i do this as a cron job but would to learn a better way

thank you for any suggestions




Tom Kyte
May 28, 2003 - 7:48 pm UTC

you are lucky that even works -- there are many known issues with 73 clients connecting to 92 and 92 clients connecting to 73. that is very fragile what you have there as it is.

gosh

kassim, May 28, 2003 - 9:35 pm UTC

thanks--
i'm none too happy with this either
the people who maintain the 7.3.4 instance are not likely to upgrade soon as there are a bunch of legacy apps that depend on it

aside from creating a new 9.01 instance just to hold dblinks or materialized views pointing to the tables in the 7.3.4 instance, what would you recommend?

can you send me link about the 7.3 <--> 9.2 issues?

should this be a new thread?

thanks again



Tom Kyte
May 29, 2003 - 7:31 am UTC

metalink has the information about 73 to 92 and its being explicitly "not supported, and not working"

I can't really recommend anything here short of getting 73 upto a release that is supported.

a related question on copy command

Moh, May 29, 2003 - 5:30 am UTC

We have oracle 7.0 and oracle 8i 815 on the same machine.
Now, porting oracle 7.0 data to oracle 8i 815 is no problem at all for us. however, porting oracle8i data to oralcle 7.0 has become tough for us. we use sqlload to load data to ora7. Copy command can be used for this purpose I suppose. But, we have oracle 8i listener running on port 1521. And, no listeners for oracle7.0 . Kindly suggest us a way-out.

Tom Kyte
May 29, 2003 - 8:20 am UTC

7.0?

anyway, the 8i listener can be used for 7.x -- just configure the proper oracle home and sid and such in there. You only need one listener for all databases.

but really, if you connect to 7.0 and initiate the copy from there, you don't need a listener for 7.x, you are already connected.

copy command

Moh, May 30, 2003 - 6:32 am UTC

I use copy command from unix system.

The oracle_sid and oracle_home for oracle 7.0 and oracle 8i 815 are different.

I logged in as oracle 7.0 and tried "copy from" after getting connected.

I got this error :

ora-12203: TNS:Unable to connect to destination.

I assure you that the listener for oracle 8i is running.

Kindly help.


Tom Kyte
May 30, 2003 - 8:19 am UTC

that just means you messed up the tnsnames.ora file

[tkyte@tkyte-pc Desktop]$ oerr ora 12203
12203, 00000, "TNS:unable to connect to destination"
// *Cause: Invalid TNS address supplied or destination is not listening.
// This error can also occur because of underlying network transport
// problems.
// *Action: Verify that the service name you entered on the command line
// was correct. Ensure that the listener is running at the remote node and
// that the ADDRESS parameters specified in TNSNAMES.ORA are correct.
// Finally, check that all Interchanges needed to make the connection are
// up and running.


very useful

Bipin G, July 24, 2003 - 8:58 am UTC

Hi tom ,
I gone thro' complete copy command uses. Nowadays i'm extensively using copy command for getting the data in other db Now, I facing one problem one of my table has got long raw datatype bcos of which it is giving me inconsistent datatype how can i resolve ?
and use copy command for copy such stru.
Imp. My long raw datatype column stores the images.

Tom Kyte
July 24, 2003 - 9:30 am UTC

you cannot use copy on long raws -- period, no workarounds, nothing.

you should be using BLOBS

Dave, July 24, 2003 - 10:22 am UTC

I'm saddened to learn from Metalink Note 171870.1 that the COPY command is to be obsoleted in future SQL*Plus releases, presumably because it doesn't support funky new data types.

Does this mean that it will actually not be available, or that it will continue to exist with no added functionality?

Tom Kyte
July 24, 2003 - 3:55 pm UTC

well, i can say it is there at least in the next release after 9iR2...

you would have to ask support for a clarification (i use that feature heavily myself)

Helpful but I get an error

LB, October 24, 2003 - 11:47 am UTC

What is the meaning of this error?

SQL> COPY FROM uet_read/password@CSCD001 INSERT SLA_LB USING SELECT * FROM UETADMIN.SLA;

Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)

ERROR:
ORA--6871402: Message -6871402 not found;  product=RDBMS; facility=ORA 

Tom Kyte
October 24, 2003 - 12:29 pm UTC

means time to call support.

but you might try going the other way. COPY TO (thats what I do when i hit an issue "pulling", i connect to the other database and try "pushing")

your response time is great

LB, October 24, 2003 - 12:49 pm UTC


another error - what is wrong?

LB, October 24, 2003 - 12:55 pm UTC

I tried the copy to from the other database and I get a similar error.  Should I contact the DBA?

SQL> COPY TO uetadmin/password@SHROLTDB INSERT SLA_LB USING SELECT * FROM UETADMIN.SLA;

Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)

 SELECT * FROM UETADMIN.SLA
*
Error in SELECT statement: ORA--33781842: Message -33781842 not found;  product=RDBMS; facility=ORA
 

Tom Kyte
October 24, 2003 - 1:00 pm UTC

yup, time for a tar.

use command line sqlplus

LB, October 28, 2003 - 12:01 pm UTC

I was able to get this to work by putting the copy command into an sql file and executing it from command line sqlplus.

beginner question

LB, October 30, 2003 - 9:29 am UTC

This has been working for me when I was using a small dataset like 7000 records. But then when I tried about 30,000 reocrds or more I keep getting the error "failed to extend rollback segemnt"

I suspect it is something to do with the arraysize or copycommit settings, but I am not sure what to change.
Sorry to ask such a beginner question for a site full of DBA's but what am I doing wrong with the arraysize and copycommit?

I should also mention that I have been running sqlplus from my pc since I am just a developer and do not have access to sqlplus on the database server as of yet.

set arraysize 100
set copycommit 1000
COPY FROM uet_read/password@PRODUCTION -
INSERT UET_DATA -
USING -
SELECT * -
FROM UETADMIN.UET_DATA -
WHERE trunc(req_time) > '26-oct-03' -
AND BRN_I LIKE '%HK%'



Tom Kyte
October 30, 2003 - 11:07 am UTC

don't feel you need to be "super experienced" to ask stuff. the only way to get that way is -- well -- to ask stuff!

This is a non-trivial question actually.


copycommit is how many fetches of N rows to perform between commits.

so, you are committing every 100,000 rows -- which, if that is blowing out your RBS, i would state "your DBA's need to rethink some things".


So, your choices are

a) use a smaller copycommit. make sure you can RESTART in the event of failure!! (eg if 2 fetches succeed, you will have committed 200 rows -- if the system fails at that point, you will still have those 200 rows. Now what?)

b) get them to size rbs as it SHOULD be


other comments

o it takes AT LEAST 4 characters to accurate represent a YEAR. 2003, not 03, never 03, ugh -- 1999 should have taught us that!!!!

o never compare a string to a date, a date to a string, a number to a string, a string to a number and so on.

to_date( '26-oct-2003', 'dd-mon-yyyy' )

o use explicit date formats when you have an explicit date like that. avoids issues with databases that have non default NLS_DATE_FORMATS

o avoid the use of a function on a column. Here the trunc(req_time) is actually not even needed!!! if the trunc(req_time) is greater then your date, then req_time by definition is greater then your date. You might be missing out on a useful index range scan on the remote system (or not, but the trunc isn't needed)


COPY command Vs Database link

Saminathan Seerangan, November 14, 2003 - 5:45 pm UTC

Dear Tom,
I used both COPY command and database link. But I would like to know

1)How internally these two are different from each other

2)How COPY command is fater than dblink?

3)Is there any reason to obsolete COPY command? Most of the time I find very useful command to copy data fom diff database.

Thanks Tom,
-Sami



Tom Kyte
November 15, 2003 - 8:57 am UTC

dblink goes db to db

copy goes db1 -> client -> db2 array by array.


so, say you have two databases connected by a LAN. You dial up from home and run sqlplus on your home computer.

using sqlplus copy -- every row will go from db1 to YOU to db2

using dblink -- every row will go from db1 to db2 over the lan


copy -- commits after every N array fetches
dblink -- commits when you say "commit"

No EXPort privilege but huge amount of data should be transferred

Saminathan Seerangan, November 15, 2003 - 12:53 pm UTC

Thanks a lot Tom.
So if I want to transfer bulk volume of data, then DBLINK should be the best choice(in terms of data transfer speed compare to COPY) provided larger rollback segment and assume that I do not have EXPort privilege on the source database.


Tom Kyte
November 16, 2003 - 10:29 am UTC

depends, all depends.

benchmarking is always appropriate -- try both.

you don't need "export" priv on a database??? if you can read the data, you can export the data.

if you can read the data, you can export the data?????

Saminathan Seerangan, November 16, 2003 - 11:25 am UTC

<quote>
you don't need "export" priv on a database???  if you can read the data, you can 
export the data. 
</quote>

Dera Tom, Please see below, what is wrong here?
I am getting "EXP-00009: no privilege to export testschema's table test_table"

$ exp userid=tomkyte tables=testschema.test_table
 
Export: Release 8.1.7.3.0 - Production on Sun Nov 16 11:19:31 2003
 
(c) Copyright 2000 Oracle Corporation.  All rights reserved.
 
Password: 
 
Connected to: Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Partitioning and Parallel Server options
JServer Release 8.1.7.3.0 - Production
Export done in UTF8 character set and UTF8 NCHAR character set
 
About to export specified tables via Conventional Path ...
EXP-00009: no privilege to export testschema's table test_table
Export terminated successfully with warnings.
 
myserver tomkyte mydb /opt/home/tomkyte


$ sqlplus tomkyte
 
SQL*Plus: Release 8.1.7.0.0 - Production on Sun Nov 16 11:19:53 2003
 
(c) Copyright 2000 Oracle Corporation.  All rights reserved.
 
Enter password: 
 
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Partitioning and Parallel Server options
JServer Release 8.1.7.3.0 - Production
 
SQL> select * from testschema.test_table where rownum <=2;
 
TYPE       CODE
---------- ----------
DESCRIPTION
--------------------------------------------------------------------------------
A CREATEDATE      UPDATEDATE
- --------------- ---------------
TWC               101
Payroll
Y 12-SEP-03       12-SEP-03
 
TWC               199
Other Payroll
Y 12-SEP-03       12-SEP-03
 
 
SQL> 
 

Tom Kyte
November 16, 2003 - 11:59 am UTC

doh! sorry about that, you are 100% correct. you need EXP_FULL_DATABASE, sorry about that. exp as THAT user won't work -- but exp AS that user would work (so exp is still an "option", if that is the best approach, that source system should facilitate that approach)

Dear Tom,thanks for your immediate response.

Saminathan Seerangan, November 16, 2003 - 2:34 pm UTC


Using Net8 listener vs direct connection to database

A reader, January 22, 2004 - 2:44 pm UTC

Tom,

You mentioned in this discussion that the COPY command will go from db1->client->db2. Assuming that I logged on directly to the UNIX machine running db1.

1. What is the difference between
sqlplus userid/pwd
and
sqlplus userid/pwd@some_connect_string

2. How will COPY work in each of the cases below? Will the two cases be same as using db link (db1->db2) since I logged directly onto machine running the Oracle database db1.

a. sqlplus userid/pwd (assuming ORACLE_SID=db1 has been set)
and use COPY ...@db1 TO ...@db2 SELECT ....

b. Connecting to db1 via net8 (listener) using
sqlplus userid/pwd@db1 and use
COPY ...@db1 TO ...@db2 SELECT ....

Thanks

Tom Kyte
January 22, 2004 - 8:02 pm UTC

1) one uses a fork/exec to get the dedicated server started.

the other asks the tnslistener to fork/exec the dedicated server.

it'll not really have an effect on the copy (beyond one might use IPC instead of TCP but thats a protocol change)

2) you left out "where" db1, db2 and sqlplus are running. without that, one cannot answer.

say db1 on server1.
say db2 on server2.
say db1 and db2 are right next too eachother on a really fast network.

say you dial into your system and run sqlplus scott/tiger@db1 locally.

Now, everything from db1 will be sent down the telephone to your sqlplus and back up the telephone to db2.

that will probably be slower than a dblink between db1 and db2.

COPY command

A reader, January 23, 2004 - 9:46 am UTC

Tom,

In the above question, I am sorry that I missed to mention where db1, db2 and sqlplus are. Assuming that db1 and sqlplus are in server1 and db2 is on server2.

1. I connect to server1 directly (not using telnet) and
A. Use sqlplus usr/pwd@db1 by asking the tnslistener to do a fork and do a COPY to db2. What happens in this case (data flow).

B. Use sqlplus usr/pwd and do a COPY to db2. What happens in this case (data flow)

Tom Kyte
January 23, 2004 - 6:22 pm UTC

the flow is always the same.

data comes from db to sqlplus to db

database -> sqlplus client -> database

for copy..

same flow in every every case.

Any other options

Ashwin N., April 19, 2004 - 3:19 am UTC

Hi Tom

Okay, now so I have about 100 database objects which includes tables, indexes, stored programs etc in my production database

I have taken export of the database and did an import to load in my local db.
But I need to keep refreshing my local db every week.

What is the best way to do it ?
Does a copy command refesh the whole database ?
Do I need to a write a script and use db link to populate my local database everytime.
Any other ways to do this ?


Thanks
Ashwin N.

Tom Kyte
April 19, 2004 - 6:52 am UTC

you could backup prod and restore to test -- that'd be a great way to know you can actually restore.... it would be the least impact, fastest method.


exp/imp can work (transporting even -- no need to rebuild, just copy datafiles basically and import the meta data)

copy, as documented, works command by command, table by table. you would have 100 copy commands.


copy does not use dblinks, but SQL could use a dblink (insert into local_table select * from table@remote_site)


so, you have:

o backup and restore (my personal favorite, tests the restore process!)
o exp/imp
o transportable tablespaces
o sqlplus copy command
o dblinks using sql



Copy Over DB Link

MM, June 10, 2004 - 11:09 am UTC

Tom,
I have this situation ..
I have a DB LINK between the source db and target db. I need to move long column from source to target. So I use copy command. In the where clause, I need to select only those rows that do not exist in target.
copy from source@connect_str TO target@connect_str INSERT-
table1 (col1, col2, col3) USING-
SELECT col1, col2, col3 from table1
where (col1) not in (select col1 from table1@target);
Table1 exists both in source and target database. How do I distinguish between the source and target table name in the sub query? If I just use table1 in the subquery it is always resolved as table1 at source. But I need to look at table1 in target. If I user table1@target I am getting a invalid username/password error. Please help.

Thank you

Tom Kyte
June 10, 2004 - 5:17 pm UTC

you cannot -- copy does this:

opens a connection to "source"
opens a connection to "target"

prepares a query on "source", array fetches from it, inserts into the "target" connection

eg: it is *not* using dblinks at all, it is using two connections from itself to source and target.


So, source would have to have a database link setup such that if you logged into source (connect u/p@source) and issued the query "select * from t where col1 not in (select col1....)" it would work.

I see a not in, make sure either:

o you definitely use the COB
o convert it into not exists

else the RBO will full scan for each row in the outer query.

Copy from 8.0.5 to 8.1.7

Dave, June 22, 2004 - 9:18 am UTC

Hi Tom 

Does the COPY command work across different Oracle releases? I am using the dos based sql*plus tool and get the following error folowing

SQL> COPY FROM d_int/pwd@trace_dev TO ds/spwg@cld817 -
> INSERT f_extract -
> (IA_ID) -
> using select -
>  IA_ID from ia_extract

Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)

select   IA_ID from ia_extract
*
Error in SELECT statement: ORA-01002: fetch out of sequence
 

Tom Kyte
June 22, 2004 - 9:47 am UTC

when and if I get that -- i reverse directions.


copy to x from y

give that a whirl.

Maybe a bug?

Oskar, June 22, 2004 - 11:08 am UTC

Take a look at this note. </code> http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=644413.8 <code>

You might be hitting a bug.


Copy from a different user !

a reader, November 02, 2004 - 5:37 pm UTC

Scenario:

Database1: has users A and B. B has SELECT/INSERT/UPDATE/DELETE privileges on all tables of User A.

Database 2: has users A and B. B has SELECT/INSERT/UPDATE/DELETE privileges on all tables of User A.


Now, I try to copy data from A@database1 (using B's login credentials on database1) and insert to A@database2 (using B's login credentails on database2)

I am getting the following error:

copy from b/b@database1 to b/b@database2 -
insert A.table -
using select * from A.table
/


Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)

ERROR:
ORA-00928: missing SELECT keyword



Is this possible ? Do you need to be the OWNER of the table to execute this command ?

Am I missing anything here? Any help would be appreciated.

Tom Kyte
November 03, 2004 - 6:41 am UTC

should not be a problem -- try it the "other direction" copy to instead of from.

Strange Problem

Anirudh, November 22, 2004 - 4:41 pm UTC

Hi Tom ,

While running copy command fromn my UNIX box sqlplus client I encounter following error. The cause of which is really out of my grasps. Please adivce.

SQL> copy from hepm_admin/redbrick@hemdaprd to hepm_work/hepmwork@hemdaprd -
> insert prod_doc_au_bak -
> (id_prod) -
> using -
> select id_prod from product_document_au where rownum=1;

Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)

CPY0008: More column list names than columns in destination table

THEN TRIED IT THE OTHER WAY!! BUT IN VAIN...

SQL> CONNECT HEPM_ADMIN/REDBRICK@HEMDAPRD
Connected.
SQL> copy to hepm_work/hepmwork@hemdaprd -
> insert prod_doc_au_bak -
> (id_prod) -
> using -
> select id_prod from product_document_au where rownum=1;

Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)

CPY0006: Select list has more columns than destination table
 

Tom Kyte
November 22, 2004 - 5:32 pm UTC

versions of all involved (sqlplus and both databases)

describes of both tables?

Copy Command !

Reader, December 07, 2004 - 6:31 pm UTC

Tom...

Sorry was away on a vacation. Am following it up with your suggestion. Both
push and pull didnot work.


ORCL Database version:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
PL/SQL Release 9.2.0.5.0 - Production
CORE        9.2.0.6.0        Production
TNS for Solaris: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production



ORCL1 Database version:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
PL/SQL Release 9.2.0.1.0 - Production
CORE        9.2.0.1.0        Production
TNS for Solaris: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

SQL> conn a/a@orcl
Connected.
SQL> desc b.test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PRODUCT_CD                                NOT NULL VARCHAR2(6)
 PORTFOLIO_ID                              NOT NULL VARCHAR2(20)
 PORTFOLIO_SHORT_NM                                 VARCHAR2(20)
 PORTFOLIO_LONG_NM                                  VARCHAR2(40)
 DESCRIPTION_TX                                     VARCHAR2(256)
 PF_MANAGER_CD                                      VARCHAR2(6)
 BENCHMARK_PORTFOLIO_ID                             VARCHAR2(10)
 PORTFOLIO_TYPE                                     VARCHAR2(2)
 DISPLAY_SEQ_NB                                     NUMBER(2)
 ASOF_DT                                            DATE
 PORTFOLIO_CATEGORY_CD                              VARCHAR2(20)
 PORTFOLIO_LOCATION                                 VARCHAR2(256)
 ENV_LOCATION                                       VARCHAR2(256)
 ENV_PORTFOLIO                                      VARCHAR2(20)
 NEXUS_DESCRIPTION                                  VARCHAR2(256)
 PERFORMANCE_ACCOUNT_ID                             VARCHAR2(62)

SQL> conn a/a@orcl1
Connected.
SQL> desc b.test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PRODUCT_CD                                NOT NULL VARCHAR2(6)
 PORTFOLIO_ID                              NOT NULL VARCHAR2(20)
 PORTFOLIO_SHORT_NM                                 VARCHAR2(20)
 PORTFOLIO_LONG_NM                                  VARCHAR2(40)
 DESCRIPTION_TX                                     VARCHAR2(256)
 PF_MANAGER_CD                                      VARCHAR2(6)
 BENCHMARK_PORTFOLIO_ID                             VARCHAR2(10)
 PORTFOLIO_TYPE                                     VARCHAR2(2)
 DISPLAY_SEQ_NB                                     NUMBER(2)
 ASOF_DT                                            DATE
 PORTFOLIO_CATEGORY_CD                              VARCHAR2(20)
 PORTFOLIO_LOCATION                                 VARCHAR2(256)
 ENV_LOCATION                                       VARCHAR2(256)
 ENV_PORTFOLIO                                      VARCHAR2(20)
 NEXUS_DESCRIPTION                                  VARCHAR2(256)
 PERFORMANCE_ACCOUNT_ID                             VARCHAR2(62)
 
 Trying to PUSH to b.test@ORCL

SQL> copy from a/a@orcl1 to a/a@orcl  - 
> insert b.test -
> using - 
> select * from b.test where product_cd = 'CNDIV' and portfolio_id = 'TEST'

Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)

ERROR:
ORA-00928: missing SELECT keyword

SQL> copy to a/a@orcl  -
> insert b.test -
>  using -
> select * from b.test where product_cd = 'CNDIV' and portfolio_id = 'TEST'

Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)

CPY0006: Select list has more columns than destination table

Now, I am trying to PULL from b.test@ORCL1

SQL> conn a/a@orcl
Connected.

SQL> copy from a/a@orcl1 -
>  insert b.test -
> using - 
> select * from b.test where product_cd = 'CNDIV' and portfolio_id = 'TEST'

Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)

CPY0007: Select list has fewer columns than destination table


Both tables are identical. User a on both databases ORCL and ORCL1 has
INSERT/SELECT/UPDATE/DELETE privileges on table b.test.

Please help !




 

Tom Kyte
December 08, 2004 - 9:54 am UTC

just noticed -- same database? no longs? don't use copy, just insert into select * from

Copy Command !

Reader, December 08, 2004 - 10:10 am UTC

Tom...

I am using two databases. ORCL and ORCL1. Please let me know if you have any questions.

Thanks !

Tom Kyte
December 08, 2004 - 10:52 am UTC

use a dblink, insert into t select * from t@remote



Copy Command !

reader, December 08, 2004 - 11:00 am UTC

We have a restriction to have DB links to/from production database. Also, I cannot use Export-import - no privileges. That is the reason I was trying to use Copy command.

Is is a bad approach to use Copy command ? Also, like you suggested I tried both "Push" and "Pull" - didnot work.

Your help is very appreciated !

Tom Kyte
December 09, 2004 - 12:29 pm UTC

well, your copy should look like this:

</code> http://asktom.oracle.com/Misc/MoveLongs.html <code>

you either copy FROM
or you copy TO

you seem to be doing both at the same time for some cases.


also, instead of "*" and no list of column names in the insert, LIST the column names explicitly

Copy command !

reader, December 08, 2004 - 11:20 am UTC

We cannot use DB links/export-import - no privileges. If copy is not the suggested method, is there anyother way to copy data from one database to another database ?

Your help would be very appreciated.

Thanks !

Tom Kyte
December 09, 2004 - 12:35 pm UTC

Copy Command !

reader, December 08, 2004 - 4:06 pm UTC

Tom..

Before exploring other options, can you help me understand why the following copy command didnot work. Please note that we have a restriction to use DBLink and Export-import for this.

Objective: To copy data from b.test@ORCL1 to b.test@ORCL
Databases: ORCL and ORCL1
Table Owner: b@ORCL and b@ORCL1
Logged in as: a@ORCL and a@ORCL1
Comments: a has SELECT/INSERT/UPDATE/DELETE privileges on b.test.
Constraints: Cannot use DB link or Export/import


ORCL Database version:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
PL/SQL Release 9.2.0.5.0 - Production
CORE        9.2.0.6.0        Production
TNS for Solaris: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production



ORCL1 Database version:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
PL/SQL Release 9.2.0.1.0 - Production
CORE        9.2.0.1.0        Production
TNS for Solaris: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

SQL> conn a/a@orcl
Connected.
SQL> desc b.test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PRODUCT_CD                                NOT NULL VARCHAR2(6)
 PORTFOLIO_ID                              NOT NULL VARCHAR2(20)
 PORTFOLIO_SHORT_NM                                 VARCHAR2(20)
 PORTFOLIO_LONG_NM                                  VARCHAR2(40)
 DESCRIPTION_TX                                     VARCHAR2(256)
 PF_MANAGER_CD                                      VARCHAR2(6)
 BENCHMARK_PORTFOLIO_ID                             VARCHAR2(10)
 PORTFOLIO_TYPE                                     VARCHAR2(2)
 DISPLAY_SEQ_NB                                     NUMBER(2)
 ASOF_DT                                            DATE
 PORTFOLIO_CATEGORY_CD                              VARCHAR2(20)
 PORTFOLIO_LOCATION                                 VARCHAR2(256)
 ENV_LOCATION                                       VARCHAR2(256)
 ENV_PORTFOLIO                                      VARCHAR2(20)
 NEXUS_DESCRIPTION                                  VARCHAR2(256)
 PERFORMANCE_ACCOUNT_ID                             VARCHAR2(62)

SQL> conn a/a@orcl1
Connected.
SQL> desc b.test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PRODUCT_CD                                NOT NULL VARCHAR2(6)
 PORTFOLIO_ID                              NOT NULL VARCHAR2(20)
 PORTFOLIO_SHORT_NM                                 VARCHAR2(20)
 PORTFOLIO_LONG_NM                                  VARCHAR2(40)
 DESCRIPTION_TX                                     VARCHAR2(256)
 PF_MANAGER_CD                                      VARCHAR2(6)
 BENCHMARK_PORTFOLIO_ID                             VARCHAR2(10)
 PORTFOLIO_TYPE                                     VARCHAR2(2)
 DISPLAY_SEQ_NB                                     NUMBER(2)
 ASOF_DT                                            DATE
 PORTFOLIO_CATEGORY_CD                              VARCHAR2(20)
 PORTFOLIO_LOCATION                                 VARCHAR2(256)
 ENV_LOCATION                                       VARCHAR2(256)
 ENV_PORTFOLIO                                      VARCHAR2(20)
 NEXUS_DESCRIPTION                                  VARCHAR2(256)
 PERFORMANCE_ACCOUNT_ID                             VARCHAR2(62)
 
 Trying to PUSH to b.test@ORCL

SQL> copy from a/a@orcl1 to a/a@orcl  - 
> insert b.test -
> using - 
> select * from b.test where product_cd = 'CNDIV' and portfolio_id = 'TEST'

Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)

ERROR:
ORA-00928: missing SELECT keyword

SQL> copy to a/a@orcl  -
> insert b.test -
>  using -
> select * from b.test where product_cd = 'CNDIV' and portfolio_id = 'TEST'

Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)

CPY0006: Select list has more columns than destination table

Now, I am trying to PULL from b.test@ORCL1

SQL> conn a/a@orcl
Connected.

SQL> copy from a/a@orcl1 -
>  insert b.test -
> using - 
> select * from b.test where product_cd = 'CNDIV' and portfolio_id = 'TEST'

Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)

CPY0007: Select list has fewer columns than destination table


Both tables are identical. User a on both databases ORCL and ORCL1 has
INSERT/SELECT/UPDATE/DELETE privileges on table b.test.

Thanks for your time !




 


 

Tom Kyte
December 09, 2004 - 12:48 pm UTC

you can work it with support if you like -- try my advice or other options if you want to get it going right now -- that is my goal, lets get the job done.

why the copy command not work properly in forms

ghazanfar, December 12, 2004 - 2:06 pm UTC

i have two number items one capturing hours and other minutes when the user navigate through any of the above mentioned field i used copy command to put the combine value of minute and hours in other field of date datatype
on insert it work properly but on update it saves null in the database what should i do

Tom Kyte
December 12, 2004 - 7:33 pm UTC

sorry -- don't even know what the "copy" command is in forms -- try otn.oracle.com -> discussion forums.

How to optimize COPY command

S.Kapidzic, December 26, 2004 - 5:38 pm UTC

Hi Tom,

I read Your article and it was very usefull for me. I have another problem. We have to migrate a database containing LONG's in 2 tables. We use SQL PLUS COPY command because this is the only workarround to do this (converting LONG to CLOB is not the option). COPY command takes 58 min to copy about 2,2 milion records from table A to table B within the same database. Could we do this faster? We use COPYCOMMIT : 6 and default ARRAYSIZE value (15). Changing COPYCOMMIT to 1 and ARRAYSIZE to 100 (with max of 150) we can gain some time, but it is not remarkable (2-3 min). Tried to test with ARRAYSIZE > 150 and sqlplus simply exits to MS DOS prompt and that is the end of story. Any suggestion how to speed up this proces?





Tom Kyte
December 26, 2004 - 6:00 pm UTC

<quote>
converting LONG to CLOB
is not the option
</quote>

"not an option" -- three of my most despised words. of course it is, it is a rather a road you have made a concious decision to not take.


parallelize it, you could use DIY paralellism.
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:865627341240#18289626556036 <code>

or just split it by primary key ranges (rowids will be preferred -- no index range scans, non-overlapping IO)


the more frequently you commit, the slower it'll be going. everytime you commit, you wait.

Another data transfer question...

denni50, January 20, 2005 - 9:44 am UTC

Tom

I asked a question on another thread about a data transfer
solution from prod db and test db.

Was doing some research here and came upon your answer
to another poster on this thread:

o backup and restore (my personal favorite, tests the restore process!)
o exp/imp
o transportable tablespaces
o sqlplus copy command
o dblinks using sql

and thought "Tom's first solution is perfect"..take
the prod backup and restore to test.

couple questions:
1) would I remove the oradata folder from test db
2) copy oradata folder from backup to test db
3) how would I tweak the control files to correct
the pointers to the data files.
4) would I need to tweak oradim and .ora files

we use a third party backup system that backs up the
entire server contents(including everything that is Oracle)
to a tape system.

any guidance and help is much, much appreciated.

thanks




Tom Kyte
January 20, 2005 - 10:44 am UTC

what says your DBA who should have this backup and RECOVERY <<<==== stuff down cold?

DBA by design...not by choice

denni50, January 20, 2005 - 11:13 am UTC

Tom

We have no in house dba..I serve in that role to the best of my current knowledge and abilities...when I know I'm over my head we get the vendor's DBA to guide,recommend and assist and that gets added to my knowledgebase.

Our backup/restore system is managed by the System Administrator since it involves a system that is not Oracle based(RMAN...etc)..we don't use RMAN. We have 3 servers that get backed up daily with preset schedules and are all managed by this third party system.

I know you are averse to this but we don't have Hot backups we are in noarchivelog mode and do only cold backup's...and yes...the db(s) get shutdown every morning,at various hours, to perform the backups as scheduled.

So I'm in the middle here trying to come up with the least intrusive way to get the darn data from prod moved to test with minimal demands on the prod db while debating with the vendor's "experienced" DBA why the need to re-clone an entire db just to transfer two itty bitty tablespaces.



Tom Kyte
January 20, 2005 - 7:09 pm UTC

don't take this the wrong way -- but if you don't know how to restore a tablespace to another system -- you are already sort of in over your head "dba wise"

I certainly hope these SA's understand how to RESTORE a database, and how to properly BACK UP a dataabase.

I'm not adverse to noarchivelog mode, AS LONG AS YOU UNDERSTAND THAT YOU WILL LOSE A DAYS WORK (or whatever your backup cycle is) someday. As long as you and mgmt are OK with that -- fine. I don't know many people who are however.

WILL lose -- not might, not maybe, it is like death and taxes...


You will need to restore system, the tablespace(s) you need, rollback to the other system from your backups. this is actually something that should take only the amount of time it takes to extract this from your backups - and have no effect on the production database and would typically be the fastest easiest way to accomplish this.

Tom..

denni50, January 21, 2005 - 9:02 am UTC

I know it's difficult for an outsider to understand
the dilemma I'm faced with. I know how to do export/import
and perform a transportable_tablespace...however my hands
are tied due to the limitations and confines imposed by
the vendor. I am being told that the above options are
no longer viable due to the complex structure of the app
design..what that means exactly..who knows.

Before we upgraded to 9i and was using an earlier version
of the vendor's software I transferred data from prod to
test via export/import with no issues.

Grant you I am not an "experienced" DBA in any sense of
word...I'm learning as I go along...from your books and
coming here...and what I can learn from those more knowledgable whenever that opportunity arises.

I came to this position as a programmer/analyst and just
happen to fill a need that wasn't available...not to
mention discovering that I REALLY LIKE this dba stuff...
a role I would never have even thought of myself as having
an interest or aptitude for. Three years ago I didn't
know what a tablespace was...so I've come alooooonnng
waaayyyy!....

As for the backup/restore system, we outsource a contract
service with a System Administrator and should the db
crash mgt wants the SA to be responsible for the Restore.

Given all the above I have to work within the boundaries
set by organization's operating procedures.

anyway thanks for always being there for me...whether
you realize it or not I consider you my mentor.
(as I'm sure everyone else here does too)




one last note...

denni50, January 21, 2005 - 9:14 am UTC

I was able to convince the vendor's DBA to perform
a restore to the test db utilizing the prod db backup..
it will be a good test for me (as this will be a first)
to actually observe how the restore process works on the
backup system...so more knowledge added to my knowledgebase.

:~)

Tom Kyte
January 21, 2005 - 11:59 am UTC

excellent ;)

it is very useful information

nandakumar, February 24, 2005 - 1:45 am UTC

why the primary key and unique key constriants are not copied? whenever u copy a table contains constraints from 1db to another db
what is major concept behind to that?
i feel index for primary and unique is not created explicitly
might be reason for this.
please clarify me





regards
nandakumar



Tom Kyte
February 24, 2005 - 5:40 am UTC

"U" - does "U" work for you? That would be great to know, I get lots of requests for "U" and I have yet to meet them (but I am looking forward to doing so, I have a ton of work for "U" when I find them -- they are very popular)




Copy vs "insert into select * from"

Rajeev, March 01, 2005 - 4:12 pm UTC

I have two questions :

(i) Does copy command not work if you are using oracle 8.1.6 client sqlplus while source and target databases are oracle 9.2.0.4.0

(ii) between two schemas of same database , copy 2 million rows , would you suggest if
insert into Schema1.t1 select * from schema2.t1
is same like
copy from schema2 insert into Schema1

I would feel since I can set arraysize and copycommit in copy command , copy command should be better .

let me know your views on it .

Tom Kyte
March 01, 2005 - 4:24 pm UTC

(i) i haven't tried, there is no reason "it should not"

(ii) insert /*+ append */ is probably the approach I would take, but I would not use the copy command in a single database, no.





follow up

Rajeev, March 01, 2005 - 4:59 pm UTC

Thanks Tom ,

(i) even with two identical tables in two different databases , oracle copy command gives me the error :
CPY0007: Select list has fewer columns than destination table

(ii) I would be interested to know why you feel copy command is not the right way to copy between two schemas of same database.

I am thinking on the lines that copy command doesnÂ’t create huge rollback segments ( for very large table ) if you have intermediate commit ( copy commit 1 ) while insert into select * from will do that , since there is no partial commit in insert .

Thanks a lot and I feel this site is extremely useful anyone from novice first time oracle user to an experienced DBA .


Tom Kyte
March 01, 2005 - 5:22 pm UTC

(i) if you are pushing, try pulling
if you are pulling try pushing

but you might be out of luck as 816 is not supported -- so unless this was a known issue with a known workaround, it won't happen.

(ii) inserts do not generate "huge rollback" -- we register "delete+rowid" only. If you /*+ append */, it generates no rollback. Only index modifications would and if you are doing tons of new inserts, might be best to disable them, load and rebuild them (faster)


don't be afraid of rollback, by using array inserts and commits you'll generate more total rollback, more total redo, you'll wait more, go slower.

why PK and FK does not copied

nandakumar, March 02, 2005 - 4:17 am UTC

why the primary key and unique key constriants are not copied? whenever copy a
table contains constraints from 1db to another db
what is major concept behind to that?
i feel index for primary and unique is not created explicitly
might be reason for this.



Tom Kyte
March 02, 2005 - 7:37 am UTC

because copy is defined as something that just copies the table data, not all of the metadata about the table.

triggers don't come
views don't come
foreign keys cannot come (what table might they point to on this other database?)

it is just a way to copy a tables data.

Hopefully, you have the DDL to properly implement your table(s) checked into a serious code repository -- that is where you would get all of the implemenation details

or use export/import

copy command issue

Rajeev, March 03, 2005 - 12:09 pm UTC

Thanks Tom for your help in understanding me the difference between copy and insert into . 

I still have issues with copy command since we moved to oracle 9i , whether I try pulling or pushing , I still get oracle error .

I am putting entire log for your review and again appreciate any help in this .

Thanks

Rajeev

=======================================================

SQL*Plus: Release 8.1.7.0.0 - Production on Thu Mar 3 11:45:01 2005

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> 


SQL> show user
USER is "BBPROD"
SQL> select * from v$instance ;

INSTANCE_NUMBER INSTANCE_NAME                                                   
--------------- ----------------                                                
HOST_NAME                                                                       
----------------------------------------------------------------                
VERSION           STARTUP_T STATUS       PAR    THREAD# ARCHIVE LOG_SWITCH_     
----------------- --------- ------------ --- ---------- ------- -----------     
LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST                   
---------- --- ----------------- ------------------ ---------                   
              1 DNYBIG03                                                        
ndvgrd07                                                                        
9.2.0.4.0         20-FEB-05 OPEN         NO           1 STARTED                 
ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE   NORMAL                      
                                                                                

SQL> desc transformations_back
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(8)
 SEQNUM                                    NOT NULL NUMBER(9)
 SRCELEMNAME                               NOT NULL VARCHAR2(128)
 TRANSFORM                                 NOT NULL VARCHAR2(128)
 PARMS                                              VARCHAR2(400)
 TRGNAME                                   NOT NULL VARCHAR2(64)
 TRGELEMNAME                                        VARCHAR2(128)

SQL> copy from ficdrloader/<Password>@dnybig01 -
> insert transformations_back -
> using select * from transformations -
> 

Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)

CPY0007: Select list has fewer columns than destination table

SQL> connect ficdrloader/<Password>@dnybig01
Connected.
SQL> show user
USER is "FICDRLOADER"
SQL> select * from v$instance ;

INSTANCE_NUMBER INSTANCE_NAME                                                   
--------------- ----------------                                                
HOST_NAME                                                                       
----------------------------------------------------------------                
VERSION           STARTUP_TIM STATUS       PAR    THREAD# ARCHIVE LOG_SWITCH_   
----------------- ----------- ------------ --- ---------- ------- -----------   
LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST                   
---------- --- ----------------- ------------------ ---------                   
              1 DNYBIG01                                                        
ndvgrd07                                                                        
9.2.0.4.0         20-FEB-2005 OPEN         NO           1 STARTED               
ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE   NORMAL                      
                                                                                

SQL> desc transformations
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(8)
 SEQNUM                                    NOT NULL NUMBER(9)
 SRCELEMNAME                               NOT NULL VARCHAR2(128)
 TRANSFORM                                 NOT NULL VARCHAR2(128)
 PARMS                                              VARCHAR2(400)
 TRGNAME                                   NOT NULL VARCHAR2(64)
 TRGELEMNAME                                        VARCHAR2(128)

SQL> copy to bbprod/<password>@dnybig03 -
> insert transformations_back -
> using select * from transformations

Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)

CPY0006: Select list has more columns than destination table

 

Tom Kyte
March 03, 2005 - 12:15 pm UTC

are you using the 8i sqlplus against 2 9i instances?

yes

Rajeev, March 03, 2005 - 1:00 pm UTC

yes , 8.1.7 sqlplus against 9i . I dont have any other issues except copy command until now .

any suggestions/workaround or if this is a known issue ?

Tom Kyte
March 03, 2005 - 5:26 pm UTC

can you try the 9i sqlplus? copy works much better if the sqlplus is local to one of the databsaes -- otherwise the data travels from database a to sqlplus on machine b to database c.

it works

Rajeev, March 09, 2005 - 5:32 pm UTC

Thanks Tom . it does work with Oracle 9i client . One assumes it must be an issue with oracle 8.1.7 client against 9i database .

Is this bug?

shailesh saraff, May 09, 2005 - 8:54 am UTC

Hi Tom,

Thanks Tom for elaborate information about COPy command.
On Oracle 8i, OS (Windows) when I run copy command for a table with Long Column I got GPF, earlier set long was 2 GB, to avoid bug Then I did following...

set long 33500000
set arraysize 1
set copycommit 1
copy from UserName/Pass@DBNAME to UserName/Pass@DBNAME create temp123 using select * from Table where projectid = 13;

Now it works, but problem is my long data contains 1200 rows (size 70k), after copy only first 136 records are copied.

What can be the problem? Why long data is truncated in COPY command? could you please guide me.

Thanks & Regards,

Shailesh



Tom Kyte
May 09, 2005 - 9:17 am UTC

Please utilize support for that -- I haven't encountered that issue at all.

</code> http://asktom.oracle.com/Misc/MoveLongs.html <code>

why not just use a single from or a single to, not both.

How to protect the password

Sreeram, January 25, 2006 - 6:01 am UTC

Tom,

Is there a way to protect the password while using the copy command.

Lets say i create a sql script on the unix side, with the copy utility in it, to be executed as a job.
We can't afford to have the user password to be left on the script.

Is there alternative to this?

Cheers,
-Sreeram.


Tom Kyte
January 25, 2006 - 1:37 pm UTC

not really, because you would not want to use remote OS authentication (and hence just using "/" won't fly)

suggest you use a dblink for this instead. not sqlplus copy. to avoid the password issue.

some help on copy command

Vijay Sehgal, April 11, 2006 - 8:09 am UTC

Hi Tom,
good day to you as always, just need some more help on this command from you,

1. Is it possible to execute this command from pl/sql block, the reason to ask you this is we are working on a module to move selective data from production database to test environment so that we don't have to create entire production box copy for testing purpose. So as of now what we are doing is insert into ... select * from over dblink but this is not supported on long datatype column we have a third party billing system that has tables with long data type so we need some way to have that selectively in our test environment thought of using copy but don't know if this can be used in pl/sql i tried creating a anonymous block and copy comman but it didn't worked any work around for this.

2. can we use where clause to restrict data by copy command e.g.
copy FROM test/test@ora INSERT bill USING SELECT * FROM bill where customer_id in (select customer_id from customers)

here customers will be a local table.

your help in this regard is much appreciated, thanks a lot in advance.

Kind Regards,
Vijay Sehgal.

Tom Kyte
April 11, 2006 - 4:06 pm UTC

1) no, it is the "sqlplus copy command" - it is not sql.

In sql, you would just use insert into select * from ....


you can use EXP with "query=" or expdp with query filters as well.

2) you could

Copy command

M.P.Kiran Kumar, April 17, 2006 - 7:15 am UTC

Tom

Can I use copy command in pl/sql block or in sql plus script, to run periodically, I have to get data from different server to my server every 15 min. Is it correct way of using copy command or shall I user db links for the same to retrive data from different tables from the other servers.

Regards
M.P.Kiran Kumar


Tom Kyte
April 17, 2006 - 8:06 am UTC

the SQLPLUS copy command is a utility built into SQLPLUS. It is not "sql", it is a feature of the command line tool sqlplus.

you would use database links in plsql to access a remote site.

DB Links in PL/SQL

M.P.Kiran Kumar, April 17, 2006 - 9:10 am UTC

Tom,

How to use the DB links in pl/sql i.e. in procedures, functions, cursors. Please can you show me with one simple example.

Regards
M.P.Kiran Kumar

Tom Kyte
April 17, 2006 - 9:58 am UTC

sure

begin
for x in (select * from table@dblink_name)
loop
.....



no different than anything else - you just tell us what table to access and we do.

thanks a lot for your answers to my previous post but I need some more help on this

Vijay, April 19, 2006 - 9:26 am UTC

Hi Tom,
good day to you as always, thanks for the answers to my previous post,however I need your help on this my earlier question to you was

Can we use where clause to restrict data by Copy command
e.g.
copy FROM test/test@ora INSERT bill USING SELECT * FROM bill where customer_id
in (select customer_id from customers)

Here customers will be a local table.

I tried this but I am getting error message as ORA-00942: table or view does not exist. I think that it's considering custoemrs table to be on remote site how can I use the table in local site for restriction of data by Copy command, if you can please give one e.g.

Thank a log in advance.

Kind Regards,
Vijay Sehgal.

Tom Kyte
April 19, 2006 - 9:30 am UTC

use a database link.

insert into bill
select * from bill@remote_site where customer_id in (select customer_id from customers);



No matter what, in the above case (using sqlplus copy) you would have to use a dblink - for you see, all sqlplus copy does is:

a) has a connection to the local database
b) opens up a SEPARATE, DISTINCT client connection to the other database
c) runs select on other database
d) array fetches data and inserts into local database connection


The "using select" would have to use a dblink back to the source database if you wanted to use copy - but that would now mean there are THREE connections (the local, the remote, and the dblink back!)

thanks a lot for the quick answer

Vijay Sehgal., April 19, 2006 - 9:40 am UTC

Hi Tom,
good day to you, thanks a lot for the quick answer but my problem is the table I need to insert into data and get the data from have long columns, this is a third party database so we don't have much control over it, it's a database of billing tool Geneva. So to move the data from the table having long column I have only one option i.e. Copy command and I don't want to move all the data as it's a huge table so how can I restrict so that data of only few customers is passed and it can be used to create test environments.

Your help in this regard is much appreciated.

Thanks a lot in advance.

Regards,
Vijay Sehgal.

Tom Kyte
April 19, 2006 - 10:23 am UTC

then you would either

a) need to copy the customer table over to the remote site
b) use a dblink from the remote site back to local.

thanks once again for the help but I am getting ,error number: ORA--3997042

Vijay Sehgal., April 20, 2006 - 3:49 am UTC

Dear Tom,
Thanks a lot for the help, I tried the option 2 suggested by you, creating a dblink in remote site to local database, but when I execute the copy comman as below I get the following error

ERROR:
ORA--3997042: Message -3997042 not found; product=RDBMS; facility=ORA

the command I executed was as follows

copy from test/test@remote insert bill using select * from bill where customer_num in (select customer_num from customers@local);

I tried looking for the reason for this error but in vain. I didn't get any help from documents or on metalink for this I am using oracle 8i on solaris 8.1.7, if you can please help me on this.


Thanks a lot in advance.

Kind Regards,
Vijay Sehgal.

Tom Kyte
April 20, 2006 - 8:05 am UTC

I would suggest trying to create a view on the remote site:

create or replace view my_view
as
select * from bill where customer_num in (select customer_num from customers@local);


and using the view - what then?

thanks a lot for all the help on this

Vijay Sehgal, April 20, 2006 - 9:47 am UTC

Hi Tom,
thanks a lot for the answers and help on this, one thing though just out of curiosity can you please tell me the reason for the below error message or place to find details about the same.

ERROR:
ORA--3997042: Message -3997042 not found; product=RDBMS; facility=ORA

Thanks a lot again for all the help.

Regards,
Vijay Sehgal.



Tom Kyte
April 20, 2006 - 10:14 am UTC

nope, that is not a valid error code.

copy command

Ganesh, June 01, 2006 - 11:42 am UTC

Tom,
I like copy command as it most useful.
I want to know if my source table having timestamp column still Can i copy data. Even my target also having same datatype timestamp.

Thanks
Ganesh

Tom Kyte
June 01, 2006 - 11:54 am UTC

when you tried it, did it work...

problem is the copy command is "deprecated" and not being enhanced to support newer types. You might be able to use to_char to hide the fact that a new type is "not supported" in some cases.

if the COPY command is deprecated, is expdp/impdp is the new alternative?

Zafer, June 22, 2006 - 5:30 pm UTC

Tom,
if the sqlplus COPY command is deprecated, is expdp/impdp is the new alternative (10g and higher) or the preferred utility instead of using the COPY command ?
thank you


Tom Kyte
June 23, 2006 - 10:01 am UTC

data pump would be the prefered replacement for using copy yes.

Copying CLOB

Laly, August 21, 2006 - 7:38 am UTC

Hello,

I'm working on 8.1.7
I have to copy slice of data from tables containing clobs from one db to another historic db.

However, the copy process is failing : 

SQL> copy from .../...@... replace laly1 using select * from ... where rownum <= 10000;

Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 2000000000. (long is 2000000000)

ERROR:
ORA-00911: invalid character

Is there any issues while using copy command on tables containing clob ?

What other solution can i use ? A db link and insert...select statement ?


Thanks in advance,

Laly.
 

Tom Kyte
August 27, 2006 - 9:09 am UTC

use a dblink, the copy command has not been updated to deal with newer datatypes over time.

clobs are dblink "friendly", you can use an insert as select.

A reader, January 05, 2007 - 10:52 am UTC

Tom,

Could you please show an example to copy a table data from one database to another database? Both the databases have the table with same name.

Thanks in advance.
Tom Kyte
January 06, 2007 - 8:18 am UTC

insert into table@remote select * from table;


is perhaps the easiest way to do that - just use a database link.

You also have the sqlplus copy command, export/import, datapump (10g) among other choices.

Rani, January 08, 2007 - 5:21 pm UTC

We use Copy...commit statement extensively in our processing...

copy from can/pw@qa to can/pw@test -
append employee using (select * from -
employee where id_load = 1001 );

We plan to upgrade to 10g very soon... Would we have to shift to data pump OR would the copy.. commit work for the data types Number, varchar, varchar2 & date. We do not have clobs in these tables.

Thanks in advance

Tom Kyte
January 08, 2007 - 6:07 pm UTC

you would have to test - it is deprecated functionality. I would just use insert /*+ APPEND */ or insert normal - why commit - what happens when it fails part way through?

Data transfer

A reader, April 23, 2007 - 2:32 pm UTC

We currently have 9i databases in production and in order to move the database (schema and data) to a test environment, we do exp/imp. We are implementing a new Oracle 10g system and we want to eventually be able to (on demand) do something like we are doing for 9i. What is the best way in 10g to achieve this and what are the solutions that you have in mind for this ?
Tom Kyte
April 23, 2007 - 4:49 pm UTC

datapump - the replacement for export/import. Higher speed, direct path operations, parallelizable, database to database (no intermediate files needed if you want)....

A reader, April 26, 2007 - 2:09 pm UTC

Thank you for the suggestion. Another question regarding data pump. Now when we refresh from Production to test environment, the schema which we are importing into might already exist. If it does, is there a way in data pump to just replace the entire schema when we import ? How can we achieve that ?
Tom Kyte
April 27, 2007 - 10:24 am UTC

you would have to empty it out first.

A reader, April 27, 2007 - 11:38 am UTC

Is there an easy way / script that you have to empty out a schema ?
Tom Kyte
April 27, 2007 - 11:49 am UTC

drop user a cascade;


A reader, April 27, 2007 - 12:01 pm UTC

You mentioned above that using datapump we can do a database to database import (without use of dump files), I am assuming you meant the use of NETWORK_LINK parameter. I am looking at creating a database link from the production box to the test box and can I directly export the data from production to test by use of this parameter? Is there a specific advantage / disadvantage to using this ? Would you advise against this ?
Tom Kyte
April 27, 2007 - 12:04 pm UTC

If I would advise against using it, I wouldn't have mentioned it :)

it is an option, the advantage - no file, direct database to database copy.

A reader, April 27, 2007 - 12:36 pm UTC

Is network_link an option with only impdp and not expdp. There would be a restriction for us to create database links from test system to production system but we can create a database link from production system to test system. Given this setup how can we use impdp / expdp with NETWORK_LINK?
Tom Kyte
April 27, 2007 - 5:19 pm UTC

expdb would use a dblink to write a file on the other system
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_overview.htm#sthref22


A reader, May 01, 2007 - 10:37 am UTC

This is what I tried. I have a test database dev on server A and production database prd on server B. I ran expdp as follows from server A dev database. I have a database link lnkprd created in dev that points to prd.

expdp devusr/devpwd directory=testexp network_link=lnkprd dumpfile=dmpprod.dmp logfile=dmpprod.log

Is this the correct usage of expdp with network_link? Moreover, this was taking a very long time and I killed the job. Is it supposed to take very long ?
Tom Kyte
May 01, 2007 - 11:05 am UTC

a very long time

is not

very descriptive. 5 minutes could be a very long time. 5 hours could be very fast.


anyway - you are not going to do a database to database copy - it would be creating a file. Is that what you wanted - it seemed we were going down this path to AVOID the file.

A reader, May 01, 2007 - 11:24 am UTC

To be a little descriptive on the times, I tried to do expdp, ftp and impdp and that took an hr. The expdp with network_link showed import to 2 tables and it had already taken a couple of hrs.

I thought after reading the article that expdp will allow me to do an export from the production box and create a dump file on the test box with network_link pointing to test from prod. But after I was trying the export I realized that expdp with network_link works the other way around. I had to run the export from the test box in order to export the contents of the prod database with the link pointing from test to prod. I did not know if that was the right way to do it. If I have to create the link anyway from test to prod, then I could do impdp and do a database to database copy. Please let me know what you think.
Tom Kyte
May 01, 2007 - 11:37 am UTC

i think you solved the problem in 1 hour?

A reader, May 01, 2007 - 11:40 am UTC

1 hr is for the traditional method - expdp, ftp and impdp.

But I want to explore the usage of network_link with expdp and impdp and compare the results / timings. Please advise.
Tom Kyte
May 01, 2007 - 11:59 am UTC

but you seem to be doing that? the comparison?? I'm not sure - other than pointing you to the documentation for the parameters - what else you need from me.

COPY FROM and EXECUTE IMMEDIATE

Robert, October 23, 2008 - 10:43 am UTC

Hello Tom
Is it possible to use the >>COPY FROM<<-command
in PL/SQL using EXECUTE IMMEADIATE ?
Everytime I tried to use it I got:
ORA-00900: invalid SQL statement
ORA-06512: at "TEST_SCHEMA.SF_TEST", line 37
ORA-06512: at line 4
Greetings
Robert
Tom Kyte
October 23, 2008 - 1:47 pm UTC

no, copy is a sqlplus'ism - period, it is not sql, it is a feature of sqlplus.

you would simply

insert into t select * from t@remote;

"COPY FROM<<-command in SQL

Duke Ganote, October 23, 2008 - 4:48 pm UTC

The 9i docs say: "In general, the COPY command was designed to be used for copying data between Oracle and non-Oracle databases. You should use SQL commands (CREATE TABLE AS and INSERT) to copy data between Oracle databases."
http://download.oracle.com/docs/cd/A91202_01/901_doc/server.901/a88827/ch64.htm#1008809

The 10gR2 documentation says "The COPY command will be obsoleted in future releases of SQL*Plus. No new datatypes will be supported by COPY."
http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/apb.htm

If you're passing data to/from a non-Oracle database, Heterogeneous Services (HS) is the answer.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4406709207206
http://download.oracle.com/docs/cd/A91202_01/901_doc/server.901/a88856/c02servr.htm#27623

COPY FROM obsoleted

Robert, October 24, 2008 - 8:47 am UTC

Hello Duke Ganote
Thanks a lot for your reply !
I just want to transfer data from one Oracle-database to another Oracle-database and would prefer to use INSERT to copy the data. But unfortunately our DB-admins don't want DB-links between these databases. So COPY should be a workaround for me. But if it's obsolete in version 10, it won't be a good idea to use it althought the tables to be copied don't contain new datatypes.
Perhaps this fact could help me to get the DB-links.
Thanks again and have a nice weekend.
Robert

Disappointing

Brett, February 05, 2009 - 11:11 am UTC

I have used the copy command in the past to move small amounts of data around. It was easy and efficient. Now I run into the roadblock of the copy command going away. The docs say to use datapump as a replacement. The issue here is that datapump requires a db link be in place. DB links are not allowed at my shop and they are not going to change the rules (believe me I have pleaded) and open a can or worms. Unless I am missing something, I now have to write a lot more code to export a file from the source db on the source server, copy it to the target server (making sure to clean up the file on the source server), and import it to the target database (making sure to cleanup the file here as well). Not to mention all of the error handling that will take place with all of the moving parts.
Tom Kyte
February 05, 2009 - 12:01 pm UTC

perhaps sqldeveloper would be of interest to you.

it can export data in a loadable format.

it can also copy data from database 1 to database 2.

And they are working on a command line version of it as we speak, so you wouldn't have to fire up the GUI at all if you didn't want to.

Maybe, but

Brett, February 11, 2009 - 8:20 am UTC

Thanks for the suggestion Tom, but I'm still kind of bummed because it seems like a very useful tool is going away. I'll look into the SQL Developer thing, but I'm not so sure it will be a suitable replacement, but we'll see.
Tom Kyte
February 11, 2009 - 12:27 pm UTC

Hey, guess what....

I just heard this morning - seriously - that exp will be included by popular demand for the foreseeable future (11.2 and beyond)

So, keep on voicing stuff...


Exp???

Brett, February 13, 2009 - 10:55 am UTC

Exp or copy? Exp staying around doesn't really excite me, but copy sticking around would pretty much make my day.
Tom Kyte
February 16, 2009 - 11:27 am UTC

ah, sorry, got confused.

Export was slated to go away in 11.2, it is not.

sqlplus copy is still in the status it has been since last century - fully deprecated, not advanced anymore. sqldeveloper does have this capability (without support for objects of course, objects are not cross database friendly at all)

copy command - slow

Todor, July 14, 2009 - 10:40 am UTC

Hi Tom,
I have the following problem:
I use the script, which copies some tables from DB2 /on unix/ to DB1 /on windows 2008/. The shell script is situated on the unix server. some rows:
shell script:
sqlplus -s >/dev/null 2>&1 "user/pass@DB1" <<EOF
@/path/script.sql;
exit
EOF
part of sql script:
set arraysize 1000;
set copycommit 1000;
...
copy from user/pass@DB2 insert Table1_in_DB1 using select col1_id,col2 ,... from Table1_in_DB2;
copy from user/pass@DB2 insert Table2_in_DB1 using select * from Table2_in_DB2;

All worked perfect until last week, the script passed for 8 minutes.
Now it works very very slow, end ends with "snapshot too old" or I kill it.

The script works perfect when I start it from my PC/my sqlplus client/.

I also tryed /from original location - Unix server/:
set arraysize 5;
set copycommit 100;
The script with this values passed for 4,5 hours - too slow.

Is there a problem with network or with the Windows server?
Please, give me an advice? Thank you!

Tom Kyte
July 15, 2009 - 11:10 am UTC

when you run the script on the DB2 the flow of data is:


Db2 -> Db1

when you run it from your desktop the flow of data is:

Db2 -> your desktop -> Db1


so, I would hazard a guess that the networking between db2 and db1 directly has been impacted somehow. Why don't you log onto db2 and ftp a file to db1 and measure the time. Then ftp file from db2 to your desktop and then ftp that file to db1 and measure the times. Then compare.


Also make sure that your tnsnames.ora file is the same as the one on Db2. It could be something silly like "db2 is connecting to db1 using shared server, my desktop connects to db1 using a dedicated server" or something.

copy command hangs up

Krishna, March 18, 2010 - 2:32 am UTC

Hi Tom,

I am using Oracle 10g.

I was able to execute once the copy command and if I try again it hangs up, what might be the reason?

Is there a limit of number of copy statements in a single sql file?

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library