Tuesday, 7 May 2019

Check Invalid objects | Number of All objects in schema


Below query will fetch invalid objects from a particular schema, Provide username at highlighted area.

COLUMN object_name FORMAT A30
SELECT owner,
       object_type,
       object_name,
       status
FROM   dba_objects
WHERE  status = 'INVALID' and owner= '<USERNAME>'
ORDER BY owner, object_type, object_name;


To Fetch only count of all the objects in a schema.

select count(*) from dba_objects where owner='<USERNAME>';


To Fetch count of objects group by object type in schema level.

select object_type,count(*) from dba_objects where owner='<USERNAME>' group by object_type;


No comments:

Post a Comment