STATSPACK CONFIGURATION

[marist89]

Are you the perfstat user? Can you see the package? Does the package have the snap method?

[sambavan]

       sqlplus perfstat@servicename

        SQL> select object_name, object_type, status
                 from user_objects;

Make sure the status is valid, if not I would suggest that you drop all and recreate everything as per the docs again and try.

Sam

[ashley75]

Sam and Jeff,

I am sorry to come back with this thread again b/c I still can't get this work.

this is what I did and please correct me if I did something wrong:

1. log in as INTERNAL/MANAGER
2. Run the SPDROP.SQL since I already run the SPCREATE.SQL
3. I am still log in as INTERNAL/MANAGER and run SPCREATE.SQL
4. login as PERFSTAT/PERFSTAT
5. Run the SPCPKG.SQL: after running this query the packages and packages body were created;however, packages body is invalid. I tried manually compile this package body and it won't compile.

I read the doc but it doesn't help. I tried couple time and I still have problem.

Please advise

[marist89]

What's the error when you try to compile the packages?

[ashley75]

Jeff,

Below is the message I got when I run the spcpkg.sql


SQL> @c:\oracle\ora817\rdbms\admin\spcpkg.sql
Creating Package STATSPACK...

Package created.

No errors.
Creating Package Body STATSPACK...

Warning: Package Body created with compilation errors.

Errors for PACKAGE BODY STATSPACK:

LINE/COL ERROR
-------- -----------------------------------------------------------------
219/12 PL/SQL: SQL Statement ignored
243/19 PLS-00201: identifier 'STATS$STATSPACK_PARAMETER' must be
declared

248/14 PL/SQL: SQL Statement ignored
248/26 PLS-00201: identifier 'STATS$STATSPACK_PARAMETER' must be
declared

313/12 PL/SQL: SQL Statement ignored
313/19 PLS-00201: identifier 'STATS$STATSPACK_PARAMETER' must be
declared

LINE/COL ERROR
-------- -----------------------------------------------------------------

354/16 PL/SQL: SQL Statement ignored
354/28 PLS-00201: identifier 'STATS$STATSPACK_PARAMETER' must be
declared

472/13 PL/SQL: SQL Statement ignored
472/20 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

472/20 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

LINE/COL ERROR
-------- -----------------------------------------------------------------

473/20 PLS-00201: identifier 'STATS$LIBRARYCACHE' must be declared
486/10 PL/SQL: SQL Statement ignored
493/10 PL/SQL: SQL Statement ignored
514/13 PL/SQL: SQL Statement ignored
514/20 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

515/20 PLS-00201: identifier 'STATS$PARAMETER' must be declared
526/10 PL/SQL: SQL Statement ignored
545/13 PL/SQL: SQL Statement ignored

LINE/COL ERROR
-------- -----------------------------------------------------------------
546/20 PLS-00201: identifier 'STATS$SYSSTAT' must be declared

NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.

I look into the package body and it's invalid

Please help and sorry about taking too much of your time on this

[ashley75]

JEFF AND SAM

PLEASE ADVISE

[marist89]

Originally posted by ashley75
LINE/COL ERROR
-------- -----------------------------------------------------------------
219/12 PL/SQL: SQL Statement ignored
243/19 PLS-00201: identifier 'STATS$STATSPACK_PARAMETER' must be
declared

248/14 PL/SQL: SQL Statement ignored
248/26 PLS-00201: identifier 'STATS$STATSPACK_PARAMETER' must be
declared

313/12 PL/SQL: SQL Statement ignored

These errors are telling you that the objects are either not created or you don't have permission on them. Post the output of sambavan's query from above...

[ashley75]

jEFF,

Here is the result:



OBJECT_NAME OBJECT_TYPE STATUS
STATSPACK PACKAGE VALID
STATSPACK PACKAGE BODY INVALID



As I mentioned before, the package body is invalid. Based on my previous post, did you seee anything I did wrong ??? If not why the package body is invalid

Thanks Jeff

[marist89]

Here is the problem. Whoever owns the statspack package doesn't own the tables. The PERFSTAT user must own both the package AND the tables.

To verify, run:

select owner, object_name, object_type
from dba_objects
where object_name like 'STATS%'
order by owner, object_name

[ashley75]

Jeff,

Here is the resutl I got



OWNER OBJECT_NAME OBJECT_TYPE
PERFSTAT STATSPACK PACKAGE
PERFSTAT STATSPACK PACKAGE BODY


There are no tables in here