Sai
I would research that before making an assumption. The COPY command is a
bit different from anything else in Oracle. I found the following note with
a quick Google search:
"Note that sqlplus COPY has a port specific limit on the maximum size of
LONG you can copy. Refer to the SQLPLUS User Guide and your port specific
documentation to determine if this is feasible or not."
Also, I haven't seen where anyone has suggested that transportable
tablespace might meet your requirement. Have you considered that?
Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com
-----Original Message-----
Sent: Saturday, May 31, 2003 5:30 PM
To: Multiple recipients of list ORACLE-L
but i think there is a sqlplus limitation of 64k and
any data longet than 64k will get truncated in this
case too..
correct me if i am wrong,even if u set long to a very
high value,data more than 64k in lenght will get
truncated .
sai
- Arup Nanda <orarup_at_hotmail.com> wrote:
> For situations like this you have the COPY command
> of SQL*Plus.
>
> Remember, it's a SQL*Plus comamnd like set, btitle,
> etc. not a sql command
> you can embed inside a pl/sql block. You could
> create a table similar in
> structure to main table and then polulate the data
>
> SQL> SET LONG 999999
> -- this is neededto set the max size of the long
> data; otherwise it gets
> truncated.
>
> COPY FROM SCHEMA_NAME/PASSWORD_at_CONNECTSTRING -
> APPEND HOLDINGTABLE -
> USING SELECT * FROM MAINTABLE WHERE DATE_COL <
> SYSDATE - 12*30
>
> Note the use of hyphens after the lines. SQL*PLus
> commands are expected to
> be in one line. Since I am continuing on to the
> next, I used the
> continuation character hyphen.
>
> This by default commits after all the rows are
> loaded. You can control the
> commit frequency by specifying two parameters
>
> -- sets 100 records per array
> SET ARRAYSIZE 100
> -- sets a commit to occur after every 200 batches,
> or 20,000 records
> SET COPYCOMMIT 200
>
> This process is fairly simple and can be easily
> automated using a shell
> script. Any error raised by the sql block can be
> checked.
>
> Hope this helps.
>
> Arup Nanda
> www.proligence.com
>
>
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L"
> <ORACLE-L_at_fatcity.com>
> Sent: Friday, May 30, 2003 7:04 PM
>
>
> > hi there is this project that is going on for
> > archiving old data from oltp system that is older
> than
> > 12 months and then purging them in the main db.
> >
> > the tables that are to be archived are with long
> rows.
> > they cannot be converted to lobs since this is a
> third
> > party application. here is where the problem lies.
> > oracle support when contacted says either mv to
> lobs
> > to make this move easier or use oci ..blah.blah..
> to
> > get this working if you want to remain in longs.
> >
> > there are some options i have though about:
> > 1. export /import ..but should make this highly
> > automated since the main db and archival db will
> be on
> > different hosts, this will not be monitored and
> import
> > has to go thru w/o issues etc.
> > 2. create snapshot - but they dont work with
> > long..hence not an option.
> > 3. getting sqlldr to work but i think it has that
> 32k
> > column size limitation.
> >
> >
> > so can you please suggest me whetehr there is
> > something else i can do or option 1 is the best
> given
> > the environment. the oracle is 8.1.7.2 on sun 2.8.
> >
> > thanks
> > sai
> > --
> > Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> > --
> > Author: Sai Selvaganesan
> > INET: ssaisundar_at_sbcglobal.net
> >
> > Fat City Network Services -- 858-538-5051
> http://www.fatcity.com
> > San Diego, California -- Mailing list and
> web hosting services
> >
>
> > To REMOVE yourself from this mailing list, send an
> E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB
> ORACLE-L
> > (or the name of mailing list you want to be
> removed from). You may
> > also send the HELP command for other information
> (like subscribing).
> >
> >
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> --
> Author: Arup Nanda
> INET: orarup_at_hotmail.com
>
> Fat City Network Services -- 858-538-5051
> http://www.fatcity.com
> San Diego, California -- Mailing list and web
> hosting services
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Sai Selvaganesan
INET: ssaisundar_at_sbcglobal.net
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: DENNIS WILLIAMS
INET: DWILLIAMS_at_LIFETOUCH.COM
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Sat May 31 2003 - 18:59:38 CDT
