Dependencies, compilation and warnings

A release adds one nullable column to a table that has five direct dependents, and Oracle recompiles exactly two of the six objects around it. Which two depends on how each unit uses the table, not on how big the change is. Predicting that list is what turns a quiet column addition from a surprise into a plan.

This lesson shows how Oracle records dependencies and status, why a stale unit is not a broken one, and how compiler warnings catch risky code before it runs. Two deeper parts then apply the documented rules to a real change, recompile a schema after a deployment, and cover conditional compilation, optimisation and editions.

The dictionary lists what depends on what and whether it is current

A dependency is a reference from one object to another: a procedure that selects from a table depends on that table. Oracle records every such reference and gives every object a status. The views USER_DEPENDENCIES, ALL_DEPENDENCIES and DBA_DEPENDENCIES list each dependencyobject dependency: A reference from one database object to another, recorded in USER_DEPENDENCIES and checked when the referenced object changes.: the dependent object and its type, and the object and type it references. CREATE statements update the dependencies on their own. Dynamic SQL does not: a statement run with EXECUTE IMMEDIATE creates no dependencies, so a unit that reaches a table only that way is never marked stale when the table changes.

The statusobject status: The STATUS column in USER_OBJECTS: VALID when the object compiled against its current references, INVALID when it compiled with errors, a reference changed or a privilege was revoked. of an object is in USER_OBJECTS. Oracle distinguishes four states: valid, compiled with errors, invalid because something it references changed, and unauthorized because a privilege was revoked. The *_OBJECTS views show the last three all as INVALID. A dependent that references another object is a direct dependent of it, and a dependent of that dependent is an indirect one. The figure shows the six objects of the lesson’s example.

The drawing shows the whole scene; its 4 steps are listed below.

Every step as text

1. Five direct dependents. USER_DEPENDENCIES lists a row for each object that references dep_customers directly. Two views, a function and two procedures name it, so the table has five direct dependents.
2. A sixth object, one step away. dep_report calls dep_name_of and never names the table. It is an indirect dependent of the table, and a direct dependent of the function only.
3. Status says if it is current. USER_OBJECTS.STATUS reads VALID for all six objects after they compile. A change to the table can make a dependent stale, and the status then reads INVALID until Oracle revalidates it.
4. Dynamic SQL leaves no row. A statement run with EXECUTE IMMEDIATE creates no dependency, so a unit that reaches the table only that way never appears in the list and is never marked stale.

A stale unit is revalidated on use, not broken

INVALID means that the compiled form may no longer match what the unit references, not that the code is wrong. Oracle decides how far a change reaches. With coarse-grained invalidation, a DDL statement that changes a referenced object invalidates all of its dependents. With fine-grained invalidationfine-grained invalidation: Invalidation that marks only the dependents a DDL change affects, those that rely on the changed attribute or whose compiled metadata is no longer correct, not every dependent. it invalidates only the dependents that rely on the changed attribute, or whose compiled metadata is no longer correct. A change that invalidates one object also invalidates that object’s dependents, which Oracle calls cascading invalidation.

When someone next uses an invalid PL/SQL unit, Oracle checks whether a referenced object changed in a way that affects it. If so, it recompiles the unit; if not, it revalidates it without recompiling. A recompile that finishes with errors leaves the unit INVALID, and the errors are in USER_ERRORS and in SHOW ERRORS. A package that is invalidated and revalidated also loses its instantiation in every session that held it, which is the discarded state of the packages lesson.

The drawing shows the whole scene; its 6 steps are listed below.

Every step as text

1. The change runs. An ALTER TABLE changes a referenced object. Oracle looks up the dependents of the table and decides, for each one, whether the change affects it.
2. The rules decide. With fine-grained invalidation a dependent is marked only when it relies on what changed or its compiled metadata is no longer correct. With coarse-grained invalidation every dependent is marked.
3. Unaffected units stay VALID. A dependent the change does not touch keeps its status and its compiled form. Nothing is recompiled and nobody notices.
4. Affected units go stale. An affected dependent is marked INVALID, and so are its own dependents by cascade. Nothing is recompiled yet; the work waits for the next use.
5. The next use revalidates. When the unit is referenced, Oracle checks whether a referenced object changed in a way that affects it. If so it recompiles the unit, and if not it revalidates it without recompiling.
6. Success or errors. A clean compile or revalidation makes the unit VALID again. A compile with errors leaves it INVALID, and its errors are in USER_ERRORS and SHOW ERRORS.

