Re: new statspack install seems to hang

On Sep 26, 2:26 pm, eAddict <eAdd..._at_gmail.com> wrote:
> On Sep 26, 12:43 pm, Frank van Bortel <frank.van.bor..._at_gmail.com>
> wrote:
>
> > The error stack was quite obvious.
>
> So I am curious. Why does the command not affect it now?
>
> sapdb:oraprd 82> sqlplus '/ as sysdba'
>
> SQL*Plus: Release 9.2.0.4.0 - Production on Wed Sep 26 13:23:57 2007
> Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
>
> Connected to:
> Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
> With the Partitioning option
> JServer Release 9.2.0.4.0 - Production
>
> SQL> grant execute on dbms_shared_pool to perfstat;
>
> Grant succeeded.
>
> SQL> connect perfstat/**********
> Connected.
> SQL> exec statspack.snap;
> BEGIN statspack.snap; END;
>
> *
> ERROR at line 1:
> ORA-01013: user requested cancel of current operation
> ORA-06508: PL/SQL: could not find program unit being called
> ORA-06512: at "SYS.DBMS_SHARED_POOL", line 45
> ORA-06512: at "SYS.DBMS_SHARED_POOL", line 53
> ORA-06512: at "PERFSTAT.STATSPACK", line 2737
> ORA-06512: at line 1

I don't think you have a permissions issue. You issued a Cntrl-C when the statspack.snap package was running which was in the middle of executing a dbms_shared_pool call. Thats what the error stack indicates.
The thing is that the dbms_shared_pool call should have completed instantaneously ..
which in your case didn't.
Leads me to believe that your issue is related to invalid objects. Either dbms_shared_pool package is marked invalid .. and/or its dependent
objects are.

As a first step run utlrp.sql while connected as sysdba. If that does not fix it, list the objects (non-user) which are marked invalid.

Anurag Received on Wed Sep 26 2007 - 15:35:33 CDT