OraFAQ Forum: Performance Tuning » error in stat pack

Home » RDBMS Server » Performance Tuning » error in stat pack

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

error in stat pack [message #285586]

Wed, 05 December 2007 01:51

pratik123
Messages: 41
Registered: October 2007
Location: hyderabad

Member

hi seniours

when i execute spreport its show following error

SQL> conn sys/syspwd@database as sysdba
Connected.
SQL> @E:\oracle\product\10.1.0\db_1\RDBMS\ADMIN\spreport.sql;

Current Instance
~~~~~~~~~~~~~~~~

DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
3546625012 DATABASE 1 database

Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id Inst Num DB Name Instance Host
----------- -------- ------------ ------------ ------------
3546625012 1 DATABASE database TNAP

Using 3546625012 for database Id
Using 1 for instance number

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.

Listing all Completed Snapshots

Snap
Instance DB Name Snap Id Snap Started Level Comment
------------ ------------ --------- ----------------- ----- --------------------

database DATABASE 1 05 Dec 2007 12:30 5

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1

Enter value for end_snap: 2
End Snapshot Id specified: 2

declare
*
ERROR at line 1:
ORA-20200: End Snapshot Id 2 does not exist for this database/instance
ORA-06512: at line 37

Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options

please guide where im going wrong

Report message to a moderator

Re: error in stat pack [message #285587 is a reply to message #285586]

Wed, 05 December 2007 01:53

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

ORA-20200: End Snapshot Id 2 does not exist for this database/instance

for statspack report you need minimum two snapshot here in your case you don't have snapshot "2".

Use AWR report instead of statspack.
just run "awrrpt.sql" scripts for create AWR report in HTML/TEXT.

Located: $ORACLE_HOME/rdbms/admin

[Updated on: Wed, 05 December 2007 01:57]

Report message to a moderator

Re: error in stat pack [message #285589 is a reply to message #285586]

Wed, 05 December 2007 02:00

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Statspack is owned by PERFSTAT (or the like) not by SYS.

Use the correct user.

- Never ever use sys but for maintenance purpose (startup, shutdown, backup, recover)
- sys is special
- sys is Oracle proprietary (try to open a SR/TAR starting with "i did that with sys" and you'll see the immediate answer)
- sys does not act like any other user
- When you use sys Oracle deactivates some code path and activates others
- Whatever you do with sys will neither validate nor invalidate the same thing with any other user.

NEVER EVER use sys for anything that can be done by another user.
Use sys ONLY for something that can't be done by someone else.

Regards
Michel

Report message to a moderator