Renetta.nl

In een Oracle database kunnen objecten soms de status “INVALID” krijgen. Dit kan als oorzaak hebben dat (geneste) afhankelijkheden niet meer beschikbaar zijn.

Een manier om de invalid objects opnieuw te compileren, is om het volgende script te gebruiken. Downloaden kan ook: recompile.zip

Hier is de code:

-- ----------------------------------------------------------
--  OS         : any
--  RDBMS      : Oracle 9i, 10g, 11g
--  Created by : Rene Zijlstra
--  Created on : 14/03/2007
--  Usage      : @[path]recompile[.sql]
--  Version    : 0.3
--  File       : recompile.sql
-- ----------------------------------------------------------
--  Parameters :
--  none
-- ----------------------------------------------------------
--  Remarks:
--  Extremely difficult way to do the same as
--    EXEC DBMS_UTILITY.compile_schema(schema => '<schema>');
-- ----------------------------------------------------------
-- --  Almost as good as Dutch apple-pie                   --
--

cl scr
set feedback off
set serveroutput on
exec   dbms_output.put_line('Gathering info...');
declare
vg_p_cnt        binary_integer;  -- previous number of invalid objects
vg_n_cnt        binary_integer;  -- new number of invalid objects
vg_compile_stmt varchar2(256);   -- the generated compile statement
vg_schema_stmt  varchar2(64);    -- usef for set current schema

-- Just a rough counter
cursor cg_cnt
is
select count(1)
from   sys.dba_objects
where  status != 'VALID'
;

-- cursor with dependencies
cursor cg_obj
is
select object_type
,      owner
,      object_name
from  sys.dba_objects dob
, (select max(level)  dlevel
,       object_id
from public_dependency dep
start with object_id in ( select object_id
from dba_objects dob
where dob.status != 'VALID'
)
connect by object_id = prior referenced_object_id
group by object_id
) lev
where dob.object_id = lev.object_id (+)
and dob.status != 'VALID'
order by lev.dlevel desc
, dob.object_name asc
, case owner
when 'SYS'    then 1
when 'SYSTEM' then 2
else 3
end
, owner
;

procedure pg_report(i_timing in varchar2)
as
vl_ordcol sys.dba_objects.owner%type;
vl_owner  sys.dba_objects.owner%type;
vl_type   sys.dba_objects.object_type%type;
vl_cnt    binary_integer;

cursor cl_obj
is
select  case owner
when 'SYS'    then 1
when 'SYSTEM' then 2
else 3
end case
,  owner
,  object_type   obj
,  count(*)      count
from     sys.dba_objects
where    status = 'INVALID'
group by object_type
,        owner
order by
1
, 2
, case object_type
when 'TYPE'         then 10
when 'TYPE BODY'    then 11
when 'VIEW'         then 20
when 'PROCEDURE'    then 30
when 'FUNCTION'     then 40
when 'PACKAGE'      then 50
when 'PACKAGE BODY' then 51
when 'SNAPSHOT'     then 60
else  70
end
;

begin
case vg_n_cnt
when 0
then dbms_output.put_line (chr(10) || 'Invalid objects ' || i_timing || ' compiling:');
dbms_output.put_line (chr(10) ||'There are no errors' || chr(10) );
else
dbms_output.put_line (chr(10) || 'Invalid objects ' || i_timing || ' compiling:' || chr(10) || chr(10));
dbms_output.put_line ('  Object Owner Object Type    Invalid Objects');
dbms_output.put_line ('  ------------ -----------    ---------------');
end case;
open cl_obj;
loop
fetch cl_obj into vl_ordcol, vl_owner, vl_type, vl_cnt;
exit when cl_obj%notfound;
dbms_output.put_line (rpad(vl_owner,13) || rpad(vl_type,15) || rpad(vl_cnt,15));
end loop;
close cl_obj;
dbms_output.put_line(chr(10));
end pg_report;

procedure p_error( i_owner in  varchar2
, i_type  in  varchar2
, i_name  in  varchar2
)
as

vl_owner sys.dba_errors.owner%type;
vl_err   varchar2(255);   -- the error message of the invalid object

cursor cl_err( i_owner in varchar2
, i_type  in varchar2
, i_name  in varchar2
)
is
select decode(owner,'SYS'   ,'1'
,'SYSTEM','2'
,'3'
)        as ordcol
,      rpad(text,255) as text
from   dba_errors
where owner = i_owner
and   type  = i_type
and   name  = i_name
order by ordcol
;

begin

open  cl_err(i_owner, i_type, i_name);
fetch cl_err into vl_owner, vl_err;
close cl_err;

dbms_output.put_line(i_type || '  ' || i_owner || '.' || i_name);
dbms_output.put_line(vl_err);

end p_error;

begin
dbms_output.enable(1000000);
-- Show session information
dbms_application_info.set_module( 'recompile.sql','Gather info' );

pg_report('before');
loop
open  cg_cnt;
fetch cg_cnt into vg_n_cnt;
close cg_cnt;
exit when vg_p_cnt = vg_n_cnt;

vg_p_cnt := vg_n_cnt;

for rg_obj in cg_obj loop
-- Show session information
dbms_application_info.set_action( 'compile ' || rg_obj.object_type || ' ' || rg_obj.object_name );
-- Build the compile statement for the invalid object

vg_schema_stmt := 'alter session set current_schema='
||case rg_obj.owner
when 'PUBLIC' then 'SYSTEM'
else rg_obj.owner
end
;

vg_compile_stmt := 'alter '
|| case rg_obj.object_type
when 'PACKAGE BODY' then 'PACKAGE '
when 'TYPE BODY'    then 'TYPE '
when 'UNDEFINED'    then 'MATERIALIZED VIEW '
else rg_obj.object_type
end
|| ' "' || rg_obj.owner || '"."'
|| case rg_obj.object_type
when 'JAVA CLASS'  then ' DBMS_JAVA.LONGNAME"(' || rg_obj.object_name || ')" '
else rg_obj.object_name
end
|| case rg_obj.object_type
when 'JAVA CLASS' then ' resolve '
else '" compile '
end
|| case rg_obj.object_type
when 'PACKAGE BODY' then 'BODY '
when 'TYPE BODY'    then 'BODY '
else null
end
;

begin
-- set current schema (Some objects will only compile as owner...)
execute immediate (vg_schema_stmt);

-- Compile the invalid object
execute immediate (vg_compile_stmt);

exception
when others then null;
end;
end loop;
end loop;

-- Show session information
dbms_application_info.set_module( 'recompile.sql', 'Error report' );
-- Show report after compiling
pg_report('after');

-- Show errors on screen
case vg_n_cnt
when 0 then null;
else
dbms_application_info.set_action( 'Error information' );
dbms_output.put_line( 'Errors:' || chr(10) || '-------' );

for rg_obj in cg_obj loop
-- Lookup the error message and log them on screen
p_error(rg_obj.owner, rg_obj.object_type, rg_obj.object_name);
end loop;
end case;


-- Reset session information
dbms_application_info.set_module( null, null );
end;
/