Options for large transactions

From: Adrian Carlson-Hedges <adrian.ch_at_btinternet.com>
Date: Mon, 26 Nov 2001 18:33:07 -0000
Message-ID: <9tu1sf$n9d$1@plutonium.btinternet.com>

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?

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 Received on Mon Nov 26 2001 - 12:33:07 CST