| SQLPLUS COPY NOT WORKING [message #59311] |
Tue, 11 November 2003 08:41  |
Vinny75
Messages: 44
Registered: October 2003
|
Member |
|
|
Can someone tell me what is wrong here. I am trying to use COPY command to create a new table and load it based on a query and commit every 1000 records. Here is what I use:
set arraysize 100;
set copycommit 1;
copy from me/me@test to me/me@test
create temp_new_table (col1, col2, col3) -
USING -
select col1, col2, col3
from scott.test_table;
Error I get is
A missing FROM or TO clause uses the current SQL*Plus connection.
create temp_new_table (col1, col2, col3
*
ERROR at line 1:
ORA-00901: invalid CREATE command
|
|
|
|
|
|
| Re: SQLPLUS COPY NOT WORKING [message #59329 is a reply to message #59317] |
Wed, 12 November 2003 06:32  |
Vinny75
Messages: 44
Registered: October 2003
|
Member |
|
|
Thiru
Thanks for the response. But I get this error now.
Error in SELECT statement: ORA-01002: fetch out of sequence
Please note that I did set Arraysize to 1000 and CopyCommit to 1 so I wanted to commit every 1000 records.
|
|
|
|
| Re: SQLPLUS COPY NOT WORKING [message #59334 is a reply to message #59329] |
Wed, 12 November 2003 09:03 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
What version of Oracle are you using ? I have heard of some bugs in 8.x that could cause this. A quick search on Metalink reveals bugs 903258 ,1329401,644413 associated with this.
The workaround is to create the table using CTAS or Insert/Select across database link.
Also , could you assign a large rollback segment and not do the copycommits ?
HTH
Thiru
|
|
|
|
| Re: SQLPLUS COPY NOT WORKING [message #59359 is a reply to message #59334] |
Thu, 13 November 2003 06:16 |
Vinny75
Messages: 44
Registered: October 2003
|
Member |
|
|
I use 8.0.5.
What is CTAS? I dont need to use DB link. We have been experiencing some bad perfornance issue with a query that accesses a complex view. Users put an order by clause on the select clause from the view and that slows the result very badly. Without the order by clause the query works moderately faster. which is why I wanted to create a TemP table using COPY command and once all valid rows are loaded into it, I would do an order by and return it to the app users.
|
|
|
|
| Re: SQLPLUS COPY NOT WORKING [message #59366 is a reply to message #59359] |
Thu, 13 November 2003 10:57 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
CTAS = Create Table As Select
eg) Create table A as select * from B;
You dont need to use dblink if you dont need to.
If order by drastically slows down the query , consider these
i) Add Index on the column you are sorting by
ii) Increase Sort_Area_size
iii) Use Locally Managed temporary tablespace with tempfiles
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
