Re: A question about ORA-04031 error

From: Marco Blanco <mblanco_at_performance2000.net>
Date: Mon, 16 Sep 2002 18:44:47 +0200
Message-ID: <3d860c0e$1_4@news.arrakis.es>

"Stan Brown" <stanb_at_panix.com> wrote in message news:am4jbd$k8h$2_at_reader1.panix.com...
> I've got a 7.3.4.5 instance running on HP-UX 10.20. It's been up and
running
> for several months now without problems.
>
> However now one of the processes that manipulates it, specificly a perl
DBI
> script that trims older records out of it has started returning:
>
> +/opt/local/bin/trim_tables line 448.
> DBD::Oracle::st execute failed: ORA-04031: unable to allocate 42000 bytes
of
> +shared memory ("unknown object","cursor work he","sort merge buffer")
(DBD:
> +oexfet error) at /opt/local/bin/trim_tables line 508.
>
> I've looked at the machine that this instance runs on, and it has plenty
of
> free swap space.
>
> Any ideas where to start looking for the culprit on this?
>
>
>
> --
> "They that would give up essential liberty for temporary safety deserve
> neither liberty nor safety."
> -- Benjamin Franklin

May be your process is using unsharable SQL. This means that your process is parsing a lot of different querys. You should avoid to give parameters to a query by literal. For example...

select * from table where key=1
select * from table where key=2
select * from table where key=3
select * from table where key=4

....
select * from table where key=n

If you use this way to pass parameters you could find there is no enough shared space to store hundreds of this sentencences.

Try ...
select * from table where key=:parameter

then you can set :parameter and run the query.

In this way the SQL is shared and Oracle only stores one copy in the SGA. Received on Mon Sep 16 2002 - 11:44:47 CDT