I am having problems with dbms_stats.gather_schema_stats with cascade option on Aix. The exact command is "exec dbms_stats.gather_schema_stats(ownname=>null,estimate_percent=>dbms_stats.auto_sample_size,cascade=>true);"
I tried the above command on two AIX machines with AIX 5.2. Both failed with ora-04030. One machine has 4GB of ram with 8GB paging space. The other machine has 8GB ram with 16 GB paging space. I used topas to monitor the memory usage for the session. When the paging space for the session went up to around 120mb, then, it failed. The two databases I used are one of those ERP databases with tons of tables and indexes. The PGA_AGGREGATE_TARGET does not seem to have any thing to do with the failure. I have tried 50M and 300M. Each time it failed at 120M. I also ran this query:
v$statname n,v$sesstat s
n.STATISTIC# = s.STATISTIC# and
name like 'session%memory%'
order by 3 desc
session pga max is right round 120m.
For some reason, Oracle just won't allocate more memory any more even though both machine have enough paging space.(one machine still has 50% paging space left, the other has 33% left).
Then, I ran the same command on a win2k server. It worked. The PGA went up to around 260MB. the oracle.exe is around 410mb, that is SGA+PGA.
Oracle version is 126.96.36.199. So, Anybody has any idea about what is going on with AIX. Here is the ulimit.
I faced similar issue - not with dbms_stats but, a session terminating due to ORA-04030 and nothing was looking out of place. I monitored the pga_used_mem from V$process for the sessions SPID and when that was reaching 1G, it was failing. After the bug-fix patch was applied (which is also part of 9206), the query completed successfully and I could also notice that the pga_used_mem did go beyond 1G.
I know you have a different OS, but may be this is just 9205 issue. Give it a try...