Compiler warnings flag risky code before it runs

A warning reports a condition that is not serious enough to stop compilation. The message code has the form PLW-nnnnn, and each warning belongs to one of three categories: SEVERE for conditions that might cause wrong results, such as aliasing problems with parameters; PERFORMANCE for conditions that might slow the code, such as passing a VARCHAR2 value to a NUMBER column; and INFORMATIONAL for code that is merely untidy, such as code that can never run. PLSQL_WARNINGSPLSQL_WARNINGS: A compilation parameter that enables or disables compiler warnings by category or number, or turns a named warning into an error; ENABLE:ALL turns them all on. turns warnings on and off for the instance, the session or one stored unit.

Warnings are read where errors are: in USER_ERRORS and with SHOW ERRORS.

Two parts of depth follow. The first applies Oracle’s rule table to the six objects, predicts what an ADD column and a DROP column invalidate, and recompiles after a deployment.

The second part covers conditional compilationconditional compilation: Directives such as $IF and $ELSE that are evaluated at compile time, selecting which code to include based on version or other constants., which selects code at compile time. It also covers the optimisation levelPLSQL_OPTIMIZE_LEVEL: A compilation parameter that sets how much effort the compiler spends optimising PL/SQL library units. and native compilationnative compilation: Compiling a PL/SQL unit to native machine code instead of interpreting it., which trade compile effort for speed. The last topic is editionsedition-based redefinition: A feature that lets old and new PL/SQL code run side by side in separate editions during online upgrades., which keep old and new code side by side.

Predicting what a DDL change invalidates, and recompiling after it Pro: opens with a Pro, Premium or Ultimate pass

Oracle publishes the rules that decide which dependents a change invalidates, and they can be applied by hand. This part applies them to the six objects, checks the answer with a model and a script, and then recompiles a schema after a deployment and reads what failed.

In this part:

• Applying the rule table to an ADD column
• The same table for a DROP column, and the unit that cannot recompile
• Watching it in the dictionary, and what Oracle’s note adds
• Recompiling after a deployment and reading what failed

1 diagram · 3 runnable examples (Python, SQL) · about 9 minutes

Opening this part with your pass…

This part opens with any MySmartCoPilot pass.

• Pro pass · 1 week · ₹79 · $2.99
• 30 days · ₹179 · $5.99
• 1 year · ₹1,699 · $45

One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.

Choose a passI have a licence key

It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).

Conditional compilation, optimisation, native compilation and editions Pro: opens with a Pro, Premium or Ultimate pass

Some problems are better caught when a unit is compiled than when it runs. This part applies warnings to a unit with a branch that cannot run, selects code by database version, and weighs the optimisation level, native compilation and editions.

In this part:

• Reading a warning, then making it an error
• Choosing code at compile time with conditional compilation
• The optimisation level and native compilation
• Editions and wrapping

4 runnable examples (Python, SQL) · about 6 minutes

Opening this part with your pass…

This part opens with any MySmartCoPilot pass.

• Pro pass · 1 week · ₹79 · $2.99
• 30 days · ₹179 · $5.99
• 1 year · ₹1,699 · $45

One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.

Choose a passI have a licence key

It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).

Practise: predict the invalid units

Each question gives a unit and a change. Work out which exception of the rule applies, if any, before you choose.

Practice that adapts to you

Practise this lesson

7 questions about 9 ideas of this lesson. Each answer is explained at once, and what you get right is kept in this browser.

1. Question 1 of 7 A view and a function both select from a table. Which query lists both as dependents of the table?

   Choose one answer.

   SELECT name, type FROM user_dependencies WHERE referenced_name = 'T' SELECT text FROM user_source WHERE name = 'T' SELECT object_name FROM user_objects WHERE status = 'VALID'

   Show the answer: Question 1 of 7

   Answer: SELECT name, type FROM user_dependencies WHERE referenced_name = 'T'

   Dependencies are recorded when objects are created and listed in the dependencies views.

2. Question 2 of 7 A procedure reaches a table only through EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM dep_customers'. The table is altered. What does USER_DEPENDENCIES say about the procedure?

   Choose one answer.

   It does not list the procedure, so the procedure is not marked stale It lists the procedure with the status INVALID It lists the procedure as a dependent of the table

   Show the answer: Question 2 of 7

   Answer: It does not list the procedure, so the procedure is not marked stale

   The list is complete only for static references. A unit that builds statements at run time needs a source search.

