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)
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
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,
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,
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
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,
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 ?
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
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
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
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.
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.
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.
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?
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
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
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%'
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
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.
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>
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
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)
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.
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
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
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
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.
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
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 !
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 !
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 !
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 !
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 !
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
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?
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
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.
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.
:~)
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
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 .
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 .
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.
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
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 ?
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
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.
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.
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
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
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.
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.
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.
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.
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
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
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.
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.
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
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 ?
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 ?
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 ?
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 ?
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?
April 27, 2007 - 5:19 pm UTC
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 ?
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.
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.
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
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
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.
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.
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.
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!
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?