Na rekompilování invalidních objektů v Oracle databázi jistě existuje  na internetu spousta skriptů. Přesto jsem se rozhodl napsat vlastní balík (package), která umí navíc z rekompilování vynechat objekty, které jsou v tu chvíli nežádoucí, nebo se u nich kompilace na dlouhou dobu „zasekne“  (mám tím na mysli například objekty, které obsahují propojení do vzdálené databáze (db link) a tento není v danou chvíli dostupný – kompilace se pak na dlouhou dobu zastaví a čeká se na odezvu vzdálené databáze. Děje se často na vývojových instancích)

[English version – How to recompile invalid objects recursively]

UPDATE 20.08.2010 – Samozřejmě nejjednodušší cestou jak invalidní objekty rekompilovat je využít systémový balík UTL_RECOMP a PROCEDURU RECOMP_SERIAL takto: „exec UTL_RECOMP.RECOMP_SERIAL(schema => ‚MY_SCHEMA_NAME‘);“. Moje řešení pouze přidává možnost vynechat některé objekty z rekompilace. Hezky to je popsané na serveru psoug.org

A teď už k mému řešení. Nejprve si vytvoříme tabulku do které budeme ukládat objekty, které chceme z kompilování vynechat.

-- Create a table of objects that do not want to compile.
CREATE TABLE UTL$DB_COMPILE_OBJ_IGNORE
(
  ID                               NUMBER           NOT NULL,
  SCHEMA_NAME                      VARCHAR2(30)     NOT NULL,
  OBJECT_NAME                      VARCHAR2(30)     NOT NULL,
  CONSTRAINT UTL$DB_COMPILE_OBJ_IGNORE_PK  PRIMARY KEY ( ID ) 
    USING INDEX 
  ENABLE VALIDATE 
)
/

-- Create index
CREATE INDEX I_UTL$DB_COMPILE_OBJ_IGNORE
  ON UTL$DB_COMPILE_OBJ_IGNORE
  (
   SCHEMA_NAME,
   OBJECT_NAME
  )
/

-- Create sequence to generate primary key
CREATE SEQUENCE UTL$DB_COMPILE_OBJ_IGNORE_SEQ
/

-- Create trigger
CREATE OR REPLACE TRIGGER UTL$DB_COMPILE_OBJ_IGNORE_ID
  BEFORE INSERT ON UTL$DB_COMPILE_OBJ_IGNORE FOR EACH ROW
BEGIN
    IF(:NEW.ID IS NULL) THEN
        SELECT UTL$DB_COMPILE_OBJ_IGNORE_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
    END IF;
END;
/
COMMENT ON TABLE UTL$DB_COMPILE_OBJ_IGNORE IS 'Table of objects that do not want to compile'
/
COMMENT ON COLUMN UTL$DB_COMPILE_OBJ_IGNORE.ID IS 'Primary key'
/
COMMENT ON COLUMN UTL$DB_COMPILE_OBJ_IGNORE.OBJECT_NAME IS 'Object name'
/
COMMENT ON COLUMN UTL$DB_COMPILE_OBJ_IGNORE.SCHEMA_NAME IS 'Owner of this object'
/

Pak vytvoříme package, která bude obsahovat proceduru „compile_objects()“, to bude právě ta, která bude rekurzivně rekompilovat objekty. Package umístíme pro testovací účely do schématu „SCOTT“ a pojmenujeme ji například „UTL$DB“.

