Tim, Thanks for the compliments. ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> 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 [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). > > -- 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).