Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Wednesday, 4 July 2012

Script: To Report all invalid objects in Database.

This report will show you all objects in the database that have an invalid status. This may be a normal condition in many cases, or it may indicate that an object has been removed. The removal of that object will cause the dependent objects to go invalid.
SELECT owner, object_name, object_type,
       TO_CHAR (last_ddl_time, 'DD-MON-YY hh:mi:ss') last_time
  FROM dba_objects
WHERE status = 'INVALID';

Output Taken from Toad:
OWNER OBJECT_NAME                   OBJECT_TYPE       LAST_TIME
SYS   DBMS_STATS                    PACKAGE BODY      27-AUG-11 10:06:55
HRMS  PAY_ELECTRONIC_FINANCE_FNC    FUNCTION          07-JUN-12 09:24:29
HRMS  PAY_ELECTRONIC_FINANCE_PRC    PROCEDURE         07-JUN-12 09:24:28
HRMS  PAY_LINK_SALARY_TO_FINANCE_FNCFUNCTION          07-JUN-12 09:24:29
HRMS  SPELL_NUMBER_FNC              FUNCTION          07-JUN-12 09:24:29
HRMS  STK_TRANS_GENERAL_PKG         PACKAGE BODY      08-SEP-11 09:38:40
HRMS  UTL_RECOMP                    PACKAGE BODY      07-JUN-12 09:55:56
SHAHID1DBMS_STORAGE_MAP             PACKAGE BODY      24-MAY-12 01:51:40

The below script will show you all objects and their status either valid or invalid. You have to provide the object type such as in above output list Table, Function, procedure, Package Body

SELECT   owner, object_type, object_name, status
    FROM dba_objects
   WHERE object_type LIKE UPPER ('&type')
ORDER BY 1, 2;

0 comments:

Post a Comment