Nezapomeňte upravit hodnotu konstanty c_SELF_SCHEMA_NAME podle schématu, v kterém bude package umístěna !!! V našem případě je to schéma ‚SCOTT‘ !!!
CREATE OR REPLACE PACKAGE UTL$DB
IS

    /**
    * ======================================================================
    *                               ANNOTATION
    * ======================================================================
    *  Base package to recursively recompile invalid objects
    * ----------------------------------------------------------------------
    * Autor: Martin Mareš
    * Copyright (C) 2010 Martin Mareš, All rights reserved... but anybody can use it :)
    * ======================================================================
    */
    c_LINE_LENGTH      CONSTANT NUMBER := 60;
    c_SELF_SCHEMA_NAME CONSTANT VARCHAR2(50) := 'SCOTT';  -- don't compile itself
    c_SELF_OBJECT_NAME CONSTANT VARCHAR2(50) := 'UTL$DB';  -- don't compile itself
    e_ALL_EXCEPTIONS   CONSTANT NUMBER := - 20001;

    TYPE struct_object IS RECORD (
        schema_name UTL$DB_COMPILE_OBJ_IGNORE.SCHEMA_NAME%TYPE,
        object_name UTL$DB_COMPILE_OBJ_IGNORE.OBJECT_NAME%TYPE,
        object_type VARCHAR2(200)
    );

    TYPE list_objects IS TABLE OF struct_object INDEX BY BINARY_INTEGER;

    PROCEDURE compile_objects(schema_name_in  IN VARCHAR2 DEFAULT NULL
                             ,object_name_in  IN VARCHAR2 DEFAULT NULL
                             ,show_skipped_in IN VARCHAR2 DEFAULT 'N');

