Re: Options for large transactions

In article <9tu1sf$n9d$1_at_plutonium.btinternet.com>, "Adrian says...
>
>Oracle 8.1.6
>
>I have a procedure that contains something like :
>
>[...]
>-- about 50000 records in table1
>CURSOR my_cursor IS Select * from table1;
>
>FOR my_record in my_cursor loop
> /*
> * insert about 40-100 records in tables
> * R,S,T,X,Y and Z based upon data
> * from the cursor, and some other static tables (tables 2,3,4,5...)
> */
> do_plenty_of_stuff;
>-- Commit;
>END LOOP;
>[...]
>
>If I have the commit commented out, then I avoid the snapshot too old error,
>however I run out of rollback. If I uncomment the Commit, then it is likely
>that I will get the snapshot too old error.
>
>If I wanted to make a rollback segment big enough for the entire
>transaction, then it would need to be > 2Gb. I can do this, but am a little
>reluctant to. Especially since I am only testing a load, and when my
>procedure is complete, it will not be my database that the data will be
>loaded into.
>
>What other options do I have?

do you modify table1?

>
>It could be feasible to put tables 1,2,3,4, 5... into their own tablespace,
>and have this tablespace as read-only. Would this be a possible solution, or
>does the fact that the cursor is based upon a read-only tablespace make no
>difference?
>
>I guess running the procedure in smaller chunks is an option, but I'm hoping
>some bright spark has a better solution.
>
>Thanks
>
>Adrian
>
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp