Database Object with INVALID status: PERFSTAT.STATSPACK

[Former Member]

Oracle version: 9.2.0.8

Activitiy: Pre-upgrade to Oracle 10.2.0.2

Hi,

I have an invalid object STATSPACK and I can't seems to get rid of it. Below are the detail steps of what I did. I wonder if anyone know how to resolve this? I can't seems to find anything on the internet, oss notes, metalink on recompiling this object except removing and reinstall statspack.

Although I think this object is not critical to the Oracle10g upgrade but I would really like to resolve all invalid objects before I start the oracle ugprade.

Points will be granted for helpful information.

Regards,

Annie Chan

Steps that I have taken:

[1] I executed below sql query:

SELECT SUBSTR(OWNER, 1, 20) OWNER,

SUBSTR(OBJECT_NAME, 1, 35) OBJECT_NAME,

SUBSTR(OBJECT_TYPE, 1, 20) OBJECT_TYPE

FROM DBA_OBJECTS

WHERE STATUS = 'INVALID';

to find out invalid objects and I am left with

OWNER OBJECT_NAME OBJECT_TYPE

-

---

-

---

PERFSTAT STATSPACK PACKAGE BODY

[2] I have tried to recompile using below query but it returns warning and the invalid object still exists.

ALTER PACKAGE PERFSTAT.STATSPACK COMPILE BODY;

[3] I executed SELECT * from DBA_ERRORS; Results:

OWNER NAME TYPE

-

---

-

---

-

---

SEQUENCE LINE POSITION

-

---

-

---

-

---

TEXT

-

---

PERFSTAT STATSPACK PACKAGE BODY

1 51 11

PL/SQL: ORA-00942: table or view does not exist

PERFSTAT STATSPACK PACKAGE BODY

2 48 4

PL/SQL: SQL Statement ignored

OWNER NAME TYPE

-

---

-

---

-

---

SEQUENCE LINE POSITION

-

---

-

---

-

---

TEXT

-

---

PERFSTAT STATSPACK PACKAGE BODY

3 55 11

PL/SQL: ORA-00942: table or view does not exist

PERFSTAT STATSPACK PACKAGE BODY

4 54 4

OWNER NAME TYPE

-

---

-

---

-

---

SEQUENCE LINE POSITION

-

---

-

---

-

---

TEXT

-

---

PL/SQL: SQL Statement ignored

PERFSTAT STATSPACK PACKAGE BODY

5 1326 14

PL/SQL: ORA-00942: table or view does not exist

PERFSTAT STATSPACK PACKAGE BODY

OWNER NAME TYPE

-

---

-

---

-

---

SEQUENCE LINE POSITION

-

---

-

---

-

---

TEXT

-

---

6 1325 7

PL/SQL: SQL Statement ignored

PERFSTAT STATSPACK PACKAGE BODY

7 1353 16

PL/SQL: ORA-00980: synonym translation is no longer valid

OWNER NAME TYPE

-

---

-

---

-

---

SEQUENCE LINE POSITION

-

---

-

---

-

---

TEXT

-

---

PERFSTAT STATSPACK PACKAGE BODY

8 1337 9

PL/SQL: SQL Statement ignored

PERFSTAT STATSPACK PACKAGE BODY

9 1413 15

PL/SQL: ORA-00980: synonym translation is no longer valid

OWNER NAME TYPE

-

---

-

---

-

---

SEQUENCE LINE POSITION

-

---

-

---

-

---

TEXT

-

---

PERFSTAT STATSPACK PACKAGE BODY

10 1361 8

PL/SQL: SQL Statement ignored

PERFSTAT STATSPACK PACKAGE BODY

11 1465 24

OWNER NAME TYPE

-

---

-

---

-

---

SEQUENCE LINE POSITION

-

---

-

---

-

---

TEXT

-

---

PL/SQL: ORA-00942: table or view does not exist

PERFSTAT STATSPACK PACKAGE BODY

12 1433 13

PL/SQL: SQL Statement ignored

PERFSTAT STATSPACK PACKAGE BODY

OWNER NAME TYPE

-

---

-

---

-

---

SEQUENCE LINE POSITION

-

---

-

---

-

---

TEXT

-

---

13 1520 17

PL/SQL: ORA-00942: table or view does not exist

PERFSTAT STATSPACK PACKAGE BODY

14 1498 10

PL/SQL: SQL Statement ignored

OWNER NAME TYPE

-

---

-

---

-

---

SEQUENCE LINE POSITION

-

---

-

---

-

---

TEXT

-

---

PERFSTAT STATSPACK PACKAGE BODY

15 1623 20

PL/SQL: ORA-00942: table or view does not exist

PERFSTAT STATSPACK PACKAGE BODY

16 1547 13

PL/SQL: SQL Statement ignored

OWNER NAME TYPE

-

---

-

---

-

---

SEQUENCE LINE POSITION

-

---

-

---

-

---

TEXT

-

---

PERFSTAT STATSPACK PACKAGE BODY

17 1809 13

PL/SQL: ORA-00980: synonym translation is no longer valid

PERFSTAT STATSPACK PACKAGE BODY

18 1777 6

OWNER NAME TYPE

-

---

-

---

-

---

SEQUENCE LINE POSITION

-

---

-

---

-

---

TEXT

-

---

PL/SQL: SQL Statement ignored

PERFSTAT STATSPACK PACKAGE BODY

19 1843 13

PL/SQL: ORA-00980: synonym translation is no longer valid

PERFSTAT STATSPACK PACKAGE BODY

OWNER NAME TYPE

-

---

-

---

-

---

SEQUENCE LINE POSITION

-

---

-

---

-

---

TEXT

-

---

20 1811 6

PL/SQL: SQL Statement ignored

20 rows selected.

[stefan_koehler · Accepted solution]

Hello Annie,

STATSPACK is not needed anymore with Oracle 10g. In Oracle 10g you have the AWR.

So you can easily uninstall statspack by running the following sql script:

> <ORACLE_HOME>/rdbms/admin/spdrop.sql

After that you can check your invalid objects again.

Regards

Stefan

[Former Member · Accepted solution]

Thanks Stefan,

I managed to remove the invalid object after I removed the statspack.

Regards,

Annie