END UTL$DB;
/
CREATE OR REPLACE PACKAGE BODY UTL$DB
IS
    FUNCTION construct_compile_command (schema_name_in IN VARCHAR2
                                       ,object_name_in IN VARCHAR2
                                       ,object_type_in IN VARCHAR2) RETURN VARCHAR2
    IS
        l_return_compile_comm VARCHAR2(2000);

    BEGIN
        l_return_compile_comm := 'ALTER ' ||

            CASE
                WHEN object_type_in IN('PACKAGE', 'PACKAGE BODY')
                    THEN 'PACKAGE'
                WHEN object_type_in IN('JAVA CLASS', 'JAVA SOURCE')
                    THEN 'JAVA CLASS'
                ELSE object_type_in
            END ||
                                 ' "' ||
                                 schema_name_in ||
                                 '"."' ||
                                 object_name_in ||
                                 '"' ||

            CASE
                WHEN object_type_in IN('JAVA CLASS', 'JAVA SOURCE')
                    THEN ' RESOLVE'
                ELSE ' COMPILE ' ||

                    CASE
                        WHEN object_type_in IN('PACKAGE BODY')
                            THEN 'BODY'
                        ELSE NULL
                    END
            END;
        RETURN l_return_compile_comm;
    END construct_compile_command;

    FUNCTION get_list_objects(schema_name_in  IN VARCHAR2 DEFAULT NULL
                             ,object_name_in  IN VARCHAR2 DEFAULT NULL
                             ,show_skipped_in IN VARCHAR2 DEFAULT 'N') RETURN list_objects
    IS
        l_struct_object struct_object;
        l_list_objects  list_objects;

    BEGIN

        <<list_all_objects>>
        FOR cur_objects IN  (  SELECT obj.owner AS schema_name
                                     ,obj.object_name AS object_name
                                     ,obj.object_type AS object_type
                                 FROM all_objects obj
                                WHERE obj.status <> 'VALID'
                                  AND NOT (obj.owner = c_SELF_SCHEMA_NAME
                                  AND obj.object_name = c_SELF_OBJECT_NAME)  -- don't compile itself
                                  AND obj.object_type IN('TRIGGER', 'PACKAGE', 'PACKAGE BODY', 'VIEW', 'PROCEDURE', 'FUNCTION', 'JAVA CLASS', 'JAVA SOURCE')
                                  AND obj.owner = COALESCE(schema_name_in, obj.owner)
                                  AND obj.object_name = COALESCE(object_name_in, obj.object_name)
                             ORDER BY DECODE(obj.object_type,
                                      'JAVA SOURCE', 1,
                                      'JAVA CLASS', 2,
                                      'PACKAGE', 3,
                                      'PACKAGE BODY', 4,
                                      'PROCEDURE', 5,
                                      'FUNCTION', 6,
                                      'TRIGGER', 7,
                                      'VIEW', 8,
                                      10) ) LOOP
            l_struct_object.schema_name := cur_objects.schema_name;
            l_struct_object.object_name := cur_objects.object_name;
            l_struct_object.object_type := cur_objects.object_type;
            l_list_objects(l_list_objects.COUNT) := l_struct_object;
        END LOOP list_all_objects;

        <<objects_not_necessary>>
        FOR cur_objects IN  (SELECT schema_name
                                   ,object_name
                               FROM utl$db_compile_obj_ignore ) LOOP

            IF (l_list_objects.COUNT > 0) THEN

                <<loop_all_objects>>
                FOR i IN l_list_objects.FIRST..l_list_objects.LAST LOOP

                    IF (l_list_objects.EXISTS(i)) THEN
                        l_struct_object := l_list_objects(i);

                        IF l_struct_object.schema_name LIKE cur_objects.schema_name
                           AND l_struct_object.object_name LIKE cur_objects.object_name THEN
                            l_list_objects.DELETE(i);

                            IF (UPPER(show_skipped_in) IN('Y'
                                                         ,'YES'
                                                         ,'A'
                                                         ,'ANO')) THEN
                                DBMS_OUTPUT.put_line('Skip ' ||
                                                     l_struct_object.schema_name ||
                                                     '.' ||
                                                     l_struct_object.object_name ||
                                                     ' (Pattern: ' ||
                                                     cur_objects.schema_name ||
                                                     '/' ||
                                                     cur_objects.object_name ||
                                                     ')');
                            END IF;

                        END IF;

                    END IF;

                END LOOP loop_all_objects;
            END IF;

        END LOOP objects_not_necessary;

        RETURN l_list_objects;
    END get_list_objects;

    PROCEDURE compile_objects(schema_name_in  IN VARCHAR2 DEFAULT NULL
                             ,object_name_in  IN VARCHAR2 DEFAULT NULL
                             ,show_skipped_in IN VARCHAR2 DEFAULT 'N')
    IS
        l_compile_command VARCHAR2(2000);
        l_struct_object   struct_object;
        l_list_objects    list_objects;
        l_count_first     NUMBER;
        l_count_before    NUMBER;
        l_count_after     NUMBER;

    BEGIN
        l_list_objects := get_list_objects(schema_name_in => schema_name_in,
                                           object_name_in => object_name_in,
                                           show_skipped_in => show_skipped_in);
        l_count_first := l_list_objects.COUNT;
        l_count_before := l_count_first;
        l_count_after := 0;

        WHILE(l_count_before <> l_count_after) LOOP
            l_count_before := l_list_objects.COUNT;

            IF (l_list_objects.COUNT > 0) THEN
                FOR i IN l_list_objects.FIRST..l_list_objects.LAST LOOP

                    IF (l_list_objects.EXISTS(i)) THEN
                        l_struct_object := l_list_objects(i);
                        l_compile_command := construct_compile_command (schema_name_in => l_struct_object.schema_name
                                                                       ,object_name_in => l_struct_object.object_name
                                                                       ,object_type_in => l_struct_object.object_type);

                        BEGIN
                            EXECUTE IMMEDIATE l_compile_command ;
                            EXCEPTION
                                WHEN OTHERS THEN

                                    IF (SQLCODE NOT IN( - 24344
                                                      , - 4052
                                                      , - 1031)) THEN
                                        DBMS_OUTPUT.put_line('SQLCODE = ' ||
                                                             SQLCODE);
                                        DBMS_OUTPUT.put_line('SQLERRM = ' ||
                                                             SQLERRM);
                                        DBMS_OUTPUT.put_line('Try Execute = ' ||
                                                             l_compile_command);
                                        RAISE_APPLICATION_ERROR (e_ALL_EXCEPTIONS
                                                                ,'Exception in execute immediate command (' ||
                                                                 sqlerrm ||
                                                                 ').');
                                    END IF;

                        END;
                    END IF;

                END LOOP;
            END IF;

            l_list_objects := get_list_objects(schema_name_in => schema_name_in,
                                               object_name_in => object_name_in);
            l_count_after := l_list_objects.COUNT;
        END LOOP;

        IF (l_list_objects.COUNT > 0) THEN
            DBMS_OUTPUT.put_line(RPAD('-- [Invalid objects after compilation] ',
                                      c_LINE_LENGTH, '-'));

            FOR i IN l_list_objects.FIRST..l_list_objects.LAST LOOP

                IF (l_list_objects.EXISTS(i)) THEN
                    l_struct_object := l_list_objects(i);
                    DBMS_OUTPUT.put_line(l_struct_object.schema_name ||
                                         '.' ||
                                         l_struct_object.object_name ||
                                         ' (' ||
                                         l_struct_object.object_type ||
                                         ')');
                END IF;

            END LOOP;

        END IF;

        DBMS_OUTPUT.put_line(RPAD('-- [Summary] ', c_LINE_LENGTH, '-'));
        DBMS_OUTPUT.put_line('Invalid objects before compilation = ' ||
                             l_count_first);
        DBMS_OUTPUT.put_line('Invalid objects after compilation = ' ||
                             l_count_after);
    END compile_objects;