3. Question 3 of 7 Nobody has called an INVALID function since the table changed. What is its compiled form?

   Choose one answer.

   Deleted until someone recompiles it Out of date, and Oracle will check it when the function is next used Still current, because INVALID is only a label

   Show the answer: Question 3 of 7

   Answer: Out of date, and Oracle will check it when the function is next used

   INVALID means stale. The unit is recompiled or revalidated the next time it is referenced.

4. Question 4 of 7 A procedure names a column that has been dropped. It is called, and Oracle tries to recompile it. Where do you find the reason it stays INVALID?

   Choose one answer.

   Nowhere, because the failed compile leaves no record In USER_DEPENDENCIES In USER_ERRORS, or with SHOW ERRORS

   Show the answer: Question 4 of 7

   Answer: In USER_ERRORS, or with SHOW ERRORS

   The error rows name the line and the message, such as an invalid identifier for the dropped column.

5. Question 5 of 7 A package that holds session state is invalidated and then revalidated. What happens to a session that was using it?

   Choose one answer.

   Its state is kept, because only the code changes The session is disconnected Its instantiation, with the state, can be lost

   Show the answer: Question 5 of 7

   Answer: Its instantiation, with the state, can be lost

   This is the ORA-04068 of the packages lesson, reached here through a dependency change instead of a replaced body.

6. Question 6 of 7 Which warning category fits a parameter aliasing problem that might cause wrong results?

   Choose one answer.

   INFORMATIONAL SEVERE PERFORMANCE

   Show the answer: Question 6 of 7

   Answer: SEVERE

   Aliasing is Oracle's example of a SEVERE warning.

7. Question 7 of 7 A unit compiles with a warning. Where can you read it?

   Choose one answer.

   Only in the database alert log In USER_ERRORS, or with SHOW ERRORS In USER_DEPENDENCIES

   Show the answer: Question 7 of 7

   Answer: In USER_ERRORS, or with SHOW ERRORS

   The static view *_ERRORS and the SQL*Plus command SHOW ERRORS show both errors and warnings.

Interview questions

Warm-up (fresher to 2–5 years): how do you find every object that depends on a table?

Query USER_DEPENDENCIES with WHERE referenced_name = 'YOUR_TABLE'; each row gives a dependent object and its type. The ALL_ and DBA_ versions widen the search to other schemas. The list is complete only for static references: a statement run through dynamic SQL creates no dependency, so search the source for the table name as well when a unit builds statements at run time.

Three longer questions follow, about a deployment that left objects invalid, a column addition whose effects you must predict, and an upgrade that cannot take the application offline:

Key takeaways

• USER_DEPENDENCIES lists the references between objects, and USER_OBJECTS.STATUS shows whether each is current.
• INVALID means stale, not broken: Oracle recompiles or revalidates the unit on its next use.
• Fine-grained invalidation marks only the dependents a change affects, and each operation has its own rule table.
• After a deployment, recompile on purpose instead of letting the first caller find a failure.
• Warnings, conditional compilation and editions are settings chosen at compile or upgrade time.

Terms in this lesson

conditional compilation

Directives such as $IF and $ELSE that are evaluated at compile time, selecting which code to include based on version or other constants.

edition-based redefinition

A feature that lets old and new PL/SQL code run side by side in separate editions during online upgrades.

fine-grained invalidation

Invalidation that marks only the dependents a DDL change affects, those that rely on the changed attribute or whose compiled metadata is no longer correct, not every dependent.

native compilation

Compiling a PL/SQL unit to native machine code instead of interpreting it.

object dependency

A reference from one database object to another, recorded in USER_DEPENDENCIES and checked when the referenced object changes.

object status

The STATUS column in USER_OBJECTS: VALID when the object compiled against its current references, INVALID when it compiled with errors, a reference changed or a privilege was revoked.

PLSQL_OPTIMIZE_LEVEL

A compilation parameter that sets how much effort the compiler spends optimising PL/SQL library units.

PLSQL_WARNINGS

A compilation parameter that enables or disables compiler warnings by category or number, or turns a named warning into an error; ENABLE:ALL turns them all on.