Oracle Invalid Objects by Schema

We’ve had a spell of warm weather for the last couple weeks, and I am optimistic we’ll have an early ice-off this spring. The weather has been into the fifties and sixties for the last couple weeks, and even hit seventy yesterday. Last year many lakes up in Oneida and Vilas counties were still covered in ice when the main fishing season opened on May 3rd. This year the ice may be off by April 1st! Although the fishing season doesn’t open for most fish (walleye, northern pike, muskie, bass) until May 2nd, panfish (bluegill, yellow perch) are open all year round. It’s a good time to get some fishing in with the kids.

My oldest son with a freshly caught bluegill

My oldest son with a freshly caught bluegill

Passing on a love for fishing is something I’m hoping to do successfully. I have a couple buddies who still fish regularly with their dads, and I’m hoping when my kids are grown up they’ll still want to spend time fishing with me.
Today I received an alert in Oracle Enterprise Manager. The alert says “Invalid Objects by Schema”, “4 object(s) are invalid in the TAURUS schema”.
20150317 Oracle Invalid Objects OEM
This is a fairly common alert, and it arises when objects that other objects depend on are changed. Usually for me this happens when a table that a stored procedure or view uses as a data source changes. To fix, simply recompile the stored procedure or view. First you’ll need to find which objects are invalid. You can use this SQL statement to find the objects:

SELECT owner,
  OBJECT_NAME,
  object_type,
  status
FROM dba_objects
WHERE status<>'VALID'
ORDER BY owner;

20150317 Oracle Invalid Objects Listed
To fix you just need to recompile the object. You can use this syntax:

--Recompile as sysdba
ALTER VIEW VEXT_MISSING_OPS compile;

Now any invalid objects can be recompiled and your database will stay running smoothly!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s