Home » RDBMS Server » Performance Tuning » regarding statspack....
regarding statspack.... [message #115662] Wed, 13 April 2005 09:25 Go to next message
balavignesh
Messages: 180
Registered: March 2005
Senior Member
hi,

i have installed statspack successfully but while taking the snapshot using exec statspack.snap , it gives error like

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

only after executing this procedure only , the report can be taken.what to do further..?

any help is appreciated.

regards
bala
Re: regarding statspack.... [message #115685 is a reply to message #115662] Wed, 13 April 2005 11:33 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Did you try to execute this procedure as the owner of statspack (default user perfstat?
If not, grant execute on statspack to the user you are trying to execute the procedure with and either create a synonym or prefix the call with the owner of the statspack package.
(e.g. exec perfstat.statspack.snap)

hth
Re: regarding statspack.... [message #115695 is a reply to message #115685] Wed, 13 April 2005 12:15 Go to previous messageGo to next message
balavignesh
Messages: 180
Registered: March 2005
Senior Member
hi frank,

tried everything , but still blowing the errors.

what to do..?

bala
Re: regarding statspack.... [message #115732 is a reply to message #115662] Thu, 14 April 2005 01:17 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Show us what you did. Please cut and paste your results from sqlplus when you execute the following:
-- This will show us what you can see of the objects
-- Most likely there will only be a synonym visible
select owner
,      object_type 
from   all_objects 
where  object_name = 'STATSPACK'
/
-- This will show us the owner of STATSPACK in case
-- you don't have permission
select table_owner
from   all_synonyms
where  synonym_name = 'STATSPACK'
/
-- Show us who you are
show user
-- Show us the exact errormessage, just in case it
-- differs from what we expected
exec perfstat.snap



hth
Re: regarding statspack.... [message #115798 is a reply to message #115732] Thu, 14 April 2005 09:12 Go to previous messageGo to next message
balavignesh
Messages: 180
Registered: March 2005
Senior Member
hi frank,

these are the results that i got when executing...

SQL> select owner , object_type from all_objects where
object_name ='STATSPACK';

no rows selected

SQL> select table_owner from all_synonyms where synonym_name='STATSPACK';

no rows selected

SQL> show user
USER is "PERFSTAT"

SQL> exec perfstat.snap
BEGIN perfstat.snap; END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'PERFSTAT.SNAP' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


SQL> exec statspack.snap
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


regards
bala
Re: regarding statspack.... [message #115805 is a reply to message #115662] Thu, 14 April 2005 09:31 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Something must have gone wrong while installing statspack; it is not installed.
check spctab.lis and spcpkg.lis for errors. These are spoolfiles of the installation.

hth
Re: regarding statspack.... [message #115818 is a reply to message #115805] Thu, 14 April 2005 10:05 Go to previous messageGo to next message
balavignesh
Messages: 180
Registered: March 2005
Senior Member
hi frank,

this is the error from spctab.lis

create table STATS$DB_CACHE_ADVICE
*
ERROR at line 1:
ORA-01659: unable to allocate MINEXTENTS beyond 11 in tablespace STAT.


bala
Re: regarding statspack.... [message #115905 is a reply to message #115662] Fri, 15 April 2005 02:02 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
$ oerr ora 1659
01659, 00000, "unable to allocate MINEXTENTS beyond %s in tablespace %s"
// *Cause:  Failed to find sufficient contiguous space to allocate MINEXTENTS
//          for the segment being created.
// *Action: Use ALTER TABLESPACE ADD DATAFILE to add additional space to the
//          tablespace or retry with smaller value for MINEXTENTS, NEXT or
//          PCTINCREASE 


Seems your stat tablespace has too little space available. Add space to it as indicated.

hth
Re: regarding statspack.... [message #168676 is a reply to message #115662] Fri, 21 April 2006 09:54 Go to previous messageGo to next message
ramsat
Messages: 49
Registered: November 2005
Member
Hai,
I understood Ur problem, this happens when ur statspack tablespce doesnot have enough space to create objects in it.

Try the same by resizing the tablespace size to considerable say 100mB.

It will work for sure. i confirmed this by playing the same scenario.


thank U
ramesh
Re: regarding statspack.... [message #416475 is a reply to message #168676] Mon, 03 August 2009 01:09 Go to previous messageGo to next message
SameerChoudhari
Messages: 9
Registered: July 2009
Location: Bangalore
Junior Member

Hi

I am getting the same error even after clean installation. Can you please through some light on this?


SQL> select owner
2 , object_type
3 from all_objects
4 where object_name = 'STATSPACK'
5 ;

OWNER OBJECT_TYPE
------------------------------ -------------------
PUBLIC SYNONYM
PERFSTAT PACKAGE
PERFSTAT PACKAGE BODY

SQL> select table_owner
2 from all_synonyms
3 where synonym_name = 'STATSPACK'
4 ;

TABLE_OWNER
------------------------------
PERFSTAT

SQL> show user
USER is "PERFSTAT"
SQL> exec perfstat.snap
BEGIN perfstat.snap; END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'PERFSTAT.SNAP' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored



----------------------------------------------------------------
SPCPKG File Dump
----------------------------------------------------------------

Creating Package STATSPACK...

Package created.

No errors.
Creating Package Body STATSPACK...

Package body created.

No errors.

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

----------------------------------------------------------------

Cheers
Sameer
Re: regarding statspack.... [message #416481 is a reply to message #416475] Mon, 03 August 2009 01:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68658
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
exec perfstat.snap

Quote:
Creating Package STATSPACK

Regards
Michel
Re: regarding statspack.... [message #416484 is a reply to message #416481] Mon, 03 August 2009 01:45 Go to previous messageGo to next message
SameerChoudhari
Messages: 9
Registered: July 2009
Location: Bangalore
Junior Member

SQL> exec perfstat.snap
BEGIN perfstat.snap; END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'PERFSTAT.SNAP' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


SQL>

I don't understand second QUOTE!:-<
Re: regarding statspack.... [message #416495 is a reply to message #416484] Mon, 03 August 2009 01:57 Go to previous message
SameerChoudhari
Messages: 9
Registered: July 2009
Location: Bangalore
Junior Member

I wrongly typed statpack.snap insted of statspack.snap



Now , I am able to execute without any error:


SQL> exec statspack.snap;

PL/SQL procedure successfully completed.

SQL>


Previous Topic: Oracle Performance Issue with dbms_stats.gather_table_stats
Next Topic: Selection/Updation from a table based on condition
Goto Forum:
  


Current Time: Tue Jun 04 06:16:30 CDT 2024