1 2 Previous Next 17 Replies Latest reply: Sep 17, 2012 10:22 AM by 918424 Go to original post RSS
      • 15. Re: 54 Invalid Objects when running utlrp.sql script
        Hussein Sawwan-Oracle
        But after disabling it I can successfully run the utlrp.sql script, but with invalid objects.
        OLAP should be enabled to resolve those invalid objects.

        Please run the query above and post the output here.

        Thanks,
        Hussein
        • 16. Re: 54 Invalid Objects when running utlrp.sql script
          918424
          I am happy that I resolved most of them. I still have 5 more invalid objects. What I did was:

          1) When I could not run utlrp.sql script since it was saying the error like "not connected to Oracle" when running the script, I just removed the OLAP from the installation with following steps:

          SQL> conn / as sysdba
          SQL> @? / Olap / admin / catnoamd.sql
          SQL> @? / Olap / admin / olapidrp.plb
          SQL> @? / Olap / admin / catnoaps.sql
          SQL> @? / Olap / admin / catnoxoq.sql

          2) Now, after restarting the database, I ran utlrp.sql and I am succeeded in running this script now, but with more than 100 invalid objects.

          3) Then, I installed the OLAP from ./runInstaller using custom Install inside the same Oracle home as the database. Then re-started the database in upgrade mode and then ran utlirp.sql and then utlrp.sql after starting the DB in normal mode. Now again, I succeeded in running this script, but again with more than 100 invalid objects.

          However, this time I used utl_recomp_errors table to check the reason for the errors. The errors were "ORA-00980: synonym translation is no longer valid". So I dropped the public synonyms and recreated them and then gave execute permission to the public.

          4) I restarted the database in upgrade mode and ran utlirp.sql script and finally ran utlrp.sql after restarting the DB in normal mode. Now I get 0 errors in utl_recomp_errors query but:

          select count() "OBJECT WITH ERRORS" from obj$ where status=3;* returns 5, which means there are 5 objects with errors.

          When I tried to see the errors, I get:

          Errors for PACKAGE BODY SYS.CWM2_OLAP_INSTALLER:

          LINE/COL ERROR
          -------- -----------------------------------------------------------------
          5/3 PL/SQL: Item ignored
          7/5 PL/SQL: Statement ignored
          7/5 PLS-00201: identifier 'CWM2_OLAP_MEASURE.CREATE_MEASURE' must be
          declared

          10/5 PL/SQL: Statement ignored
          10/5 PLS-00201: identifier 'CWM2_OLAP_MEASURE.DROP_MEASURE' must be
          declared

          12/10 PLS-00201: identifier 'CWM2_OLAP_EXCEPTIONS.ALREADY_EXISTS' must
          be declared

          LINE/COL ERROR
          -------- -----------------------------------------------------------------

          20/3 PL/SQL: Item ignored
          22/5 PL/SQL: Statement ignored
          22/5 PLS-00201: identifier 'CWM2_OLAP_CUBE.CREATE_CUBE' must be
          declared

          25/5 PL/SQL: Statement ignored
          25/5 PLS-00201: identifier 'CWM2_OLAP_CUBE.DROP_CUBE' must be declared
          27/10 PLS-00201: identifier 'CWM2_OLAP_EXCEPTIONS.ALREADY_EXISTS' must
          be declared


          LINE/COL ERROR
          -------- -----------------------------------------------------------------
          35/3 PL/SQL: Item ignored
          37/5 PL/SQL: Statement ignored
          37/5 PLS-00201: identifier
          'CWM2_OLAP_LEVEL_ATTRIBUTE.CREATE_LEVEL_ATTRIBUTE' must be
          declared

          40/5 PL/SQL: Statement ignored
          40/5 PLS-00201: identifier
          'CWM2_OLAP_LEVEL_ATTRIBUTE.DROP_LEVEL_ATTRIBUTE' must be declared

          42/10 PLS-00201: identifier 'CWM2_OLAP_EXCEPTIONS.ALREADY_EXISTS' must

          LINE/COL ERROR
          -------- -----------------------------------------------------------------
          be declared

          52/5 PL/SQL: Statement ignored
          52/5 PLS-00201: identifier 'CWM2_OLAP_LEVEL.CREATE_LEVEL' must be
          declared


          When checking dba_objects I get:

          SQL> select object_name,object_type,owner from dba_objects where status='INVALID';

          OBJECT_NAME
          --------------------------------------------------------------------------------
          OBJECT_TYPE OWNER
          ------------------- ------------------------------
          INTERACTIONEXECUTE
          FUNCTION SYS

          OLAP_OLEDB_REG_ATTRS_PVT
          SYNONYM PUBLIC

          INTERACTIONEXECUTE
          SYNONYM PUBLIC


          OBJECT_NAME
          --------------------------------------------------------------------------------
          OBJECT_TYPE OWNER
          ------------------- ------------------------------
          CWM2_OLAP_INSTALLER
          PACKAGE BODY SYS

          OLAP_OLEDB_REG_ATTRS_PVT
          VIEW SYS


          When checking the status of the OLAP, I see:

          SQL> select comp_name,status from dba_registry where comp_name='Oracle OLAP API';

          COMP_NAME
          --------------------------------------------------------------------------------
          STATUS
          -----------
          Oracle OLAP API
          VALID

          Please guide me to get a clue for what the error may be..

          Thanks.
          • 17. Re: 54 Invalid Objects when running utlrp.sql script
            918424
            My question is not answered...Still some more errors are there. Kindly, refer my previous post. I have 5 invalid objects.

            Edited by: 915421 on Sep 17, 2012 8:22 AM
            1 2 Previous Next