oracle object dependencies – the new oracle DBA reference before using DDL on any objects





You are a new oracle DBA and you want to alter the properties on some objects like tables, indexes, procedures, packages, triggers etc. That means you want to perform DDL on these objects. Before you perform any DDL on these objects you should know what will happen to other objects that are dependent on the objects you are modifying.

Below article explains clearly the care that needs to be taken by the new oracle dba when performing DDL on any database objects.

About Dependencies Among Schema Objects

Some types of schema objects can reference other objects as part of their definition. For example, a view is defined by a query that references tables or other views. A

procedure’s body can include SQL statements that reference other schema objects. An

object that references another object as part of its definition is called a dependent

object, while the object being referenced is a referenced object.

If you alter the definition of a referenced object, dependent objects may become invalid.

The next time that an invalid object is referenced—for example, used in a query or

invoked by a procedure—the database automatically recompiles it before proceeding

with the current operation. In many cases, this restores the validity of the object.

However, some changes to a referenced object cause invalidations to dependent

objects that cannot be remedied by just recompilation. For example, if you drop a

table, any procedure that queries that table will generate errors upon recompilation. In

this case, code changes are required before that procedure can be made valid again.

Even if all currently invalid objects can be made valid again with just recompilation, it

is obvious that a large number of invalid objects can have a negative impact on

application performance. In addition, if a package were to become invalid and then

run by an application, the automatic recompilation of that package could result in an

error ORA-04068 (existing state of package pkg_name has been

discarded) in one or more sessions. As a DBA, you must therefore be aware of object

dependencies before altering the definitions of schema objects.

Oracle Database automatically tracks dependencies among schema objects and tracks

the current state (VALID or INVALID) of every schema object. You can determine the

state of all objects in a schema with the following query:

select object_name, object_type, status from user_objects;

The following are some generalized rules for the invalidation of schema objects:

■If you change the definition of a schema object, dependent objects are cascade

invalidated. This means that if B depends on A and C depends on B, if you change

the definition of A, then B becomes invalid, which in turn invalidates C.

■If you revoke privileges from a schema object, dependent objects are cascade

invalidated.

■There are a small number of situations where altering the definition of a schema

object does not invalidate dependent objects. For example, if a procedure includes

a query on a view, if you change only the view’s WHERE clause, the procedure

remains valid.

Author: admin