END UTL$DB;
/

Nyní si ukážeme, jak využít proceduru „compile_objects()“. Naše tabulka UTL$DB_COMPILE_OBJ_IGNORE obsahuje následující objekty. Tyto objekty budou z kompilace vynechány.

ID SCHEMA_NAME OBJECT_NAME
1 SYS %
2 SYSTEM %
3 WMSYS %
4 ADMIN %
5 ADMIN %OMIT_PACKAGE%

Pokud spustíme proceduru „compile_objects()“ následujícím způsobem:

SQL> exec scott.utl$db.compile_objects();

…objeví se například (nezapomeňte zapnout výstup pro dbms_output):

-- [Invalid objects after compilation] ---------------------
TESTSCHEMA.SYPK_FLOW (PACKAGE)
TESTSCHEMA.SYPK_TEST (PACKAGE)
TESTSCHEMA.SYPK_MAIN (PACKAGE)
-- [Summary] -----------------------------------------------
Invalid objects before compilation = 3
Invalid objects after compilation = 3

To znamená, že některé objekty se nepodařilo zkompilovat. Najdeme a opravíme chybu v testovacím balíku „TESTSCHEMA.SYPK_FLOW“ a znovu spustíme „compile_objects()“, ve výpisu uvidíme:

-- [Summary] -----------------------------------------------
Invalid objects before compilation = 3
Invalid objects after compilation = 0

Pokud spustíme „compile_objects“ s nepovinnými parametry takto:

SQL> exec scott.utl$db.compile_objects(NULL, NULL, 'Y');

Ve výpise se objeví i objekty, které byly z kompilace vynechány:

Skip ADMIN.TEST_PACKAGE (Pattern: ADMIN/%)
Skip ADMIN.TEST_PACKAGE2 (Pattern: ADMIN/%)
Skip ADMIN.TEST_PACKAGE3 (Pattern: ADMIN/%)
Skip ADMIN.OMIT_PACKAGE1 (Pattern: ADMIN/%OMIT_PACKAGE%)
Skip ADMIN.OMIT_PACKAGE2 (Pattern: ADMIN/%OMIT_PACKAGE%)
Skip ADMIN.OMIT_PACKAGE3 (Pattern: ADMIN/%OMIT_PACKAGE%)
-- [Summary] -----------------------------------------------
Invalid objects before compilation = 0
Invalid objects after compilation = 0

Post to Twitter

2 Comments

  1. Odpovědět

    Hi Martin,

    Thanks for giving me the reference to your version on recompiling script. Its a nice post and yeah…good coincidence :) Hope to see more in this blog!

    Thanks,
    Sowjanya

Leave a comment

Vaše emailová adresa nebude zveřejněna. Vyžadované informace jsou označeny *