4 Replies Latest reply on May 27, 2009 11:21 PM by 180827

    How to run @?/rdbms/admin/utlrp.sql from OEM?

    80675
      After I make database changes I have to run utlrp.sql to compile all the invalid objects. I know how to do it from the host. But now I'm running this release from the OEM as sql jobs.

      Is there any equivalent of @?/rdbms/sadmin/utlrp.sql running from OEM?

      error msg:

      DOC> 2. Query showing UTL_RECOMP jobs that are running
      DOC>     SELECT job_name FROM dba_scheduler_running_jobs
      DOC>          WHERE job_name like 'UTL_RECOMP_SLAVE_%';
      DOC>#
      utl_recomp.recomp_parallel(threads);
      *
      ERROR at line 4:
      ORA-06550: line 4, column 4:
      PLS-00201: identifier 'UTL_RECOMP.RECOMP_PARALLEL' must be declared
      ORA-06550: line 4, column 4:
      PL/SQL: Statement ignored


      Elapsed: 00:00:00.01
      SELECT dbms_registry_sys.time_stamp('utlrp_end') as timestamp from dual
      *
      ERROR at line 1:
      ORA-00904: "DBMS_REGISTRY_SYS"."TIME_STAMP": invalid identifier


      Elapsed: 00:00:00.00

      PL/SQL procedure successfully completed.

      Elapsed: 00:00:00.01
      DOC> The following query reports the number of objects that have compiled
      DOC> with errors (objects that compile with errors have status set to 3 in
      DOC> obj$). If the number is higher than expected, please examine the error
      DOC> messages reported with each object (using SHOW ERRORS) to see if they
      DOC> point to system misconfiguration or resource constraints that must be
      DOC> fixed before attempting to recompile these objects.
      DOC>#
      select COUNT(*) "OBJECTS WITH ERRORS" from obj$ where status = 3
      *
      ERROR at line 1:
      ORA-00942: table or view does not exist
        • 1. Re: How to run @?/rdbms/admin/utlrp.sql from OEM?
          80675
          I think you can do one of following as sysdba from OEM to accomplish the same thing. You don't need to literally invoke @?/rdbms/admin/utlrp.sql.

          Good to figure it out myself.

          -- Schema level.
          EXEC UTL_RECOMP.recomp_serial('SCOTT');
          EXEC UTL_RECOMP.recomp_parallel(4, 'SCOTT');

          -- Database level.
          EXEC UTL_RECOMP.recomp_serial();
          EXEC UTL_RECOMP.recomp_parallel(4);

          -- Using job_queue_processes value.
          EXEC UTL_RECOMP.recomp_parallel();
          EXEC UTL_RECOMP.recomp_parallel(NULL, 'SCOTT');
          • 2. Re: How to run @?/rdbms/admin/utlrp.sql from OEM?
            180827
            Hi,

            How can you create job in OEM to use EXEC UTL_RECOMP.recomp_serial(); inorder to compile all db objects.

            I tried few things but all resulting in error.
            I am creating OEM job as SQL script and where it ask for SQL script I am giving below code:

            1.
            WHENEVER SQLERROR EXIT FAILURE;
            EXEC UTL_RECOMP.recomp_serial;
            Error:
            SQL> SQL> SQL> SQL> SQL> BEGIN utl_recomp.recomp_serial ; END;

            *
            ERROR at line 1:
            ORA-06550: line 1, column 7:
            PLS-00201: identifier 'UTL_RECOMP.RECOMP_SERIAL' must be declared
            ORA-06550: line 1, column 7:
            PL/SQL: Statement ignored


            2.when trying with using utlrp.sql:
            WHENEVER SQLERROR EXIT FAILURE;
            @?/rdbms/admin/utlrp.sql;
            Error:
            SQL> SQL> SQL> SQL> SQL> SELECT dbms_registry_sys.time_stamp('utlrp_bgn') as timestamp from dual
            *
            ERROR at line 1:
            ORA-00904: "DBMS_REGISTRY_SYS"."TIME_STAMP": invalid identifier


            I can run utlrp.sql without any error manually from sql prompt as sys user.

            I am login into OEM as SYSTEM user and also with user who has privilege for backup.

            thanks,
            • 3. Re: How to run @?/rdbms/admin/utlrp.sql from OEM?
              670486
              utlrp.sql has to be run with "sys as sysdba"
              • 4. Re: How to run @?/rdbms/admin/utlrp.sql from OEM?
                180827
                Hi,

                I have got other user setup which has got sysdba privilege.
                Same user is also taking db backup every night.

                thanks,