Re: BULK Processing Process "Expires" without completing
Date: Tue, 22 Jan 2008 04:11:43 -0800 (PST)
Message-ID: <40596fff-91f5-41a4-81d8-347b987a0047@p69g2000hsa.googlegroups.com>
On Jan 21, 7:27 pm, "Dereck L. Dietz" <diet..._at_ameritech.net> wrote:
> > Try looking at v$sesstat from another session to see how much PGA you
> > are soaking up.
>
> > There used to be an old adage about assigning an empty plsql table was
> > better at releasing memory then xxx.delete, but I've never really tested
> > that hypothesis.
>
> I'm using nested tables and for some reason I'm not able to get the syntax
> correct for assigning an empty table to a nested table.
>
>
>
> > Can you not achieve the same with just plain old SQL, or maybe a
> > pipeline function
> > --
> > Connor McDonald
> > Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
> > Co-author: "Oracle Insight - Tales of the OakTable"
>
> > web:http://www.oracledba.co.uk
> > web:http://www.oaktable.net
> > email: connor_mcdon..._at_yahoo.com
>
> > "Semper in excremento, sole profundum qui variat."
>
> > ------------------------------------------------------------
SQL> create type my_nested_table as table of number(10); 2 /
Type created.
SQL> set serveroutput on
SQL> declare
2 my_nt my_nested_table := my_nested_table();
3 begin
4 for i in 1..100 loop
5 my_nt.extend; 6 my_nt(i) := i;
7 end loop;
8 dbms_output.put_line('array size = '||my_nt.count); 9 my_nt := my_nested_table();
10 dbms_output.put_line('array size = '||my_nt.count); 11 end;
12 /
array size = 100
array size = 0
PL/SQL procedure successfully completed.
Now, if you use PL/SQL collections, you need to declare an uninitialized collection (say, my_coll_empty,) and a working collection and assign that empty collection each time you want to free the working set.
Hth,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com
Received on Tue Jan 22 2008 - 06:11:43 CST