Oracle Scripts To Recompile Invalid Objects

One of the classic problems a DBA can have is invalid objects. If a view becomes invalid, any other objects using that view, such as a procedure, also become invalid. Then when the procedure is called, it will not run.

Some scripts that have been useful to me are:

invalid.sql:

Script To Quickly Find Object Info

Here is a script I’ve used to quickly hunt down objects and information about them.

Very useful to find things like: exact spellings, other objects with a similar spelling, or owned by a different user, synonyms, etc.

 

/*

foinfo.sql:  find object info

Script to find objects and information about them.
By Rodger Lepinsky

*/

accept ls_object_name prompt "Enter the object name to find info about:  " ;

select     object_type  ||  '   '  ||  owner || '.' || object_name   ||
               '    ' || object_id 
          as object_and_object_id
/*  ,  CREATED
  ,   TIMESTAMP
  ,   LAST_DDL_TIME    */
from all_objects    /* By Rodger Lepinsky */
/* where upper(object_name) = trim(upper('&ls_object_name'))  */
where upper(object_name) like trim(upper('%&ls_object_name%'))      
/* and object_type IN ('TABLE', 'VIEW')  */ 
order by object_type, owner, object_name
/

In some environments, the different timestamps have been useful. Ie. Has the object just been recompiled?

You can vary the script as you see fit. Change it to find only with an exact spelling. Restrict it to objects types such as a TABLE or VIEW. Check the timestamps. Etc.

Enjoy!

Finding All Generations Of An Object’s Children

Say you have an object, such as a table. What are all objects are dependent on it? That is, all generations. Children, Grandchildren. Great-Grandchildren. Great-Great-Grandchildren. Etc. ??

Four Generations

If you drop that object, all generations of its children will become invalid.

The classic case is a table. There are generations of objects that are built referencing them.

Table
  View
    Package
      Function

Table
  Trigger

etc.

This script will be useful for:
– Determining the Order Of Operations when building objects.
– Determining Multiple dependencies
– Assessing the impact of changing a table structure, or dropping a table.

The Parents And The Order Of Operations

When you build objects in a new environment, you need to build them in the right order of operations.

Until you have all the objects in place, you can’t create a procedure that references them all. And if those objects require more parent objects, they must be created first too. For example:

procedure reads
    view which reads a
       table which is composed of a
          type

—-

Recently I wrote in my other post about the parent and child dependencies. They give a lot of great information. But they only go one level in either direction. As you know, there can be many levels of objects.

What is the order of operations to build them? I’ve written some complex scripts here to find all the successive parents of an object.

Very Useful Scripts To Find Object Dependencies

For DBAs and Developers.

When you are developing schemas, a lot of objects get created. It can be really confusing to keep track of them all. Especially when you now need to create a few dozen in a new environment.

What is even worse, is when you start to maintain databases and schemas that you didn’t build yourself. If you drop a table or unsuccesfully modify a view, all their children will now become invalid. Suddenly child procedures and packages that reference them will not work.

Here you can find some sqlplus scripts that I have used for years to see the dependencies in schema objects. Run the script, and enter in the object you have questions on. Very fast and effective.

They have been extremely useful to determine things like:
– The order of operations to create objects. Parents first. Then children.
– The impact of dropping a view, package, procedure.
– Causes of object invalidation such as another invalid object.
– All the tables that a package is accessing directly. For those multiple 18 table joins.
– Whether a package, function or procedure calls other prodecures.
– Obscure issues, such as references to variables in packages.

————————————————————
————————————————————
————————————————————

To Find Just The Parents of an Oracle Database Object:

Parent objects must be created first, before creating the child object. If the parent object is dropped, all subsequent child objects will become invalid.

This info is very important in new environments, such as moving from Dev to Test. Or Test to Prod.

Oracle Script To Find Tables With No Primary Key

Primary keys are so important in databases.  I keep trying to write an effective post about why they are so important.  But I don’t feel I’m doing the subject justice.  So let me start with a related, but shorter post.

At a number of shops, I’ve come across so many tables that did not have a primary key.  Some of these were very important tables in the system!   Bad, bad, bad.

After discovering so many tables that no primary key, at so many shops, I finally wrote an Oracle script to list all the tables that did not have a PK.