Re: How to tune SQL to avoid ORA-03232 ?

Jan

The 00054 error is resource busy - suggests that one or more rows in the table are locked. Check for locks and blocked sessions.

As for the plan table containing 50 rows but producing masses of text - this sounds like the issue Billy mentions about the code used to extract the data. Sort the locks out first to be sure then use some SQL such as

select lpad(' ',level) || operation ||decode(options,null,null,' - ')|| options, object_name
from plan_table
where statement_id = '&stmt'
start with id = 0
connect by prior id = parent_id;

Run this in SQL*Plus or newer versions of TOAD. Enter the statement_id when prompted for the value of stmt.

Andy

"Jan Gelbrich" <j_gelbrich_at_westfalen-blatt.de> wrote in message news:aojifn$n3rr9$1_at_ID-152732.news.dfncis.de...
>
> ...
> > I always run delete plan_table before an explain plan.
>
> Hi, Niall, that is just the strange thing that I observe now: PLAN_TABLE
> contains just 50 rows
> (but when I see the weird plan it lists an incredible bunch of text)
>
> but DELETE FROM PLAN_TABLE takes forever ... TRUNCATE TABLE is impossible
> (ORA-00054), I cant even drop it.
> What is going on here ...
>
> Has anybody observed such a strange thing before ? I never did...
>
> Yours, Jan
>
> >
> >
> > --
> > Niall Litchfield
> > Oracle DBA
> > Audit Commission UK
> > *****************************************
> > Please include version and platform
> > and SQL where applicable
> > It makes life easier and increases the
> > likelihood of a good answer
> >
> > ******************************************
> >
> >
>
>
Received on Wed Oct 16 2002 - 07:20:42 CDT