Recompiling invalid objects in an Oracle DB (or a single schema)

Oracle has its own solution for the problem in the package named utl_recomp (you can find it in %ORACLE_HOME%/rdbms/admin/utlrp.sql), however it's only available from 9i and up. Oracle 8i users are left out in the cold ... as was I a long time ago.
I wrote my own recompile code based on what I found on the topic by others (unfortunately I do not remember the sources Sad, otherwise I'd give credit to them) and some of my own researches. Smile

It's a copy and paste stuff, not much to adjust. However I think it's quite well structured and easy to understand. It can be easily extended to support other object types as well.

Here it is without any further ado Smile ...

--
-- Recompile all invalid objects in current schema
--
DECLARE
  TYPE vc_arr IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
  TYPE num_arr IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  -- use sSchema to restrict scope to objects of currently logged on user
  sSchema VARCHAR2(30) := SYS_CONTEXT('USERENV', 'SESSION_USER');
  CURSOR cObjects IS
    SELECT
      ao.owner,
      ao.object_type,
      ao.object_name,
      ao.object_id
    FROM
      all_objects ao,
      (
        SELECT
          dep1.object_id,
          MAX(dep1.dep_level) AS dep_level
        FROM
          (
            SELECT
              pd1.referenced_object_id AS object_id,
              LEVEL AS dep_level
            FROM
              public_dependency pd1
            START WITH pd1.referenced_object_id IN (
              SELECT
                ao1.object_id
              FROM
                all_objects ao1
              WHERE 1 = 1
                AND ao1.status = 'INVALID'
            )
            CONNECT BY PRIOR pd1.object_id = pd1.referenced_object_id
            UNION
            SELECT
              pd2.object_id,
              (LEVEL + 1) AS dep_level
            FROM
              public_dependency pd2
            START WITH pd2.referenced_object_id IN (
              SELECT
                ao2.object_id
              FROM
                all_objects ao2
              WHERE 1 = 1
                AND ao2.status = 'INVALID'
            )
            CONNECT BY PRIOR pd2.object_id = pd2.referenced_object_id
            UNION
            SELECT
              ao3.object_id,
              0 AS dep_level
            FROM
              all_objects ao3
            WHERE 1 = 1
              AND ao3.status = 'INVALID'
              AND NOT EXISTS(
                SELECT
                  NULL
                FROM
                  public_dependency pd3
                WHERE 1 = 1
                  AND pd3.referenced_object_id = ao3.object_id
              )
          ) dep1
        GROUP BY
          dep1.object_id
      ) dep2
    WHERE 1 = 1
      AND ao.owner = sSchema
      AND ao.object_type IN (
        'DIMENSION',
        'FUNCTION',
        'INDEX',
        'INDEXTYPE',
        'JAVA CLASS',
        'JAVA SOURCE',
        'MATERIALIZED VIEW',
        'OPERATOR',
        'OUTLINE',
        'PACKAGE',
        'PACKAGE BODY',
        'PROCEDURE',
        'TRIGGER',
        'TYPE',
        'TYPE BODY',
        'VIEW'
      )
      AND ao.object_id = dep2.object_id
    ORDER BY
      dep2.dep_level
  ;
  aErrorMsgs vc_arr;
  aOwners vc_arr;
  aTypes vc_arr;
  aNames vc_arr;
  aIDs num_arr;
  sSQL VARCHAR2(4000);
  nTmp NUMBER;
  nSuccess NUMBER;
  nPrevSuccess NUMBER;
  nIdx NUMBER;
  nRoundCnt NUMBER;
BEGIN
  dbms_output.enable(1000000);
  nRoundCnt := 1;
  LOOP
    nPrevSuccess := nSuccess;
    nSuccess := 0;
    aErrorMsgs.DELETE;
    OPEN cObjects;
    FETCH cObjects BULK COLLECT INTO
      aOwners,
      aTypes,
      aNames,
      aIDs
    ;
    CLOSE cObjects;
    nIdx := aIDs.FIRST;
    WHILE nIdx IS NOT NULL LOOP
      SELECT
        COUNT(*)
      INTO
        nTmp
      FROM
        all_objects ao
      WHERE 1 = 1
        AND ao.object_id = aIDs(nIdx)
        AND ao.status = 'INVALID'
      ;
      IF nTmp > 0 THEN
        IF aTypes(nIdx) = 'PACKAGE' THEN
          sSQL := 'ALTER PACKAGE "' || aOwners(nIdx) || '"."' || aNames(nIdx) || '" COMPILE SPECIFICATION';
        ELSIF aTypes(nIdx) = 'PACKAGE BODY' THEN
          sSQL := 'ALTER PACKAGE "' || aOwners(nIdx) || '"."' || aNames(nIdx) || '" COMPILE BODY';
        ELSIF aTypes(nIdx) = 'TYPE' THEN
          sSQL := 'ALTER TYPE "' || aOwners(nIdx) || '"."' || aNames(nIdx) || '" COMPILE SPECIFICATION';
        ELSIF aTypes(nIdx) = 'TYPE BODY' THEN
          sSQL := 'ALTER TYPE "' || aOwners(nIdx) || '"."' || aNames(nIdx) || '" COMPILE BODY';
        ELSIF aTypes(nIdx) = 'INDEX' THEN
          sSQL := 'ALTER INDEX "' || aOwners(nIdx) || '"."' || aNames(nIdx) || '" REBUILD';
        ELSIF aTypes(nIdx) = 'OUTLINE' THEN
          sSQL := 'ALTER OUTLINE "' || aNames(nIdx) || '" REBUILD';
        ELSE
          sSQL := 'ALTER ' || aTypes(nIdx) || ' "' || aOwners(nIdx) || '"."' || aNames(nIdx) || '" COMPILE';
        END IF;
        BEGIN
          EXECUTE IMMEDIATE sSQL;
          nSuccess := nSuccess + 1;
        EXCEPTION
          WHEN OTHERS THEN
            aErrorMsgs(aErrorMsgs.COUNT+1) := 'Error at:'
              || ' ' || aTypes(nIdx)
              || ' "' || aOwners(nIdx) || '"."' || aNames(nIdx) || '"'
            ;
        END;
      END IF;
      nIdx := aIDs.NEXT(nIdx);
    END LOOP;
    EXIT WHEN nSuccess = nPrevSuccess;
    dbms_output.put_line(' ');
    nTmp := aErrorMsgs.FIRST;
    WHILE nTmp IS NOT NULL LOOP
      dbms_output.put_line(aErrorMsgs(nTmp));
      nTmp := aErrorMsgs.NEXT(nTmp);
    END LOOP;
    dbms_output.put_line(' ');
    dbms_output.put_line('Round no. #' || nRoundCnt);
    dbms_output.put_line('  Successes:  ' || (nSuccess + aErrorMsgs.COUNT));
    dbms_output.put_line('  Errors:     ' || aErrorMsgs.COUNT);
    nRoundCnt := nRoundCnt + 1;
    EXIT WHEN aErrorMsgs.COUNT = 0 OR nRoundCnt > 10;
  END LOOP;
EXCEPTION WHEN OTHERS THEN
  dbms_output.put_line('Exception occured. Oracle errormessage:' || CHR(10) || SQLERRM);
END;

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

padseller@gmail.com

I have to say, you did a really nice job on explaining something that can be really tricky at times. There are times that I struggle with wrapping my head around topics like the this, thank you for summing it up well. There is another guy that writes on this subject, and he does it really well. His name escapes me at the moment though.

Thanks!