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
, otherwise I'd give credit to them) and some of my own researches.
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
...
--
-- 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
padseller@gmail.com
Thanks!