11/10: Ssdba Hash Insert In An Anonymous Block Causes Ora-4030 (Doc ID 293128.1)

Last updated on APRIL 10, 2025

Applies to:

Symptoms

When running the following script, the customer gets ORA-4030 errors after about 10 minutes.

NOTE: The script is a way to monitor session activity for help in performance tuning analysis.

#!/usr/bin/ksh
. /u01/app/oracle/product/8.1.7/PO01.env
sqlplus -s ssdba/ssdba <<__1
alter session set sort_area_size = 30144000 ;
declare
begin
for i in 1..8000
loop
ssdba_hash_insert ;
commit;
end loop;
end;
/
__1
exit;
!`
cat $TEMP_LOG >> $LOGFILE

ERROR at line 1:
ORA-04030: out of process memory when trying to allocate 8192 bytes (sort
subheap,sort key)
ORA-06512: at "SSDBA.SSDBA_HASH_INSERT", line 3
ORA-06512: at line 5

SORT_AREA_SIZE changes do not affect the outcome when running this via the OS job. This PL/SQL loop runs fine within SQL*Plus (runs process for two to three hours and kills session--as planned).

Changes

Only a problem when running the script from an OS job.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.