This discussion is archived
5 Replies Latest reply: Mar 5, 2013 11:03 AM by Balazs Papp RSS

ORA-04063: package body SYS.DBMS_SQLTUNE has errors

963152 Newbie
Currently Being Moderated
Hi All,

After upgrading my database from 11.2.0.2 to 11.2.0.3, I am getting following error, can some one please help me on this as soon as possible.

ERROR at line 1:
ORA-04063: package body SYS.DBMS_SQLTUNE has errors
ORA-06508: PL/SQL: could not find program unit being called: SYS.DBMS_SQLTUNE
ORA-06512: at SYS.UTL_RECOMP, line 829
ORA-06512: at line 4

Regards,
960149
  • 1. Re: ORA-04063: package body SYS.DBMS_SQLTUNE has errors
    963152 Newbie
    Currently Being Moderated
    Please see the below valid and invalid dba_registry

    SQL> select comp_name||' '||status from dba_registry;

    COMP_NAME||''||STATUS
    --------------------------------------------------------------------------------
    Oracle Application Express VALID
    Oracle XML Database INVALID
    Oracle Text VALID
    Oracle Expression Filter VALID
    Oracle Rules Manager VALID
    Oracle Workspace Manager VALID
    Oracle Database Catalog Views VALID
    Oracle Database Packages and Types INVALID
    JServer JAVA Virtual Machine VALID
    Oracle XDK VALID
    Oracle Database Java Packages VALID

    11 rows selected.


    In this list Oracle XML Database INVALID and
    Oracle Database Packages and Types INVALID.
  • 2. Re: ORA-04063: package body SYS.DBMS_SQLTUNE has errors
    Balazs Papp Expert
    Currently Being Moderated
    select owner, synonym_name from dba_synonyms where synonym_name = 'XMLCONCAT';

    if the XMLCONCAT PUBLIC synonym exists:

    drop public synonym XMLCONCAT;
    alter package DBMS_SQLTUNE_INTERNAL compile body;
    @?/rdbms/admin/utlrp

    check again the status
  • 3. Re: ORA-04063: package body SYS.DBMS_SQLTUNE has errors
    963152 Newbie
    Currently Being Moderated
    Hi Papp,

    Thanks a lot, now all the objects are valid,

    SQL> select comp_name||''||status from dba_registry;

    COMP_NAME||''||STATUS
    --------------------------------------------------------------------------------
    Oracle XML DatabaseVALID
    Oracle Application ExpressVALID
    Oracle TextVALID
    Oracle Expression FilterVALID
    Oracle Rules ManagerVALID
    Oracle Workspace ManagerVALID
    Oracle Database Catalog ViewsVALID
    Oracle Database Packages and TypesVALID
    JServer JAVA Virtual MachineVALID
    Oracle XDKVALID
    Oracle Database Java PackagesVALID

    11 rows selected.


    can you please let me know why these objects are invalid after upgardation.

    Note: I have upgarded the databsae using DBUA.

    Regards,
    960149
  • 4. Re: ORA-04063: package body SYS.DBMS_SQLTUNE has errors
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    For the INVALID component statuses, pl see

    How To Diagnose Components With NON VALID Status In DBA_REGISTRY After an Upgrade [ID 753041.1]

    HTH
    Srini
  • 5. Re: ORA-04063: package body SYS.DBMS_SQLTUNE has errors
    Balazs Papp Expert
    Currently Being Moderated
    Hi,

    you did nothing wrong, it is a known issue, check the note:

    *Upgrade From 10G To 11G Fails On Dbms_sqltune : ORA-04063: Package Body "SYS.DBMS_SQLTUNE" Has Errors [ID 1271490.1]*

    I even met it when upgrading 11.2.0.x to 11.2.0.x+n sometimes, because the previous DBA just didnt check it after a 10g -> 11g upgrade.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points