OraFAQ Forum: Server Administration » Invalid Object

Home » RDBMS Server » Server Administration » Invalid Object - PRVT_ADVISOR (Oracle 10g x64 RAC, 10.2.0.4, Windows 2003 R2 x64)

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

Invalid Object - PRVT_ADVISOR [message #432742]

Thu, 26 November 2009 00:27

panco
Messages: 2
Registered: November 2009
Location: Macau

Junior Member

I found an invalid system object in my production. It is a package called PRVT_ADVISOR. I tried to drop this package and execute the prvtadv.plb to rebuild it; however, it still show the same error message. Could any one help?

SQL> select object_type, owner, object_name from dba_objects where status = 'INVALID'
OBJECT_TYPE     OWNER           OBJECT_NAME
--------------- --------------- ----------------------------------------
PACKAGE BODY    SYS             PRVT_ADVISOR

SQL> alter package PRVT_ADVISOR compile;

Warning: Package altered with compilation errors.

SQL> show error package body prvt_advisor;
Errors for PACKAGE BODY PRVT_ADVISOR:

LINE/COL ERROR
-------- -----------------------------------------------------------------
976/7    PL/SQL: SQL Statement ignored
977/27   PL/SQL: ORA-00904: "TYPE": invalid identifier
4546/5   PL/SQL: SQL Statement ignored
4546/12  PL/SQL: ORA-00904: "B"."TYPE": invalid identifier
4569/5   PL/SQL: SQL Statement ignored
4569/17  PL/SQL: ORA-00904: "A"."TYPE": invalid identifier




Error message in log files:

------------------------ bdump/alert_mydb1.log ------------------------
Thu Nov 26 14:00:45 2009
Errors in file d:\oracle\product\10.2.0\admin\mydb\bdump\mydb1_m001_7960.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-04063: package body "SYS.PRVT_ADVISOR" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.PRVT_ADVISOR"
ORA-06512: at "SYS.PRVT_HDM", line 112
ORA-04063: package body "SYS.PRVT_ADVISOR" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.PRVT_ADVISOR"
ORA-06512: at line 1


------------------------ bdump/mydb1_m001_7960.trc ------------------------
Dump file d:\oracle\product\10.2.0\admin\mydb\bdump\mydb1_m001_7960.trc
Wed Nov 25 09:00:44 2009
ORACLE V10.2.0.4.0 - 64bit Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Windows NT Version V5.2 Service Pack 2
CPU                 : 16 - type 8664, 4 Physical Cores
Process Affinity    : 0x0000000000000000
Memory (Avail/Total): Ph:679M/16380M, Ph+PgF:3078M/19865M
Instance name: mydb1

Redo thread mounted by this instance: 1

Oracle process number: 55

Windows thread id: 7960, image: ORACLE.EXE (m001)


*** ACTION NAME:(Auto ADDM Slave Action) 2009-11-25 09:00:44.669
*** MODULE NAME:(MMON_SLAVE) 2009-11-25 09:00:44.669
*** SERVICE NAME:(SYS$BACKGROUND) 2009-11-25 09:00:44.669
*** SESSION ID:(505.16767) 2009-11-25 09:00:44.669
KSV 604 error in slave process
*** 2009-11-25 09:00:44.685
ORA-00604: error occurred at recursive SQL level 1
ORA-04063: package body "SYS.PRVT_ADVISOR" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.PRVT_ADVISOR"
ORA-06512: at "SYS.PRVT_HDM", line 112
ORA-04063: package body "SYS.PRVT_ADVISOR" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.PRVT_ADVISOR"
ORA-06512: at line 1
OPIRIP: Uncaught error 447. Error stack:
ORA-00447: fatal error in background process
ORA-00604: error occurred at recursive SQL level 1
ORA-04063: package body "SYS.PRVT_ADVISOR" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.PRVT_ADVISOR"
ORA-06512: at "SYS.PRVT_HDM", line 112
ORA-04063: package body "SYS.PRVT_ADVISOR" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.PRVT_ADVISOR"
ORA-06512: at line 1
Dump file d:\oracle\product\10.2.0\admin\mydb\bdump\mydb1_m001_7960.trc
Thu Nov 26 05:00:07 2009
ORACLE V10.2.0.4.0 - 64bit Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Windows NT Version V5.2 Service Pack 2
CPU                 : 16 - type 8664, 4 Physical Cores
Process Affinity    : 0x0000000000000000
Memory (Avail/Total): Ph:655M/16380M, Ph+PgF:3181M/19865M
Instance name: mydb1

Redo thread mounted by this instance: 1

Oracle process number: 56

Windows thread id: 7960, image: ORACLE.EXE (m001)


*** ACTION NAME:(Auto ADDM Slave Action) 2009-11-26 05:00:07.667
*** MODULE NAME:(MMON_SLAVE) 2009-11-26 05:00:07.667
*** SERVICE NAME:(SYS$BACKGROUND) 2009-11-26 05:00:07.667
*** SESSION ID:(519.6384) 2009-11-26 05:00:07.667
KSV 604 error in slave process
*** 2009-11-26 05:00:07.714
ORA-00604: error occurred at recursive SQL level 1
ORA-04063: package body "SYS.PRVT_ADVISOR" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.PRVT_ADVISOR"
ORA-06512: at "SYS.PRVT_HDM", line 112
ORA-04063: package body "SYS.PRVT_ADVISOR" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.PRVT_ADVISOR"
ORA-06512: at line 1
OPIRIP: Uncaught error 447. Error stack:
ORA-00447: fatal error in background process
ORA-00604: error occurred at recursive SQL level 1
ORA-04063: package body "SYS.PRVT_ADVISOR" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.PRVT_ADVISOR"
ORA-06512: at "SYS.PRVT_HDM", line 112
ORA-04063: package body "SYS.PRVT_ADVISOR" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.PRVT_ADVISOR"
ORA-06512: at line 1
Dump file d:\oracle\product\10.2.0\admin\mydb\bdump\mydb1_m001_7960.trc
Thu Nov 26 14:00:45 2009
ORACLE V10.2.0.4.0 - 64bit Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Windows NT Version V5.2 Service Pack 2
CPU                 : 16 - type 8664, 4 Physical Cores
Process Affinity    : 0x0000000000000000
Memory (Avail/Total): Ph:4120M/16380M, Ph+PgF:2116M/19865M
Instance name: mydb1

Redo thread mounted by this instance: 1

Oracle process number: 64

Windows thread id: 7960, image: ORACLE.EXE (m001)


*** ACTION NAME:(Auto ADDM Slave Action) 2009-11-26 14:00:45.022
*** MODULE NAME:(MMON_SLAVE) 2009-11-26 14:00:45.022
*** SERVICE NAME:(SYS$BACKGROUND) 2009-11-26 14:00:45.022
*** SESSION ID:(505.10890) 2009-11-26 14:00:45.022
KSV 604 error in slave process
*** 2009-11-26 14:00:45.053
ORA-00604: error occurred at recursive SQL level 1
ORA-04063: package body "SYS.PRVT_ADVISOR" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.PRVT_ADVISOR"
ORA-06512: at "SYS.PRVT_HDM", line 112
ORA-04063: package body "SYS.PRVT_ADVISOR" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.PRVT_ADVISOR"
ORA-06512: at line 1
OPIRIP: Uncaught error 447. Error stack:
ORA-00447: fatal error in background process
ORA-00604: error occurred at recursive SQL level 1
ORA-04063: package body "SYS.PRVT_ADVISOR" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.PRVT_ADVISOR"
ORA-06512: at "SYS.PRVT_HDM", line 112
ORA-04063: package body "SYS.PRVT_ADVISOR" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.PRVT_ADVISOR"
ORA-06512: at line 1

Report message to a moderator

Re: Invalid Object - PRVT_ADVISOR [message #432745 is a reply to message #432742]

Thu, 26 November 2009 00:52

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

Senior Member
Account Moderator

This is not a standard package but one of yours. So only YOU can fix the errors.
Do NOT store your packages in SYS schema (unless there is a very good reason).

Regards
Michel

Report message to a moderator

Re: Invalid Object - PRVT_ADVISOR [message #432748 is a reply to message #432745]

Thu, 26 November 2009 00:57

ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

Michel Cadot wrote on Thu, 26 November 2009 12:22

This is not a standard package but one of yours. So only YOU can fix the errors.
Do NOT store your packages in SYS schema (unless there is a very good reason).

Regards
Michel

Michel it is a built in package is n`t it ?

SQL> select owner||','||object_name||','||object_type
  2  from dba_objects
  3  where object_name like 'PRVT_ADVISOR';

SYS,PRVT_ADVISOR,PACKAGE
SYS,PRVT_ADVISOR,PACKAGE BODY

SQL>

Please advice if I am wrong.

sriram Smile

Report message to a moderator

Re: Invalid Object - PRVT_ADVISOR [message #432753 is a reply to message #432748]

Thu, 26 November 2009 01:14

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

Senior Member
Account Moderator

Doh! When I check it I typed PRTV instead of PRVT. /forum/fa/1606/0/

It is used by DBMS_SQLTUNE, DBMS_ADVISOR and DBMS_UNDO_ADV.

Regards
Michel

[Updated on: Thu, 26 November 2009 01:14]

Report message to a moderator

Re: Invalid Object - PRVT_ADVISOR [message #432757 is a reply to message #432742]

Thu, 26 November 2009 01:52

ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

Actually it an undocumented version

refer this
Complete list

And i guess this is your problem

Quote:

ORA-00447: fatal error in background process

sriram

Report message to a moderator

Re: Invalid Object - PRVT_ADVISOR [message #432768 is a reply to message #432742]

Thu, 26 November 2009 03:36

panco
Messages: 2
Registered: November 2009
Location: Macau

Junior Member

Find the solution from Meta-Link.... here is the steps to "Rebuild" that package:

[Edit MC: do not post Metalink materials, it is illegal. Give Metalink note number].

[Updated on: Thu, 26 November 2009 03:58] by Moderator

Report message to a moderator

Previous Topic:	Maximum number of cursors
Next Topic:	Compilers for oracle patching

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jul 01 01:02:47 CDT 2024