Oracle memory problems

[Fifty]

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);
>END LOOP;
>
>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?

thanks,
fifty

[Graham Cameron_1]

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

[Steven E. Protter]

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.

SEP