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).
Received on Sat May 31 2003 - 17:29:40 CDT
