Recompilando objetos inválidos

Grande Rodrigo,

Apenas adicionando mais alguns scripts!!!

--

-- Ver todos os objetos invalidos no banco

--

select status, object_id, object_type, owner||'.'||object_name "OWNER.OBJECT"
from dba_objects where status = 'INVALID' order by 4,2;

--

-- Criando os scripts para validar os objetos

--

select 'alter '||object_type||' '||owner||'.'||object_name||' compile '||object_type||';'
from dba_objects where status = 'INVALID' and object_type = 'PACKAGE';

select 'alter '||object_type||' '||owner||'.'||object_name||' compile BODY;'
from dba_objects where status = 'INVALID' and object_type in ('PACKAGE BODY','TYPE','TYPE BODY','');

select 'alter '||object_type||' '||owner||'.'||object_name||' compile;'
from dba_objects where status = 'INVALID' and object_type in ('VIEW','SYNONYM','TRIGGER','PROCEDURE','FUNCTION','INDEXTYPE','INDEX','OPERATOR');

OBS.: Estes scripts devem ser executados com algum usuário que tenha permissão de DBA.

http://forum.imasters.com.br/public/style_emoticons/default/thumbsup.gif

Declare Cursor Objetos Is Select Distinct Object_Type, Substr(Object_Name, 1, 50) Object_Name From User_Objects Where Status = 'INVALID'; Comando Varchar2(200); Begin For g In 1 .. 5 Loop For i In Objetos Loop Comando := 'ALTER PROCEDURE ' || i.Object_Name || ' COMPILE'; -- DBMS_OUTPUT.put_line(COMANDO); Begin Execute Immediate Comando; Exception When Others Then Null; -- dbms_output.put_line(sqlerrm); End; End Loop; End Loop; End;

Realmente, um script muito útil, muito bom.

Por mim, deveria ser pinned este tópico.

Foi de muito avalia para mim aqui no trabalho.

Muito Obrigado amigo ! http://forum.imasters.com.br/public/style_emoticons/default/thumbsup.gif

Thnks http://forum.imasters.com.br/public/style_emoticons/default/clap.gif

Caros amigos,

O script abaixo é muito interessante, já que compila todas as dependencias, sendo executado uma unica vez. Basta criar a função e executar passando o OWNER. Abraço

EXECUTAR:

declare

-- Boolean parameters are translated from/to integers:

-- 0/1/null <--> false/true/null

 -- display boolean := sys.diutil.int_to_bool(:display);

 result varchar2(400);

begin

-- Call the function

 result := recompile('OWNER');

end;

FUNÇÃO:

-- | |

-- | Recompile Utility |

-- |____________________________________________________________________|

--

-- FILE: recompile.sql

-- TITLE: Recompile Utility

-- TYPE: ORACLE PL/SQL Stored Function

-- VERSION: 2.0

-- CREATED: August 3, 1998

-- AUTHOR: Solomon Yakobson

-- WARNING:

-- SCOPE: Recompile Utility can be used for Oracle 7.3 and above object compilation.

--

-- MODIFICATION

-- HISTORY: September 9, 1998 - fixed obj_cursor to include objects

-- with no dependencies.

--

-- May 12, 1999 - fix for DBMS_SQL behavior change in

-- Oracle 8 (most likely it is a bug).

-- If object recompilation has errors,

-- ORACLE 8 DBMS_SQL raises exception:

-- ORA-24333: success with compilation

-- error, followed by host environment

-- (e.g. SQL*Plus) internal error and

-- Unsafe to proceed message.

--

-- May 12, 1999 - added COMPILE_ERRORS return code.

--

-- May 12, 1999 - added TYPE and TYPE BODY objects.

--

-- Jan 10, 2006 - upgraded by Steven Feuerstein

--

-- DESCRIPTION: Recompile Utility is designed to compile the following

-- types of objects:

--

-- PROCEDURE (ORACLE 7 && 8),

-- FUNCTION (ORACLE 7 && 8),

-- PACKAGE - specification and body (ORACLE 7 && 8),

