5 Replies Latest reply: Mar 5, 2013 1:03 PM by Balazs Papp RSS

    ORA-04063: package body SYS.DBMS_SQLTUNE has errors

    963152
      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
          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
            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
              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
                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
                  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.