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