-- PACKAGE BODY - body only (ORACLE 7 && 8),

-- TRIGGER (ORACLE 7 && 8),

-- VIEW (ORACLE 7 && 8),

-- TYPE - specification only (ORACLE 8),

-- TYPE BODY - body only (ORACLE 8).

--

-- Objects are recompiled based on object dependencies and

-- therefore compiling all requested objects in one path.

-- Recompile Utility skips every object which is either of

-- unsupported object type or depends on INVALID object(s)

-- outside of current request (which means we know upfront

-- compilation will fail anyway). If object recompilation

-- is not successful, Recompile Utility continues with the

-- next object. Recompile Utility has five parameters:

--

-- o_owner - IN mode parameter is a VARCHAR2 defining

-- owner of to be recompiled objects. It

-- accepts operator LIKE widcards. Backslash

-- (\) is used for escaping wildcards. If

-- omitted, parameter defaults to USER.

-- o_name - IN mode parameter is a VARCHAR2 defining

-- names of to be recompiled objects. It

-- accepts operator LIKE widcards. Backslash

-- (\) is used for escaping wildcards. If

-- omitted, it defaults to '%' - any name.

-- o_type - IN mode parameter is a VARCHAR2 defining

-- types of to be recompiled objects. It

-- accepts operator LIKE widcards. Backslash

-- (\) is used for escaping wildcards. If

-- omitted, it defaults to '%' - any type.

-- o_status - IN mode parameter is a VARCHAR2 defining

-- status of to be recompiled objects. It

-- accepts operator LIKE widcards. Backslash

-- (\) is used for escaping wildcards. If

-- omitted, it defaults to 'INVALID'.

-- display - IN mode parameter is a BOOLEAN defining

-- whether object recompile status is written

-- to DBMS_OUTPUT buffer. If omitted, it

-- defaults to TRUE.

--

-- Recompile Utility returns the following values or their

-- combinations:

--

-- 0 - Success. All requested objects are recompiled and

-- are VALID.

-- 1 - INVALID_TYPE. At least one of to be recompiled

-- objects is not of supported object type.

-- 2 - INVALID_PARENT. At least one of to be recompiled

-- objects depends on an invalid object outside of

-- current request.

-- 4 - COMPILE_ERRORS. At least one of to be recompiled

-- objects was compiled with errors and is INVALID.

--

-- If parameter display is set to TRUE, Recompile Utility

-- writes the following information to DBMS_OUTPUT buffer:

--

-- RECOMPILING OBJECTS

--

-- Object Owner is o_owner

-- Object Name is o_name

-- Object Type is o_type

-- Object Status is o_status

--

-- TTT OOO.NNN is recompiled. Object status is SSS.

-- TTT OOO.NNN references invalid object(s) outside of

-- this request.

-- OOO.NNN is TTT and can not be recompiled.

--

-- where o_owner is parameter o_owner value, o_name is

-- parameter o_name value, o_type is parameter o_type

-- value and o_status is is parameter o_status value. TTT

-- is object type, OOO is object owner, NNN is object name

-- and SSS is object status after compilation.

--

-- NOTES: If parameter display is set to TRUE, you MUST ensure

-- DBMS_OUTPUT buffer is large enough for produced output.

-- Otherwise Recompile Utility will not recompile all the

-- objects. If used in SQL*Plus, issue:

--

-- SET SERVEROUTPUT ON SIZE xxx FORMAT WRAPPED

--

-- FORMAT WRAPPED is needed for text alignment.

-- ______________________________________________________________________

--

CREATE OR REPLACE FUNCTION recompile (

o_owner IN VARCHAR2 := USER

, o_name IN VARCHAR2 := '%'

, o_type IN VARCHAR2 := '%'

, o_status IN VARCHAR2 := 'INVALID'

, display IN BOOLEAN := TRUE

)

RETURN NUMBER AUTHID CURRENT_USER

IS

