Re: archiving data

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

>> a long column of that size, but according to the fine manuals, the max

>> 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).