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