-- Exceptions

 successwithcompilationerror EXCEPTION;

 PRAGMA EXCEPTION_INIT ( successwithcompilationerror, -24344 );

 -- Return Codes

 invalid_type CONSTANT INTEGER := 1;

 invalid_parent CONSTANT INTEGER := 2;

 compile_errors CONSTANT INTEGER := 4;

 cnt NUMBER;

 dyncur INTEGER;

 type_status INTEGER := 0;

 parent_status INTEGER := 0;

 recompile_status INTEGER := 0;

 object_status VARCHAR2 ( 30 );

 

 CURSOR invalid_parent_cursor (

 oowner VARCHAR2

 , oname VARCHAR2

 , otype VARCHAR2

 , ostatus VARCHAR2

 , OID NUMBER

 )

 IS

 SELECT /*+ RULE */

 o.object_id

 FROM public_dependency d, all_objects o

 WHERE d.object_id = OID

 AND o.object_id = d.referenced_object_id

 AND o.status != 'VALID'

 MINUS

 SELECT /*+ RULE */

 object_id

 FROM all_objects

 WHERE owner LIKE UPPER ( oowner )

 AND object_name LIKE UPPER ( oname )

 AND object_type LIKE UPPER ( otype )

 AND status LIKE UPPER ( ostatus );

 

 CURSOR recompile_cursor ( OID NUMBER )

 IS

 SELECT /*+ RULE */

 'ALTER '

 || DECODE ( object_type

 , 'PACKAGE BODY', 'PACKAGE'

 , 'TYPE BODY', 'TYPE'

 , object_type

 )

 || ' '

 || owner

 || '.'

 || object_name

 || ' COMPILE '

 || DECODE ( object_type

 , 'PACKAGE BODY', ' BODY'

 , 'TYPE BODY', 'BODY'

 , 'TYPE', 'SPECIFICATION'

 , ''

 )

 || ' REUSE SETTINGS' stmt

 , object_type, owner, object_name

 FROM all_objects

 WHERE object_id = OID;

 

 recompile_record recompile_cursor%ROWTYPE;

 

 CURSOR obj_cursor (

 oowner VARCHAR2

 , oname VARCHAR2

 , otype VARCHAR2

 , ostatus VARCHAR2

 )

 IS

 SELECT /*+ RULE */

 MAX ( LEVEL ) dlevel, object_id

 FROM SYS.public_dependency

 START WITH object_id IN (

 SELECT object_id

 FROM all_objects

 WHERE owner LIKE UPPER ( oowner )

 AND object_name LIKE UPPER ( oname )

 AND object_type LIKE UPPER ( otype )

 AND status LIKE UPPER ( ostatus ))

 CONNECT BY object_id = PRIOR referenced_object_id

 GROUP BY object_id

 HAVING MIN ( LEVEL ) = 1

 UNION ALL

 SELECT 1 dlevel, object_id

 FROM all_objects o

 WHERE owner LIKE UPPER ( oowner )

 AND object_name LIKE UPPER ( oname )

 AND object_type LIKE UPPER ( otype )

 AND status LIKE UPPER ( ostatus )

 AND NOT EXISTS ( SELECT 1

 FROM SYS.public_dependency d

 WHERE d.object_id = o.object_id )

 ORDER BY 1 DESC;

 

 TYPE integer_tt IS TABLE OF PLS_INTEGER

 INDEX BY BINARY_INTEGER;

 

 l_dlevel integer_tt;

 l_object_id integer_tt;

 

 CURSOR status_cursor ( OID NUMBER )

 IS

 SELECT /*+ RULE */

 status

 FROM all_objects

 WHERE object_id = OID;

BEGIN

-- Recompile requested objects based on their dependency levels.

IF display

