About recompiling invalid objects in Oracle database on the Internet, there is certainly lots of scripts. Still, I decided to write my own package, which can skip recompiling addition of objects, which are undesirable in that moment, or compile them for a long time “hangs” (I mean for example, objects that contain links to remote Database (db link) and this is not available at the moment — then compile a long time to stop and wait for a response from the remote database. It happens often in the development instances)
[Česká verze článku — Jak rekurzivně rekompilovat invalidní objekty]
UPDATE 20.08.2010 - Of course the easiest way to recompile invalid objects is to use the system package UTL_RECOMP a PROCEDURE RECOMP_SERIAL so: “exec UTL_RECOMP.RECOMP_SERIAL(schema => ‘MY_SCHEMA_NAME’);”. My solution only adds to omit some of the objects of recompilation. It is nicely described on the server psoug.org
And now for my solution. First, you create a table in which we store the objects that we want to omit from the compilation.
-- 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'
/
Then, create a package that will contain the procedure compile_objects, it will be the one that will recompile objects recursively. Package is placed for testing purposes in schema “SCOTT” and name it as UTL$DB.
Remember to adjust the constants c_SELF_SCHEMA_NAME by scheme, in which the package will be installed! In our case, this scheme is ‘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;
/
Now we will show how to use the procedure compile_objects(). Our table UTL$DB_COMPILE_OBJ_IGNORE contains the following objects. These objects will be omitted from the compilation.
| ID | SCHEMA_NAME | OBJECT_NAME |
|---|---|---|
| 1 | SYS | % |
| 2 | SYSTEM | % |
| 3 | WMSYS | % |
| 4 | ADMIN | % |
| 5 | ADMIN | %OMIT_PACKAGE% |
If you run the procedure compile_objects() follows:
SQL> exec scott.utl$db.compile_objects();
…appears, for example (do not forget to turn on dbms_output throught “set serveroutput on” in SQL*Plus):
-- [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
This means that some objects could not compile. Find and fix a bug in the test package “TESTSCHEMA.SYPK_FLOW” and re-run compile_objects() the statement see:
-- [Summary] ----------------------------------------------- Invalid objects before compilation = 3 Invalid objects after compilation = 0
If I run compile_objects() with optional parameters such:
SQL> exec scott.utl$db.compile_objects(NULL, NULL, 'Y');
In the listing it will appear objects that have been omitted from the compilation:
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
Related posts:
