Query to identify invalid objects:
With the article “Query for INVALID Objects,” we have the ability to quickly identify objects that have, for some reason, changed status from VALID to INVALID.
If a code change is made on a DDL, it’s possible for the object to become invalid, which can cause various issues, such as slowdowns or, in the worst-case scenario, blocks.
In such cases, we can easily identify objects experiencing this issue through the query provided below.
The selected fields are the OWNER, representing the object’s owner, the OBJECT_TYPE (whether it’s an INDEX, PACKAGE, PROCEDURE, etc.), the name of the object, and the status of the invalid objects.
The query presents the information by selecting the following fields:
OWNER - Owner of the object
OBJECT_TYPE - Type of object
OBJECT_NAME - Object name
STATUS - Status of the object, whether VALID or INVALID
Additionally, the information is retrieved from the DBA_OBJECTS table, and it should return all objects that are different from VALID, thus indicating all INVALID objects.
Query to identify invalid objects:
set lines 130
col owner for a20
col object_name for a40
col object_type for a25
col status for a20
select owner, object_type, object_name , status
from dba_objects
where status != 'VALID'
order by
owner, object_type;After the article Query for INVALID Objects, to view my guides on CRS Commands, click here!
At this LINK, At this LINK,
Stay updated on our activities by joining our community:
Facebook
Youtube
Linkedin