1 / 32

Managing Dependencies

Managing Dependencies. Objectives. After completing this lesson, you should be able to do the following: Track procedural dependencies Predict the effect of changing a database object on stored procedures and functions Manage procedural dependencies. Understanding Dependencies.

patriciamay
Download Presentation

Managing Dependencies

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Managing Dependencies

  2. Objectives After completing this lesson, you should be able to do the following: • Track procedural dependencies • Predict the effect of changing a database object on stored procedures and functions • Manage procedural dependencies

  3. Understanding Dependencies Dependent objects Referenced objects Table View Database trigger Procedure Function Package body Package specification User-defined objectand collection types Function Package specification Procedure Sequence Synonym Table View User-defined objectand collection types

  4. xxxxxxxxxxxxxx vvvvvvvvvvvvvv xxxxxxxxxxxxxx vvvvvvvvvvvvvv xxxxxxxxxxxxxx vvvvvvvvvvvvvv xxxxxxxxxxxxxx vvvvvvvvvvvvvv xxxxxxxxxxxxxx vvvvvvvvvvvvvv Dependencies View or procedure Procedure Table Direct dependency Direct dependency Referenced Dependent Indirect dependency Dependent Referenced

  5. vvvvvvvvvvvvvv xxxxxxxxxxxxxx vvvvvvvvvvvvvv xxxxxxxxxxxxxx vvvvvvvvvvvvvv xxxxxxxxxxxxxx vvvvvvvvvvvvvv xxxxxxxxxxxxxx vvvvvvvvvvvvvv xxxxxxxxxxxxxx vvvvvvvvvvvvvv xxxxxxxxxxxxxx vvvvvvvvvvvvvv vvvvvvvvvvvvvv Local Dependencies Procedure Procedure View Table Local references Direct local dependency

  6. vvvvvvvvvvvvvv xxxxxxxxxxxxxx vvvvvvvvvvvvvv xxxxxxxxxxxxx xxxxxxxxxxxxxx vvvvvvvvvvvvvv xxxxxxxxxxxxxx vvvvvvvvvvvvvv xxxxxxxxxxxxxx vvvvvvvvvvvvvv vvvvvvvvvvvvvv Local Dependencies The Oracle server implicitly recompiles any INVALID object when the object is next called. Procedure Procedure View Table INVALID INVALID INVALID Local references Direct local dependency Definition change

  7. xxxxxxxxxxxxxxxxxxxxx vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv vvvvvvvvvvvvvvvvvvvvv vvvvvvvvvvvvvvvvvvvvv vvvvvvxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxx vvvvvvvvvvvvvvvvvvvvv xxxxxxxxxxxxxxxxxxxxx vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv vvvvvvvvvvvvvvvvvvvvv vvvvvvvvvvvvvvvvvvvvv vvvvvvxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxx vvvvvvvvvvvvvvvvvvvvv A Scenario of Local Dependencies EMP_VW view ADD_EMP procedure … EMPLOYEES table QUERY_EMP procedure …

  8. … … Displaying Direct Dependencies by Using USER_DEPENDENCIES SELECT name, type, referenced_name, referenced_type FROM user_dependencies WHERE referenced_name IN ('EMPLOYEES','EMP_VW' );

  9. Displaying Direct and Indirect Dependencies 1. Run the script utldtree.sql that creates the objects that enable you to display the direct and indirect dependencies. 2. Execute the DEPTREE_FILL procedure. EXECUTE deptree_fill('TABLE','SCOTT','EMPLOYEES')

  10. Displaying Dependencies DEPTREE view SELECT nested_level, type, name FROM deptree ORDER BY seq#; … …

  11. Another Scenario of Local Dependencies xxxxxxxxxxxxxxxxxxxxx vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv vvvvvvvvvvvvvvvvvvvvv vvvvvvvvvvvvvvvvvvvvv vvvvvvxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxx vvvvvvvvvvvvvvvvvvvvv REDUCE_SAL procedure RAISE_SAL procedure xxxxxxxxxxxxxxxxxxxxx vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv vvvvvvvvvvvvvvvvvvvvv vvvvvvvvvvvvvvvvvvvvv vvvvvvxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxx vvvvvvvvvvvvvvvvvvvvv EMPLOYEES table …

  12. xxxxxxxxxxxxxxxxxxxxx vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv vvvvvvvvvvvvvvvvvvvvv vvvvvvvvvvvvvvvvvvvvv vvvvvvxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxx vvvvvvvvvvvvvvvvvvvvv A Scenario of Local Naming Dependencies QUERY_EMP procedure EMPLOYEES public synonym … EMPLOYEES table …

  13. Understanding Remote Dependencies Procedure View Table Procedure xxxxxxxxxxxxxx vvvvvvvvvvvvvv xxxxxxxxxxxxxx vvvvvvvvvvvvvv xxxxxxxxxxxxxx vvvvvvvvvvvvvv xxxxxxxxxxxxxx vvvvvvvvvvvvvv xxxxxxxxxxxxxx vvvvvvvvvvvvvv vvvvvvvvvvvvvv xxxxxxxxxxxxxx vvvvvvvvvvvvvv xxxxxxxxxxxxxx vvvvvvvvvvvvvv xxxxxxxxxxxxxx vvvvvvvvvvvvvv Network Local and remote references Direct local dependency Direct remote dependency

  14. Understanding Remote Dependencies Procedure View Table Procedure xxxxxxxxxxxxxx vvvvvvvvvvvvvv xxxxxxxxxxxxxx vvvvvvvvvvvvvv xxxxxxxxxxxxxx vvvvvvvvvvvvvv xxxxxxxxxxxxxx vvvvvvvvvvvvvv xxxxxxxxxxxxxx vvvvvvvvvvvvvv vvvvvvvvvvvvvv xxxxxxxxxxxxxx vvvvvvvvvvvvvv xxxxxxxxxxxxxx vvvvvvvvvvvvvv xxxxxxxxxxxxxx vvvvvvvvvvvvvv Network VALID INVALID INVALID Local and remote references Definitionchange Direct local dependency Direct remote dependency

  15. Concepts of Remote Dependencies Remote dependencies are governed by the mode that is chosen by the user: • TIMESTAMP checking • SIGNATURE checking

  16. REMOTE_DEPENDENCIES_MODE Parameter Setting REMOTE_DEPENDENCIES_MODE: • As an init.ora parameterREMOTE_DEPENDENCIES_MODE = value • At the system levelALTER SYSTEM SET REMOTE_DEPENDENCIES_MODE = value • At the session levelALTER SESSION SET REMOTE_DEPENDENCIES_MODE = value

  17. Remote Dependencies and Time Stamp Mode Procedure View Table Procedure xxxxxxxxxxxxxx vvvvvvvvvvvvvv xxxxxxxxxxxxxx vvvvvvvvvvvvvv xxxxxxxxxxxxxx vvvvvvvvvvvvvv xxxxxxxxxxxxxx vvvvvvvvvvvvvv xxxxxxxxxxxxxx vvvvvvvvvvvvvv vvvvvvvvvvvvvv xxxxxxxxxxxxxx vvvvvvvvvvvvvv xxxxxxxxxxxxxx vvvvvvvvvvvvvv xxxxxxxxxxxxxx vvvvvvvvvvvvvv Network Network

  18. Remote Dependencies and Time Stamp Mode Procedure View Table Procedure xxxxxxxxxxxxxx vvvvvvvvvvvvvv xxxxxxxxxxxxxx vvvvvvvvvvvvvv xxxxxxxxxxxxxx vvvvvvvvvvvvvv xxxxxxxxxxxxxx vvvvvvvvvvvvvv xxxxxxxxxxxxxx vvvvvvvvvvvvvv vvvvvvvvvvvvvv xxxxxxxxxxxxxx vvvvvvvvvvvvvv xxxxxxxxxxxxxx vvvvvvvvvvvvvv xxxxxxxxxxxxxx vvvvvvvvvvvvvv Network VALID INVALID INVALID Definitionchange Network

  19. Remote Procedure B Compiles at 8:00 a.m. Remote procedure B Compiles Valid

  20. Local Procedure A Compiles at 9:00 a.m. Local procedure A Remote procedure B Time stamp of A Record time stamp of B Time stamp of B Valid Valid

  21. Local procedure A Remote procedure B Time stamp comparison Time stamp of A Time stamp of B Time stamp of B Execute B Valid Valid Execute Procedure A

  22. Remote Procedure B Recompiled at 11:00 a.m. Remote procedure B Compiles Valid

  23. Execute Procedure A Local procedure A Remote procedure B Time stamp comparison Time stamp of A Time stamp of B Time stamp of B ERROR Valid Invalid Valid

  24. Signature Mode • The signature of a procedure is: • The name of the procedure • The data types of the parameters • The modes of the parameters • The signature of the remote procedure is saved in the local procedure. • When executing a dependent procedure, the signature of the referenced remote procedure is compared.

  25. Recompiling a PL/SQL Program Unit Recompilation: • Is handled automatically through implicit run-time recompilation • Is handled through explicit recompilation with the ALTER statement ALTER PROCEDURE [SCHEMA.]procedure_name COMPILE; ALTER FUNCTION [SCHEMA.]function_name COMPILE; ALTER PACKAGE [SCHEMA.]package_name COMPILE [PACKAGE | SPECIFICATION | BODY]; ALTER TRIGGER trigger_name [COMPILE[DEBUG]];

  26. Unsuccessful Recompilation Recompiling dependent procedures and functions is unsuccessful when: • The referenced object is dropped or renamed • The data type of the referenced column is changed • The referenced column is dropped • A referenced view is replaced by a view with different columns • The parameter list of a referenced procedure is modified

  27. Successful Recompilation Recompiling dependent procedures and functions is successful if: • The referenced table has new columns • The data type of referenced columns has not changed • A private table is dropped, but a public table that has the same name and structure exists • The PL/SQL body of a referenced procedure has been modified and recompiled successfully

  28. Recompilation of Procedures Minimize dependency failures by: • Declaring records with the %ROWTYPE attribute • Declaring variables with the %TYPE attribute • Querying with the SELECT* notation • Including a column list with INSERT statements

  29. Packages and Dependencies Stand-aloneprocedure Package specification Valid Procedure Adeclaration Valid Package body Procedure A definition Definition changed

  30. Packages and Dependencies Package specification Valid Procedure Adeclaration Package body Stand-aloneprocedure Procedure A definition Invalid Definition changed

  31. Summary In this lesson, you should have learned how to: • Keep track of dependent procedures • Recompile procedures manually as soon as possible after the definition of a database object changes

  32. Practice 8: Overview This practice covers the following topics: • Using DEPTREE_FILL and IDEPTREE to view dependencies • Recompiling procedures, functions, and packages

More Related