Object(s) are invalid in the PUBLIC schema

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 !

About Juan Andres
Consultant | Oracle DBA & IT Specialist | LinuxUnix Administrator | Father | Musician | Farmer | Environmentalist | Writer | Builder | Buenos Aires · burzaco.wordpress.com

Comments are closed.

%d bloggers like this: