Re: Options for large transactions

In article <0EsoNDAnE+A8EwvN_at_warcraft.demon.co.uk>, Adrian says...
>
>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.
>

Well, I think if you are getting the 1555 and the only cursor that stays open is on table1 -- and you are getting the 1555 on table1 -- putting it in a read only tablespace *won't help*

See
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:895410916429 for an example of why.

see
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:275215756923 and read case 2 -- that example above is an example of case 2.

Do you load up table1 with lots of data -- and then process it?

Tell us more about HOW this process works. If you load up table1 with conventional inserts (say a big bulk load) we might have to "clean out" that table.

What cursor is getting the error exactly? Against what table?

>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

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