Query for Compiling INVALID Objects
Dynamic query for generating the compilation command for INVALID Objects
With the article “Query for Compiling INVALID Objects,” we have the opportunity to identify invalid objects and perform compilation.
In the Oracle database, all tables, partitions, indexes, views, packages, procedures, triggers, etc., are identified as objects.
All database objects are visible from the dba_objects table, and to identify the type of object, the OBJECT_TYPE field is present.
For various reasons, an object can transition from a valid state to an invalid state. If a view or an index is based on a table where the creation code (DDL) has changed, or if fields are present in the definition of the index or view, they could become invalid.
The same goes for packages, procedures, triggers, or perhaps a malfunctioning db_link within a procedure that could render the object invalid.
If this situation occurs, it is necessary to compile the invalid objects.
This operation can be performed in various ways.
On this page, we present the method with the SQL code.
If invalid objects are present, we will execute the spooled file named nome_sql.sql generated by the statement.
Query for Compiling INVALID Objects:
set echo off
set head off
set feed off
set ver off
set pages 99
spool nome_sql.sql
select decode( OBJECT_TYPE, 'PACKAGE BODY','alter package ' || OWNER||'.'||OBJECT_NAME || ' compile body;', 'SYNONYM', (decode (OWNER, 'PUBLIC', 'alter public synonym '||OBJECT_NAME||' compile;',
'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || ' compile;')),
'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || ' compile;' )
from dba_objects
where STATUS = 'INVALID'
and OBJECT_TYPE in ( 'PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE', 'TRIGGER',
'VIEW', 'MATERIALIZED VIEW','SYNONYM')
order by OWNER, OBJECT_TYPE, OBJECT_NAME;
spool offPer visionare le mie guide sui Comandi CRS clicca qui!
A questo LINK, invece, troverai le query SQL
Resta aggiornato sulle nostre attività entrando a far parte della nostra community:
Facebook
Youtube
Linkedin
0 Comments