You are a generous person, Arup! Thanks...
on 5/31/03 10:54 PM, Arup Nanda at [EMAIL PROTECTED] 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/[EMAIL PROTECTED] 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/[EMAIL PROTECTED] create lt5 using select * from lt3 > > SQL> set long 80 > SQL> copy from ananda/[EMAIL PROTECTED] 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: <[EMAIL PROTECTED]> > 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" <[EMAIL PROTECTED]> >> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> >> 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 <[EMAIL PROTECTED]> 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/[EMAIL PROTECTED] - >>>> 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" >>>> <[EMAIL PROTECTED]> >>>> 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: [EMAIL PROTECTED] >>>>> >>>>> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] >>>> >>>> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] >>> >>> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
