This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Sep 17, 2012 8:22 AM by 918424 Go to original post RSS
  • 15. Re: 54 Invalid Objects when running utlrp.sql script
    Hussein Sawwan-Oracle Employee ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

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