SQL query Help Needed.

Hi All,
I am writing a checker that should hit all the views one after other in database and report if there is an SQL error ( Page: Data cannot be retrived from the view XYZ).
The query I am running is
"select * from $view fetch first 1 rows only".
Can somebody suggest me a better query which runs faster as I have to query all the views in db in the script ( script runs every 10 mins on cron).

Thanks in advance,
Mani

Not sure if I understand you, If you are trying to check that the all the views are still valid then:

select * from user_objects where status='INVALID'

you can also furthur refine the above query to only see object types of view... I believe the column is object_type but just desc the user_objects view to check.

Hi Hedge,
I need to query each of the views and see if data can be retrived. If it throws an SQL error I need to page myself. Since I have a long list of views (tables) I need a common query that can run fast. Basically this is to check it doen not throw an SQL Error.

Thanks,
Mani.

well, the sql I gave you will accomplish that. It goes at the problem differently, If the view is valid then it is queryable (made up a word).

This however won't tell you if it returns any rows.

Once a view is compiled it will be valid unless an underlying object is dropped or modified.