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.