pl/sql tables memory usage

Hi all,

I've tried scouring the groups for more info on this but i couldnt' find anything too useful and decided to post after a while. please feel free to direct me to another thread if you know of one.

intro: am a novice user to pl/sql. i believe we're running what appears to be oracle 8.1.7.

we're running a large batch process that tries to keep track of information in a pl/sql table. the table's index is unique ID to a user and we use the table to save us the time of having to requery the information each time for that user during the batch process.

the problem i'm running into is that somewhere, after a bit of running it, we get the "ORA-04030: out of process memory when trying to allocate..." error. this would happen during an insert statement which kinda makes me think the rollback space is tied to the space used by the pl/sql index-by tables. i have to believe it's the tables because emptying out the table every now and then (eg. everytime it gets to a certain size) seems to alleviate this issue.

so i was wondering how to monitor the memory usage of this table. i'm hoping to get two bits of info:
- how much memory i have left to use before i'm likely to get the out
of memory exception
- how much memory it is using

using this info i was hoping to monitor the memory while the process was running, and manage the size of the table to avoid the exception.

any advice would greatly be appreciated. thanks. Received on Fri Feb 21 2003 - 20:03:46 CST