Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
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