Re: Why is UGA size so large?

Noons <wizofoz2k_at_yahoo.com.au> wrote in message news:<4253dfb8$0$13932

> Not quite. IIRC (going back a while now, but it's hidden somewhere in
> the PL/SQL manual) what you have to do is define an empty table.
> One that you don't ever extend. Then when it comes time to
> resize to zero the one you work with, you simply assign the empty
> one to it. As in:
> work_table := empty_table;
> No indexes. This resets the work table to empty in one go, rather
> than the more conventional:
> for i in work_table.first..work_table.last loop
> work_table[i].delete;
> end loop;

What you mention is a way to delete all rows of a nested table. This can nowadays be done very easily using work_table.delete;

What I nedd is a way to release memory that stays allocated for a table that was large, but has now become very sparsely populated. My solution is to copy
the rows to a temporary table, then releasing all memory of my working table, and then copy the rows back to my working table. For nested tables this will change the indexes of the rows, for associated tables it will even converse the index. I will post an example as soon as I find the time to write it.

Regards,
Marcel Received on Thu Apr 07 2005 - 09:21:05 CDT