No modifications are made to table1. The task is a migration of data
from legacy databases to a new system. Table1, 2,3,4,5 etc. are tables
of data as they were in the legacy database. It would be possible to
make all of my source tables read-only.
In article <9tuc0r05eu_at_drn.newsguy.com>, Thomas Kyte
<tkyte_at_us.oracle.com> writes
>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
>
--
Adrian Carlson-Hedges
Received on Tue Nov 27 2001 - 12:53:27 CST
