Re: Q: how to bring up warnings when objects become 'invalid'

In article <xcmaf6wa8jd.fsf_at_x4u2.desy.de>,   Harald Falkenberg <hfalken_at_x4u2.desy.de> wrote:
>
> Hi,
>
> we have a lot of application developers, who redesign there database
> objects from time to time. Therefore some applications running into problems
> due to objects which become the 'invalid' status. Periodically I check
> for objects in the status 'invalid'. But I'm looking for a way to generate a
> message or mail to give the developer a hint that some of his database
objects
> become the status 'invalid'.
>
> Does someone know how to handle and to achieve this?
>
> Please mail me any hint to
> hfalken_at_x4u.desy.de
>
> regards and thank you in advance
> Harald Falkenberg
>
>

Hi,
look at sys.dba_objects. You can also write a simple sql statement which would return the userid and the count of invalid objects (written to a spool file invalidobjects.lst) for instance. for eg.
select count(*), owner, object_type
from sys.dba_objects
where status = 'INVALID'
group by owner, object_type;

You can automate the above sql statement by using cron (if you are using unix). ie save above SQL to a unix file. create a bsh, ksh, or a csh file with the following in it: ORACLE_SID, ORACLE_HOME PATH to oracle binaries export all the above variables

and simply issue
sqlplus userid/password @sqlscriptname.sql

Just make sure you are able to access sys.dba_objects first!

Now the difficult part:
You can check the spool file to see if there is any values by using the following:
FILE=/tmp/invalidobjects.lst
if [ -f $FILE -a ! -s $FILE ]
then

    echo "All objects valid today. Complilation not required." |mail youremailaddress (in format ename_at_eprovider.com) fi
if [ -f $FILE -a -s $FILE ]
then
mail youremailaddress < /tmp/invalidobjects.lst fi

Hope this helps..
vjoshi_at_pinnacle.co.uk

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Tue Jun 30 1998 - 06:44:18 CDT