13 Replies Latest reply: Apr 16, 2007 2:52 PM by 445431 RSS

    SQL Tuning Advisor

    445431
      Every time I try to run the SQL Tuning Advisor i get this error message ->>
      There was a problem creating a SQL tuning task. ORA-04063: package body "SYS.DBMS_SQLTUNE_INTERNAL" has errors ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_SQLTUNE_INTERNAL" ORA-06512: at "SYS.DBMS_SQLTUNE", line 909 ORA-06512: at line 1
      Does anyone know how to troubleshoot this?
        • 1. Re: SQL Tuning Advisor
          439126
          Is SYS.DBMS_SQLTUNE_INTERNAL actually valid on the target database?
          • 2. Re: SQL Tuning Advisor
            rodneyli
            Check if the package is valid...you may need to recompile it.
            • 3. Re: SQL Tuning Advisor
              445431
              I check the package SYS.dbms_sqltune_internal and is not valid
              • 4. Re: SQL Tuning Advisor
                sgalaxy
                Hi ,
                Log on OEM as sys or sysman and :
                1.select "All Metrics" form the Related Links in the first page of OEM
                2.Select Invalid Objects by schema link
                3. In the page displayed , you should find the schemas which contain invalid objects.
                4. Select the SYS schema...and then maybe all others.
                5. Check all and complile.....

                Regards,
                Simon
                • 5. Re: SQL Tuning Advisor
                  rodneyli
                  Just recompile it..
                  You may want to run utlrp to recompile everything ...
                  • 6. Re: SQL Tuning Advisor
                    445431
                    Simon I tried your steps and it dont recompile
                    • 7. Re: SQL Tuning Advisor
                      445431
                      These are some of the errors that TOAD is showing on the schema browser

                      On line: 1344
                      PL/SQL: ORA-00980: synonym translation is no longer valid
                      PLS-00634:loop index variable 'STAT_REC' use is invalid line 1349
                      PLS-00634:loop index variable 'STAT_REC' use is invalid line 1357
                      PL/SQL: ORA-00980: synonym translation is no longer valid line 3974
                      PLS-00634:loop index variable 'STAT_REC' use is invalid line 4003
                      PLS-00634:loop index variable 'STAT_REC' use is invalid line 4035
                      PLS-00634:loop index variable 'STAT_REC' use is invalid line 4039
                      PL/SQL: ORA-00980: synonym translation is no longer valid line 4082
                      PLS-00634:loop index variable 'OBJECT_REC' use is invalid line 4094
                      PLS-00634:loop index variable 'OBJECT_REC' use is invalid line 4100
                      • 8. Re: SQL Tuning Advisor
                        445431
                        You dont want do this on production enviroment
                        • 9. Re: SQL Tuning Advisor
                          sgalaxy
                          Simon I tried your steps and it dont recompile
                          You mean that you have recompiled the SYS schema and all others' which may have invalid objects and the problem remain....????

                          Simon
                          • 10. Re: SQL Tuning Advisor
                            445431
                            I found this Note on metalink and has solved my problem.
                            390221.1
                            Thank all for the reply
                            • 11. Re: SQL Tuning Advisor
                              mnazim-Oracle
                              Thats Great
                              • 12. Re: SQL Tuning Advisor
                                sgalaxy
                                Hi ,
                                As i have not access to Metalink , could you please write down how have you solved the problem , (executed a script or made some config)...????


                                Many thanks,
                                Simon
                                • 13. Re: SQL Tuning Advisor
                                  445431
                                  This is from metalink ...
                                  <--

                                  Subject: Oracle Database Server Invalid After Upgrade To 10.2.0.2 Ora-00980 on DBMS_XPLAN and DBMS_SQLTUNE_INTERNAL
                                  Doc ID: Note:390221.1 Type: PROBLEM
                                  Last Revision Date: 21-FEB-2007 Status: MODERATED

                                  In this Document
                                  Symptoms
                                  Cause
                                  Solution
                                  References



                                  --------------------------------------------------------------------------------


                                  This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.



                                  Applies to:
                                  Oracle Server - Enterprise Edition - Version: 10.2.0.2
                                  This problem can occur on any platform.

                                  Symptoms
                                  After upgrading 10.2.0.1 to 10.2.0.2 Oracle Database Server component is INVALID.
                                  There are two packages compiled with errors: DBMS_XPLAN and DBMS_SQLTUNE_INTERNAL.

                                  Warning: Package Body created with compilation errors.

                                  Errors for PACKAGE BODY DBMS_XPLAN:

                                  LINE/COL ERROR
                                  -------- -----------------------------------------------------------------
                                  1507/9 PL/SQL: SQL Statement ignored
                                  1511/34 PL/SQL: ORA-00980: synonym translation is no longer valid
                                  1518/9 PL/SQL: SQL Statement ignored
                                  1520/34 PL/SQL: ORA-00980: synonym translation is no longer valid
                                  1526/9 PL/SQL: SQL Statement ignored
                                  1540/27 PL/SQL: ORA-00980: synonym translation is no longer valid

                                  ...

                                  Warning: Package Body created with compilation errors.

                                  Errors for PACKAGE BODY DBMS_SQLTUNE_INTERNAL:

                                  LINE/COL ERROR
                                  -------- -----------------------------------------------------------------
                                  1341/9 PL/SQL: SQL Statement ignored
                                  1344/33 PL/SQL: ORA-00980: synonym translation is no longer valid
                                  1349/9 PL/SQL: Statement ignored
                                  1349/13 PLS-00364: loop index variable 'STAT_REC' use is invalid
                                  1356/9 PL/SQL: Statement ignored
                                  1357/19 PLS-00364: loop index variable 'STAT_REC' use is invalid
                                  3958/8 PL/SQL: SQL Statement ignored
                                  3974/35 PL/SQL: ORA-00980: synonym translation is no longer valid
                                  4003/7 PL/SQL: Statement ignored
                                  ....

                                  Cause
                                  SYS.EXTRACT and SYS.EXISTSNODE public synonyms conflicting with synonyms being used to compile
                                  these packages.

                                  These objects that do not belong to a 10.2 database. They should had been removed in a previous
                                  upgrade.



                                  Solution
                                  - Drop synonyms
                                  drop public synonym existsnode;
                                  drop public synonym extract;

                                  - Recompile packages. If this is OK, then
                                  - Rerun catupgrd.
                                  References
                                  Bug 3431498 - ORA-29900: WHEN CREATING VIEW USING XMLSEQUENCE
                                  Bug 5094109 - RUNNING SYS.DBMS_SQLTUNE_INTERNAL GIVES ORA-04063 ORA-06508 ORA-06512

                                  Errors
                                  PLS-364 loop index variable '%s' use is invalid
                                  ORA-980 synonym translation is no longer valid

                                  -->
                                  Luis