THEN

 DBMS_OUTPUT.put_line ( CHR ( 0 ));

 DBMS_OUTPUT.put_line

 ( ' RECOMPILING OBJECTS' );

 DBMS_OUTPUT.put_line ( CHR ( 0 ));

 DBMS_OUTPUT.put_line

 ( ' Object Owner is '

 || o_owner

 );

 DBMS_OUTPUT.put_line

 ( ' Object Name is '

 || o_name

 );

 DBMS_OUTPUT.put_line

 ( ' Object Type is '

 || o_type

 );

 DBMS_OUTPUT.put_line

 ( ' Object Status is '

 || o_status

 );

 DBMS_OUTPUT.put_line ( CHR ( 0 ));

 END IF;

 

 dyncur := DBMS_SQL.open_cursor;

 

 OPEN obj_cursor ( o_owner, o_name, o_type, o_status );

 

 FETCH obj_cursor

 BULK COLLECT INTO l_dlevel, l_object_id;

 

 FOR indx IN 1 .. l_dlevel.COUNT

 LOOP

 OPEN recompile_cursor ( l_object_id ( indx ));

 

 FETCH recompile_cursor

 INTO recompile_record;

 

 CLOSE recompile_cursor;

 

 -- We can recompile only Functions, Packages, Package Bodies,

 -- Procedures, Triggers, Views, Types and Type Bodies.

 IF recompile_record.object_type IN

 ( 'FUNCTION'

 , 'PACKAGE'

 , 'PACKAGE BODY'

 , 'PROCEDURE'

 , 'TRIGGER'

 , 'VIEW'

 , 'TYPE'

 , 'TYPE BODY'

 )

 THEN

 -- There is no sense to recompile an object that depends on

 -- invalid objects outside of the current recompile request.

 OPEN invalid_parent_cursor ( o_owner

 , o_name

 , o_type

 , o_status

 , l_object_id ( indx )

 );

 

 FETCH invalid_parent_cursor

 INTO cnt;

 

 IF invalid_parent_cursor%NOTFOUND

 THEN

 -- Recompile object.

 BEGIN

 DBMS_SQL.parse ( dyncur

 , recompile_record.stmt

 , DBMS_SQL.native

 );

 EXCEPTION

 WHEN successwithcompilationerror

 THEN

 NULL;

 END;

 

 OPEN status_cursor ( l_object_id (indx));

 

 FETCH status_cursor

 INTO object_status;

 

 CLOSE status_cursor;

 

 IF display

 THEN

 DBMS_OUTPUT.put_line ( recompile_record.object_type

 || ' '

 || recompile_record.owner

 || '.'

 || recompile_record.object_name

 || ' is recompiled. Object status is '

 || object_status

 || '.'

 );

 END IF;

 

 IF object_status <> 'VALID'

 THEN

 recompile_status := compile_errors;

 END IF;

 ELSE

 IF display

 THEN

 DBMS_OUTPUT.put_line ( recompile_record.object_type

 || ' '

 || recompile_record.owner

 || '.'

 || recompile_record.object_name

 || ' references invalid object(s)'

 || ' outside of this request.'

 );

 END IF;

 

 parent_status := invalid_parent;

 END IF;

 

 CLOSE invalid_parent_cursor;

 ELSE

 IF display

 THEN

 DBMS_OUTPUT.put_line ( recompile_record.owner

 || '.'

 || recompile_record.object_name

 || ' is a '

 || recompile_record.object_type

 || ' and can not be recompiled.'

 );

 END IF;

 

 type_status := invalid_type;

 END IF;

 END LOOP;

 

 DBMS_SQL.close_cursor ( dyncur );

 RETURN type_status + parent_status + recompile_status;

EXCEPTION

WHEN OTHERS

THEN

IF obj_cursor%ISOPEN

THEN

 CLOSE obj_cursor;

 END IF;

 

 IF recompile_cursor%ISOPEN

 THEN

 CLOSE recompile_cursor;

 END IF;

 

 IF invalid_parent_cursor%ISOPEN

 THEN

 CLOSE invalid_parent_cursor;

 END IF;

 

 IF status_cursor%ISOPEN

 THEN

 CLOSE status_cursor;

 END IF;

 

 IF DBMS_SQL.is_open ( dyncur )

 THEN

 DBMS_SQL.close_cursor ( dyncur );

 END IF;

 

 RAISE;

END;

Se quizer mais uma dica para Recompilar todos os objetos, dentro do Software do Oracle Server, existem scripts capazes de realizar essa tarefa também, exemplo:

SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql
Execute com o usuário SYS.

Abraços, :lol: