ORA-20200: Database/Instance does not exist in STATS$DATABASE_INSTANCE

When attempting to run a Statspack instance report using spreport.sql the following error is generated.

SQL> @spreport.sql

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

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 1253224498 ORCL                1 orcl



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

Using 1253224498 for database Id
Using          1 for instance number
declare
*
ERROR at line 1:
ORA-20200: Database/Instance 1253224498/1 does not exist in
STATS$DATABASE_INSTANCE
ORA-06512: at line 23


Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\app\oracle\product\11.2.0\dbhome_1\RDBMS\ADMIN>

Upon receiving the error you verify the DBID using V$DATABASE with the value in STATS$DATABASE_INSTANCE.

SQL> select dbid from v$database;

      DBID
----------
1253224498

SQL> select * from stats$database_instance;

no rows selected

SQL> 

You see that no rows are in STATS$DATABASE_INSTANCE.

This error can occur if spreport.sql is ran before any snapshots have been taken. Try creating a snapshot using execute statspack.snap and rerun spreport.sql.

If you have an automated process to collect snapshots ensure that it is running properly and executing the correct procedure.

Leave a Reply

Your email address will not be published. Required fields are marked *