Entre algunas alertas que podemos ver por el Enterprise Manager, podemos ver la de objetos descompilados.
13 object(s) are invalid in the PUBLIC schema.
Me logueo a la instancia y voy a ver cuales son los schemas que tienen objetos descompilados.
Para ello voy a compartirles un script que utilizo para el chequeo y para el armado de la compilacion por schema.
SELECT 'EXECUTE DBMS_UTILITY.COMPILE_SCHEMA(''' || owner || ''',FALSE) ;'
FROM dba_objects
WHERE status = 'INVALID';
En el ejemplo que expongo no solo hay objetos en el schema PUBLIC, si no tambien en el schema QUEST;
$ sqlplus /
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Feb 11 12:04:42 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
ERROR:
ORA-28002: the password will expire within 18446744073709551614 days
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> SELECT 'EXECUTE DBMS_UTILITY.COMPILE_SCHEMA(''' || owner || ''',FALSE) ;'
FROM dba_objects
WHERE status = 'INVALID'; 2 3
'EXECUTEDBMS_UTILITY.COMPILE_SCHEMA('''||OWNER||''',FALSE);'
-----------------------------------------------------------------------------
EXECUTE DBMS_UTILITY.COMPILE_SCHEMA('PUBLIC',FALSE) ;
EXECUTE DBMS_UTILITY.COMPILE_SCHEMA('PUBLIC',FALSE) ;
EXECUTE DBMS_UTILITY.COMPILE_SCHEMA('PUBLIC',FALSE) ;
EXECUTE DBMS_UTILITY.COMPILE_SCHEMA('PUBLIC',FALSE) ;
EXECUTE DBMS_UTILITY.COMPILE_SCHEMA('PUBLIC',FALSE) ;
EXECUTE DBMS_UTILITY.COMPILE_SCHEMA('PUBLIC',FALSE) ;
EXECUTE DBMS_UTILITY.COMPILE_SCHEMA('PUBLIC',FALSE) ;
EXECUTE DBMS_UTILITY.COMPILE_SCHEMA('PUBLIC',FALSE) ;
EXECUTE DBMS_UTILITY.COMPILE_SCHEMA('PUBLIC',FALSE) ;
EXECUTE DBMS_UTILITY.COMPILE_SCHEMA('PUBLIC',FALSE) ;
EXECUTE DBMS_UTILITY.COMPILE_SCHEMA('PUBLIC',FALSE) ;
EXECUTE DBMS_UTILITY.COMPILE_SCHEMA('PUBLIC',FALSE) ;
EXECUTE DBMS_UTILITY.COMPILE_SCHEMA('PUBLIC',FALSE) ;
EXECUTE DBMS_UTILITY.COMPILE_SCHEMA('QUEST',FALSE) ;
14 rows selected.
Con la misma salida compilo ambos schemas.
Lo voy hacer de a uno asi pueden ver con más detalle.
SQL> EXECUTE DBMS_UTILITY.COMPILE_SCHEMA('PUBLIC',FALSE) ;
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> SELECT 'EXECUTE DBMS_UTILITY.COMPILE_SCHEMA(''' || owner || ''',FALSE) ;'
FROM dba_objects
WHERE status = 'INVALID'; 2 3
'EXECUTEDBMS_UTILITY.COMPILE_SCHEMA('''||OWNER||''',FALSE);'
-----------------------------------------------------------------------------
EXECUTE DBMS_UTILITY.COMPILE_SCHEMA('QUEST',FALSE) ;
SQL>
SQL> EXECUTE DBMS_UTILITY.COMPILE_SCHEMA('QUEST',FALSE) ;
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> SELECT 'EXECUTE DBMS_UTILITY.COMPILE_SCHEMA(''' || owner || ''',FALSE) ;'
FROM dba_objects
WHERE status = 'INVALID'; 2 3
no rows selected
SQL>
Resultado. La base con objetos compilados !
