Hi,

I'm seeking some advice. Basically, one of our developers has written
an insert-select statement within a procedure. When the statement is
extracted from the procedure and run in sqlplus it executes in about 30
secs (populated table).

However, when the procedure itself was executed it was still running 2
days later and then generated a RBS segment too small error. During
this time the session was marked as ACTIVE and no exceptions were
thrown by the procedure.

I can't understand why running the same code in a procedure would make
such a difference.
The problem is definitely not linked to permissions.

Does anybody have any suggestions how I can start diagnosing this..

Thanks in advance.
John

Sorry mean select-insert statement.

Johne_uk wrote:
> Sorry mean select-insert statement.

And you're sure it's on the same DB with the same amount of data touched?

robert

Johne_uk wrote:
> Hi,
>
> I'm seeking some advice. Basically, one of our developers has written
> an insert-select statement within a procedure. When the statement is
> extracted from the procedure and run in sqlplus it executes in about 30
> secs (populated table).
>
> However, when the procedure itself was executed it was still running 2
> days later and then generated a RBS segment too small error. During
> this time the session was marked as ACTIVE and no exceptions were
> thrown by the procedure.
>
> I can't understand why running the same code in a procedure would make
> such a difference.
> The problem is definitely not linked to permissions.
>
> Does anybody have any suggestions how I can start diagnosing this..
>
> Thanks in advance.
> John

It doesn't ... something else is happening. Here's a simple test:

BEGIN
<your_statement_here>;
END;
/

How long does it take to run?

Also ... what version of Oracle and post the explain plan.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

Would you post the PLSQL that wraps this DML statement please? Sounds
like some type of infinite looping, etc. You are getting a ORA-01555
error right (snapshot too old)? Which version of Oracle?