Re: archiving data

Tim, Thanks for the compliments.
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Sunday, June 01, 2003 10:59 AM

> You are a generous person, Arup! Thanks...
>
>
> on 5/31/03 10:54 PM, Arup Nanda at orarup_at_hotmail.com wrote:
>
> > I just did a few tests with a LONG field in a table. Final Answer: data
more
> > than 64K is properly loaded using COPY.
> >
> > Test Setup
> >
> > Used a plain text file, s.dat in unix with 97885 characters (97K) .
Created
> > a table LT3 with only one field COL1 LONG. Used SQL*Loader to load the
data
> > into the table. The controlfile looks like this
> >
> > load data
> > infile 's.dat' "var 5"
> > into table lt3
> > (
> > col1 position(1:100000) char)
> >
> > I placed a number 99000 in the beginning of the line 1 on the file s.dat
to
> > indicate the length. After loading to the table LT3, I created a table
LT4
> > as follows
> >
> > SQL> set long 99000
> > SQL> copy from ananda/tiger_at_anw1 create lt4 using select * from lt3
> >
> > Then the long size was changed and I created two more tables
> >
> > SQL> set long 64000
> > SQL> copy from ananda/tiger_at_anw1 create lt5 using select * from lt3
> >
> > SQL> set long 80
> > SQL> copy from ananda/tiger_at_anw1 create lt6 using select * from lt3
> >
> > Finally, I created a third table to hold the data in LOB format so that
I
> > can measure it.
> >
> > SQL> set long 99000
> > SQL> insert into lt7 select 3, to_lob(col1) from lt3;
> > SQL> insert into lt7 select 4, to_lob(col1) from lt4;
> > SQL> insert into lt7 select 5, to_lob(col1) from lt5;
> > SQL> insert into lt7 select 6, to_lob(col1) from lt6;
> > SQL> commit;
> >
> > SQL> select col1, dbms_lob.getlength(col2) from lt7;
> >
> > COL1 DBMS_LOB.GETLENGTH(COL2)
> > ---------- ------------------------
> > 3 96057
> > 4 96057
> > 5 63996
> > 6 76
> >
> > 4 rows selected.
> >
> > The results speak for themselves. As you can see, the COPY command
correctly
> > copied data from one table to the other where the chunk was about 97K,
more
> > than the 64K limit you mentioned. But the key was setting the LONGSIZE
> > parameter in SQL*Plus. When I set it a low value, like 80 bytes, the
value
> > was truncated.
> >
> > Hope this helps in your archiving strategy.
> >
> > Arup Nanda
> > www.proligence.com
> >
> > ----- Original Message -----
> > To: <ORACLE-L_at_fatcity.com>
> > Sent: Saturday, May 31, 2003 10:46 PM
> >
> >
> >> Sai,
> >>
> >> Where did you find that limitation of 64K? Although I admit I have not
> > used
> >> a long column of that size, but according to the fine manuals, the max
> > size
> >> of LONG column copied is 2 GB; actually 2,000,000,000 bytes, not 64K.
You
> >> have to specify the size of long in your session using SET LONG
2000000000
> >> before attempting the copy command.
> >>
> >> Please let us know where you found that 64K limitation. The ohter thin
you
> >> have to consider is that COPY is being depecrated in 10i, or whatever
it
> >> will be called; but then again, I hope your application will have
ceased
> >> using LONGs.
> >>
> >> Arup Nanda
> >> www.proligence.com
> >>
> >> ----- Original Message -----
> >> From: "Sai Selvaganesan" <ssaisundar_at_sbcglobal.net>
> >> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> >> Sent: Saturday, May 31, 2003 6:29 PM
> >> Subject: Re: archiving data
> >>
> >>
> >>> 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: Tim Gorman
> INET: tim_at_sagelogix.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: 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).