- If you alter the definition of a referenced object, dependent objects might not continue to function without error, depending on the type of alteration. For example, if you drop a table, no view based on the dropped table is usable.
[ALL_ | DBA_ | USER_]DEPENDENCIES:
describe dependencies among db objectsSQL> create or replace view highsal as select * from employees where salary >=100000; SQL> create or replace view commissioned as select first_name, last_name, commission_pct from employees where commission_pct > 0.00; SQL> select object_name, status from user_objects where object_type = 'VIEW' order by object_name; OBJECT_NAME STATUS ---------------- ------- COMMISSIONED VALID HIGHSAL VALID SQL> alter table employees modify email varchar2(100); SQL> select object_name, status from user_objects where object_type = 'VIEW' order by object_name; OBJECT_NAME STATUS ---------------- ------- COMMISSIONED VALID HIGHSAL INVALID SQL> select name, type, referenced_name, referenced_type, dependency_type from user_dependencies order by name; NAME TYPE REFERENCED_NAME REFERENCED_TYPE DEPENDENCY_TYPE ----------- ------------ --------------- -------------- ------------- CIRCLE_AREA PROCEDURE DBMS_OUTPUT SYNONYM HARD CIRCLE_AREA PROCEDURE MY_PKG PACKAGE HARD ... COMMISSIONED VIEW EMPLOYEES TABLE HARD HIGHSAL VIEW EMPLOYEES TABLE HARD ... MY_PKG PACKAGE BODY MY_PKG PACKAGE HAD MY_PKG PACKAGE BODY DBMS_DB_VERSION PACKAGE HARD ... MY_PKG PACKAGE DBMS_DB_VERSION PACKAGE HARD
- Cascading invalidation: Indirect dependents can be invalidated by changes to the reference object that do not affect them.
- If a change to C invalidates B, it invalidates A (and all other direct and indirect dependents of B).
- Coarse-grained invalidation: DDL statement that changes a referenced object invalidates all of its dependents.
On 11g R1: Fine-grained invalidation |
- Fine-grained invalidation: DDL statement that changes a referenced object invalidates only dependents for which:
- Dependent relies on the attribute of the referenced object that the DDL statement changed.
- The compiled metadata of the dependent is no longer correct for the changed referenced object.
- For example, if view v selects columns c1 and c2 from table t, a DDL statement that changes only column c3 of t does not invalidate v.
Minimize object invalidation
(a) Add Items to End of Package
This preserves the entry point numbers of existing top-level package items, preventing their invalidation.
CREATE OR REPLACE PACKAGE pkg1 IS FUNCTION get_var RETURN VARCHAR2; END; -- Adding an item to the end of pkg1, as follows, -- does not invalidate dependents that reference the get_var function: CREATE OR REPLACE PACKAGE pkg1 IS FUNCTION get_var RETURN VARCHAR2; PROCEDURE set_var (v VARCHAR2); END; -- Inserting an item between the get_var function and the set_var procedure -- invalidates dependents that reference the set_var function: CREATE OR REPLACE PACKAGE pkg1 IS FUNCTION get_var RETURN VARCHAR2; PROCEDURE assert_var (v VARCHAR2); PROCEDURE set_var (v VARCHAR2); END;
(b) Reference Each Table Through a View
Reference tables indirectly, using views, enabling you to:
- Add columns to the table without invalidating dependent views or dependent PL/SQL objects.
- Modify or delete columns not referenced by the view without invalidating dependent objects
- The statement CREATE OR REPLACE VIEW does not invalidate an existing view or its dependents if the new ROWTYPE matches the old ROWTYPE.
Revalidation
To recompile a schema object using the appropriate SQL statement with the COMPILE clause.
ALTER PACKAGE pkg1 COMPILE REUSE SETTINGS;
Requisites to use UTL_RECOMP package:
- This package must be run using SQL*PLUS.
- You must be connected AS SYSDBA to run this script.
- The following packages should have VALID status:
STANDARD (standard.sql)
DBMS_STANDARD (dbmsstdx.sql)
DBMS_JOB (dbmsjob.sql)
DBMS_RANDOM (dbmsrand.sql)
- No other DDL must be running on the database, or deadlock may result.
- This package uses the job queue for parallel recompilation.
You can use UTL_RECOMP package to recompile one or more invalid objects:
- This is useful after an application upgrade or patch, when is good practice to revalidate invalid objects to avoid application latencies that result from on-demand object revalidation.
-- Recompile all objects sequentially:
EXECUTE UTL_RECOMP.RECOMP_SERIAL();
-- Recompile objects in schema SCOTT sequentially:
EXECUTE UTL_RECOMP.RECOMP_SERIAL('SCOTT');
-- Recompile all objects using 4 parallel threads:
EXECUTE UTL_RECOMP.RECOMP_PARALLEL(4);
-- Recompile objects in schema JOE using the number of threads specified in the parameter JOB_QUEUE_PROCESSES:
EXECUTE UTL_RECOMP.RECOMP_PARALLEL(NULL, 'JOE');
Revalidate individual invalid objects using
DBMS_UTILITY.VALIDATE
. -- Revalidate the procedure UPDATE_SALARY in schema HR:
begin
dbms_utility.validate('HR', 'UPDATE_SALARY', namespace=>1);
end;
-- Revalidate the package body HR.ACCT_MGMT:
begin
dbms_utility.validate('HR', 'ACCT_MGMT', namespace=>2);
end;
No comments:
Post a Comment