Showing results for 
Search instead for 
Did you mean: 

Oracle memory problems

Occasional Visitor

Oracle memory problems

Hi guys,

I have RedHat 9 workstation with Oracle DB 9.2.

I would like to execute a huge sql statement which size is larger then 32K. That's why I use a varchar2s variable to store the statement. The "where" clause of the statement is generated dinamically from the result array:

>create_stmt dbms_sql.varchar2s;
>create_stmt(1) := 'select count(*) from test where ';
>FOR i in 1 .. result.count LOOP
> create_stmt(i + 1):= result(i);
>cnum := dbms_sql.open_cursor;
>dbms_sql.parse(cnum, create_stmt, 1, result.count + 1, NULL , dbms_sql.native);
>nrows := dbms_sql.execute(cnum);

When the size of the result array is too big I get the following error:

>select count(*) from test where .....
>ERROR at line 1:
>ORA-29902: error in executing ODCIIndexStart() routine
>ORA-04031: unable to allocate 68 bytes of shared memory ("shared pool","select >con#,obj#,rcon#,enabl...","sql area","strdef : prsstr")
>ORA-06512: at "SYS.DBMS_SYS_SQL", line 1485
>ORA-06512: at "SYS.DBMS_SQL", line 26
>ORA-06512: at "SCOTT.JC_IDXTYPE_IM", line 263
>ORA-06512: at line 1

I increased the shared_pool_size of my database. Now I get another error:

> ORA-03113: "end-of-file on communication channel"

Do you know what couse these errors?

Graham Cameron_1
Honored Contributor

Re: Oracle memory problems

ORA-03113 means that either someone took down the database or killed your server process.

ORA-04031 means you need more shared pool as you have found.

In any case, 32K is a pretty big chunk of SQL. How about storing your result() data in a temporary table, and using "select .. where exists (select ..", or a correlated subquery?

-- Graham
Computers make it easier to do a lot of things, but most of the things they make it easier to do don't need to be done.
Steven E. Protter
Exalted Contributor

Re: Oracle memory problems

You need to make sure that there is enough shared memory on the Linux box and that the init.ora file for that database allocates enough shared memory.

Reboot after changes and try again.

Steven E Protter
Owner of ISN Corporation