-
statspack issue
Guys,
I executed spcreate.sql and perfstat user get created.
Then I logged in as perfstat user and tried to execute
SQL> EXECUTE statspack.snap;
I am getting the follwing error
BEGIN statspack.snap; END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'STATSPACK.SNAP' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
can anybody help me!!
sumit
-
I think ur statspack user i.e. prefstat, don't have suffecient privilage
vishal sood
OCP 8
-
Just to verify , i have granted DBA privileges to perfstat user. Still getting error.
sumit
-
Try again after running spcpkg.sql
vishal sood
OCP 8
-
You may be trying to execute snapshot.snap procedure other than prefstat user/schema. it should be run only in prefstat schema. when you install statspack it creates prefstat user and grants necessary privileges to this user
J Gangadhar
-
Vishal,
I am getting error while running spcpkg.sql
SQL> @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
-------- -----------------------------------------------------------------
291/12 PL/SQL: SQL Statement ignored
329/19 PL/SQL: ORA-00942: table or view does not exist
335/14 PL/SQL: SQL Statement ignored
335/26 PL/SQL: ORA-00942: table or view does not exist
431/12 PL/SQL: SQL Statement ignored
431/19 PL/SQL: ORA-00942: table or view does not exist
494/16 PL/SQL: SQL Statement ignored
494/28 PL/SQL: ORA-00942: table or view does not exist
671/13 PL/SQL: SQL Statement ignored
672/20 PL/SQL: ORA-00942: table or view does not exist
713/13 PL/SQL: SQL Statement ignored
LINE/COL ERROR
-------- -----------------------------------------------------------------
714/20 PL/SQL: ORA-00942: table or view does not exist
744/13 PL/SQL: SQL Statement ignored
745/20 PL/SQL: ORA-00942: table or view does not exist
775/13 PL/SQL: SQL Statement ignored
776/20 PL/SQL: ORA-00942: table or view does not exist
814/13 PL/SQL: SQL Statement ignored
815/20 PL/SQL: ORA-00942: table or view does not exist
853/13 PL/SQL: SQL Statement ignored
854/20 PL/SQL: ORA-00942: table or view does not exist
NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.
SQL>
sumit
-
329/19 PL/SQL: ORA-00942: table or view does not exist
As mentioned in Oracle Documentation for ORA-00942:
ORA-00942 table or view does not exist
Cause: The table or view entered does not exist, a synonym that is not allowed here was used, or a view was referenced where a table is required. Existing user tables and views can be listed by querying the data dictionary. Certain privileges may be required to access the table. If an application returned this message, the table the application tried to access does not exist in the database, or the application does not have access to it.
Action: Check each of the following:
the spelling of the table or view name.
that a view is not specified where a table is required.
that an existing table or view name exists.
Contact the database administrator if the table needs to be created or if user or application privileges are required to access the table.
Also, if attempting to access a table or view in another schema, make certain the correct schema is referenced and that access to the object is granted.
vishal sood
OCP 8
-
obviously you are missing all these grants, i.e you have not created perfstat properly
Code:
pcusr.sql:grant create session to PERFSTAT;
spcusr.sql:grant alter session to PERFSTAT;
spcusr.sql:grant create table to PERFSTAT;
spcusr.sql:grant create procedure to PERFSTAT;
spcusr.sql:grant create sequence to PERFSTAT;
spcusr.sql:grant create public synonym to PERFSTAT;
spcusr.sql:grant drop public synonym to PERFSTAT;
spcusr.sql:grant select on STATS$X_$KCBFWAIT to PERFSTAT;
spcusr.sql:grant select on STATS$X_$KSPPSV to PERFSTAT;
spcusr.sql:grant select on STATS$X_$KSPPI to PERFSTAT;
spcusr.sql:grant select on STATS$V_$FILESTATXS to PERFSTAT;
spcusr.sql:grant select on STATS$V_$TEMPSTATXS to PERFSTAT;
spcusr.sql:grant select on STATS$V_$SQLXS to PERFSTAT;
spcusr.sql:grant SELECT_CATALOG_ROLE to PERFSTAT;
spcusr.sql:grant select on V_$PARAMETER to PERFSTAT;
spcusr.sql:grant select on V_$SYSTEM_PARAMETER to PERFSTAT;
spcusr.sql:grant select on V_$DATABASE to PERFSTAT;
spcusr.sql:grant select on V_$INSTANCE to PERFSTAT;
spcusr.sql:grant select on V_$LIBRARYCACHE to PERFSTAT;
spcusr.sql:grant select on V_$LATCH to PERFSTAT;
spcusr.sql:grant select on V_$LATCH_MISSES to PERFSTAT;
spcusr.sql:grant select on V_$LATCH_CHILDREN to PERFSTAT;
spcusr.sql:grant select on V_$LATCH_PARENT to PERFSTAT;
spcusr.sql:grant select on V_$ROLLSTAT to PERFSTAT;
spcusr.sql:grant select on V_$ROWCACHE to PERFSTAT;
spcusr.sql:grant select on V_$SGA to PERFSTAT;
spcusr.sql:grant select on V_$BUFFER_POOL to PERFSTAT;
spcusr.sql:grant select on V_$SGASTAT to PERFSTAT;
spcusr.sql:grant select on V_$SYSTEM_EVENT to PERFSTAT;
spcusr.sql:grant select on V_$SESSION to PERFSTAT;
spcusr.sql:grant select on V_$SESSION_EVENT to PERFSTAT;
spcusr.sql:grant select on V_$SYSSTAT to PERFSTAT;
spcusr.sql:grant select on V_$WAITSTAT to PERFSTAT;
spcusr.sql:grant select on V_$ENQUEUE_STAT to PERFSTAT;
spcusr.sql:grant select on V_$SQLAREA to PERFSTAT;
spcusr.sql:grant select on V_$SQL to PERFSTAT;
spcusr.sql:grant select on V_$SQLTEXT to PERFSTAT;
spcusr.sql:grant select on V_$SESSTAT to PERFSTAT;
spcusr.sql:grant select on V_$BUFFER_POOL_STATISTICS to PERFSTAT;
spcusr.sql:grant select on V_$RESOURCE_LIMIT to PERFSTAT;
spcusr.sql:grant select on V_$DLM_MISC to PERFSTAT;
spcusr.sql:grant select on V_$UNDOSTAT to PERFSTAT;
spcusr.sql:grant select on V_$SQL_PLAN to PERFSTAT;
spcusr.sql:grant select on V_$DB_CACHE_ADVICE to PERFSTAT;
spcusr.sql:grant select on V_$PGASTAT to PERFSTAT;
spcusr.sql:grant select on V_$INSTANCE_RECOVERY to PERFSTAT;
spcusr.sql:grant select on V_$SHARED_POOL_ADVICE to PERFSTAT;
spcusr.sql:grant select on V_$SQL_WORKAREA_HISTOGRAM to PERFSTAT;
spcusr.sql:grant select on V_$PGA_TARGET_ADVICE to PERFSTAT;
spcusr.sql:grant select on V_$SEGSTAT to PERFSTAT;
spcusr.sql:grant select on V_$SEGMENT_STATISTICS to PERFSTAT;
spcusr.sql:grant select on V_$SEGSTAT_NAME to PERFSTAT;
spcusr.sql:grant execute on DBMS_SHARED_POOL to PERFSTAT;
spcusr.sql:grant execute on DBMS_JOB to PERFSTAT;
-
try to run: spdrop.sql and then spcreate.sql as
sysdba
-
I've also found that if you want to reinstall statspack you need to comment out whenever sqlerror exit; out of spcusr.sql (called by spcreate.sql) because the drop script doesnt drop the x$ views and synonyms so falls over when you try and re-create them
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|