Logo

Navigation
  • Home
  • Services
    • ERP Solutions
    • Implementation Solutions
    • Support and Maintenance Solutions
    • Custom Solutions
    • Upgrade Solutions
    • Training and Mentoring
    • Web Solutions
    • Production Support
    • Architecture Designing
    • Independent Validation and Testing Services
    • Infrastructure Management
  • Expertise
    • Microsoft Development Expertise
    • Mobile Development
    • SQL Server Database and BI
    • SAP BI, SAP Hana, SAP BO
    • Oracle and BI
    • Oracle RAC
  • Technical Training
    • Learn Data Management
      • Business Intelligence
      • Data Mining
      • Data Modeling
      • Data Warehousing
      • Disaster Recovery
    • Learn Concepts
      • Application Development
      • Client Server
      • Cloud Computing Tutorials
      • Cluster Computing
      • CRM Tutorial
      • EDI Tutorials
      • ERP Tutorials
      • NLP
      • OOPS
      • Concepts
      • SOA Tutorial
      • Supply Chain
      • Technology Trends
      • UML
      • Virtualization
      • Web 2.0
    • Learn Java
      • JavaScript Tutorial
      • JSP Tutorials
      • J2EE
    • Learn Microsoft
      • MSAS
      • ASP.NET
      • ASP.NET 2.0
      • C Sharp
      • MS Project Training
      • Silverlight
      • SQL Server 2005
      • VB.NET 2005
    • Learn Networking
      • Networking
      • Wireless
    • Learn Oracle
      • Oracle 10g
      • PL/SQL
      • Oracle 11g Tutorials
      • Oracle 9i
      • Oracle Apps
    • Learn Programming
      • Ajax Tutorial
      • C Language
      • C++ Tutorials
      • CSS Tutorial
      • CSS3 Tutorial
      • JavaScript Tutorial
      • jQuery Tutorial
      • MainFrame
      • PHP Tutorial
      • VBScript Tutorial
      • XML Tutorial
    • Learn Software Testing
      • Software Testing Types
      • SQA
      • Testing
  • Career Training
    • Career Improvement
      • Career Articles
      • Certification Articles
      • Conflict Management
      • Core Skills
      • Decision Making
      • Entrepreneurship
      • Goal Setting
      • Life Skills
      • Performance Development
      • Personal Excellence
      • Personality Development
      • Problem Solving
      • Relationship Management
      • Self Confidence
      • Self Supervision
      • Social Networking
      • Strategic Planning
      • Time Management
    • Education Help
      • Career Tracks
      • Essay Writing
      • Internship Tips
      • Online Education
      • Scholarships
      • Student Loans
    • Managerial Skills
      • Business Communication
      • Business Networking
      • Facilitator Skills
      • Managing Change
      • Marketing Management
      • Meeting Management
      • Process Management
      • Project Management
      • Project Management Life Cycle
      • Project Management Process
      • Project Risk Management
      • Relationship Management
      • Task Management
      • Team Building
      • Virtual Team Management
    • Essential Life Skills
      • Anger Management
      • Anxiety Management
      • Attitude Development
      • Coaching and Mentoring
      • Emotional Intelligence
      • Stress Management
      • Positive Thinking
    • Communication Skills
      • Conversation Skills
      • Cross Culture Competence
      • English Vocabulary
      • Listening Skills
      • Public Speaking Skills
      • Questioning Skills
    • Soft Skills
      • Assertive Skills
      • Influence Skills
      • Leadership Skills
      • Memory Skills
      • People Skills
      • Presentation Skills
    • Finding a Job
      • Etiquette Tips
      • Group Discussions
      • HR Interviews
      • Interview Notes
      • Job Search Tips
      • Resume Tips
      • Sample Resumes
 

Manage Oracle dependencies

By Saurabh Gupta | on August 16, 2011 |
Oracle 11g Tutorials

mod001.jpgOracle schema is the home for all the database objects. It shelters the entire set of objects which participate in the data storage layer (tables, views) and data access layer (package, procedures, functions, triggers). These objects can establish reference across themselves to embed one or the other application logic. This relationship between the two participating objects is known as Oracle Dependency; where one object acts as ‘Dependent object’, while the other one plays the role of ‘Referenced object’.

Dependent and Referenced Object

If an object A uses another object B within its definition, then the object A is known as Dependent object (on B). In the same case, the object B would be referred as Referenced object. Once the object A is compiled successfully, it establishes the relationship with the object B by virtue of its state. This implies that Oracle server identifies the state of an object as the relationship property between two related objects.

As stated, any object can behave as dependent or referenced object in particular situation, but this is not always true. Synonyms can always be categorized under referenced objects. Similarly, package body can always be a dependent object.

Direct and Indirect dependency

Conventional Dependency Management

Usually, if the referenced object undergoes a change, all its dependent objects are marked invalid. If the change is significant for the dependent object, it would be validated by the server in the very next call. This ‘DEPENDS ON’ matrix is maintained internally by Oracle. Oracle database implicitly toggles over the status of the objects and voluntarily takes decision to validate or invalidate them. The status of an object is determined by the value in the STATUS column of USER_OBJECTS dictionary view. It can be VALID for validated objects or INVALID for invalidated objects.

For instance, a procedure P_GET_SAL queries the EMPLOYEES table to get the salary of an employee. As per the dependency equation, P_GET_SAL is dependent on EMPLOYEES (referenced) table. If EMPLOYEES table is altered to add a new column “commission”, procedure P_GET_SAL is immediately invalidated. Hereafter, whenever P_GET_SAL would be invoked, it would throw exception of ‘Snapshot too old’. But in the very next call, Oracle server compiles the invalidated objects.

Apart from the above scenario, object dependency might bump the database business layer activities for a while. In addition, if the database makes use of synonyms and view, it always runs the threat of being affected by object dependencies.

 

Oracle 10g even made certain modifications in synonyms to succumb over the dependency issue. Oracle 11g implemented an algorithm, known as Fine Grained Dependency, to handle the dependency from the depth. The new concept has changed the granularity of dependency from object to element. Let us now go through the concept in detail.

Recapitulate Object status in a schema

Now, let us take a look at the possible status(s) of an object in a schema. The object status gets updated in the STATUS column of [ALL | DBA | USER]_OBJECTS dictionary view for all objects within the database/schema. The column can also be found in [ALL | DBA | USER]_PROCEDURES which holds subprogram and package information only. I will list the probable status(s) of an existing object.

 

Status

Description

VALID

The object exists in compiled state. All referenced objects too are stable

COMPILED WITH ERRORS

The objects failed to compile due to some syntactical errors.

INVALID

The object exists with its referenced objects altered and modified

UNAUTHORIZED

If any privilege has been revoked from the referenced object, dependent object is marked UNAUTHORIZED

USER_DEPENDENCIES view

The dependency matrix of an object is maintained by [ALL | DBA | USER]_DEPENDENCIES dictionary view in Oracle. It captures the referenced object’s name, type, owner, and db link information.

 

Track Direct and Indirect dependency views

Besides USER_DEPENDENCIES dictionary view, Oracle provides two additional views to track direct and indirect dependency of the object. These views are DEPTREE (direct dependencies) and IDEPTREE (indirect dependencies), which do not exist in the schema as other dictionary view, but their script can be found in ORACLE_HOME.

Steps to follow

1. Execute the UTLDTREE.sql script from ORACLE_HOME/rdbms/admin folder.
2. Execute the DEPTREE_FILL procedure to populate the DEPTREE_TEMPTAB table.

 

How to use?

Suppose, I want to check the dependency level of a procedure P_GET_SAL, I would execute the DEPTREE_FILL procedure in the below fashion.

EXEC DEPTREE_FILL(‘PROCEDURE’,’SCOTT’,’P_GET_SAL’);

Above statement would populate the dependency matrix into DEPTREE_TEMPTAB table. The view DEPTREE AND IDEPTREE are created on top of DEPTREE_TEMPTAB.

Fine grained dependency

Fine grained dependency is one of the key enhancements of Oracle 11g. The new concept has taken the object dependency concept to an instrumental level so as to assure minimum hindrance in database object validations.

 

The Concept

The object level dependency has been made more precise to element level. This implies that if the change done to an object does not affect its dependent objects in any manner, they would retain their VALID state.

For example, I have a procedure P_GET_SAL which references EMPLOYEES table to get the salary of an employee. EMPLOYEES table is required to undergo a structural change; it has to be altered to add a new column STATUS.
For the same sequence of actions in Oracle 9i, the procedure P_GET_SAL would have been set as INVALID. This is because the object snapshot is proportional to the status of its referenced objects. If any of the referenced objects attains ‘invalid’ state, the ‘using’ object would also be invalidated.

But now with 11g release, P_GET_SAL would have remained in VALID state even after the EMPLOYEES table is altered. This is because the newly added column is no where used in the package.

Illustrations: Example would better demonstrate the concept.

In Views

Views are one of the most referred examples on object dependency. Let us see how the Oracle dependency has changed its behavior in 11g release, in comparison to its prior releases.
A view VU_EMP has been created on EMP table as show below. Note the query definition of the view as it is build on two columns only.

 

Now, the table EMP has been altered to include the STATUS column.

 

Fetch the STATUS of the view from USER_DEPENDENCIES. It remains in VALID state and thus the entire dependent objects of the view remain valid.

 

 

The same above query when executed in 9i returned INVALID state. The reason for such behavior is that Oracle 9i checks the dependency at object level.

 

In Packages

Many a time, Oracle packages too get affected by Object level Oracle dependency. I would illustrate a simple example.

 

A package PKG_FGD has been created for demonstration purpose. Currently, it has only one subprogram GET_SAL.

Display the content of the EMP nested table through a stored procedure P_PRINT_SAL

 

Check the status of the P_PRINT_SAL

 

Now, I would add one more subprogram GET_JOB to the package as below.

 

Without recompiling the stand alone procedure P_PRINT_SAL, check its status in the USER_DEPENDENCIES table.

 

The same scenario in Oracle 10g (or earlier version) would have yielded INVALID status for the procedure P_PRINT_SAL. It could be recompiled using ALTER PROCEDURE [NAME] RECOMPILE statement. Even Oracle database used to perform auto recompilation of invalidated program units in second invocation, not in the first reference.

Besides the above citations, synonym redefinition is another fair example to demonstrate the change in Oracle dependency. If synonym is redefined using columns with same name and type, the dependent objects would not be invalidated.

 

Conclusion

Fine Grained Dependency tracking has reformed the object dependency policy of databases. The chance of dependent object invalidation has been reduced to the impact of changes in the referenced object. Even redefinitions and restructuring of views, synonyms, tables or packages would not hamper the working of their dependent object.

« « UTL_FILE
Project Manager » »

Author Description

Avatar

Free Training

RSSSubscribe 392 Followers
  • Popular
  • Recent
  • Oracle Pragma

    July 6, 2011 - 0 Comment
  • Oracle 11g SecureFiles

    September 4, 2011 - 0 Comment
  • Oracle 11g Virtual Columns

    May 30, 2011 - 0 Comment
  • UTL_FILE

    August 10, 2011 - 0 Comment
  • Oracle 11g Invisible Index

    May 27, 2011 - 0 Comment
  • Compound Triggers in Oracle 11g

    May 9, 2011 - 0 Comment
  • Oracle 11g Collections

    June 2, 2011 - 0 Comment
  • Oracle VPD implementation

    October 24, 2011 - 0 Comment
  • Oracle 11g Result Cache

    April 28, 2011 - 0 Comment
  • PL/Scope and PL/SQL Hierarchical Profiler

    November 6, 2011 - 0 Comment
  • Oracle XML Storage

    June 19, 2012 - 0 Comment
  • Oracle Analytic Enhancements

    June 19, 2012 - 0 Comment
  • Oracle Partitioning Overview

    January 17, 2012 - 0 Comment
  • PL/Scope and PL/SQL Hierarchical Profiler

    November 6, 2011 - 0 Comment
  • Oracle VPD implementation

    October 24, 2011 - 0 Comment
  • Oracle 11g SecureFiles

    September 4, 2011 - 0 Comment
  • UTL_FILE

    August 10, 2011 - 0 Comment
  • Oracle Pragma

    July 6, 2011 - 0 Comment
  • Conditional compilation in Oracle PL/SQL

    June 28, 2011 - 0 Comment
  • Oracle 11g Subprogram Overloading

    June 17, 2011 - 0 Comment

Exforsys e-Newsletter

ebook
 

Related Articles

  • Oracle XML Storage
  • Oracle Analytic Enhancements
  • Oracle Partitioning Overview
  • PL/Scope and PL/SQL Hierarchical Profiler
  • Oracle VPD implementation

Latest Articles

  • Project Management Techniques
  • Product Development Best Practices
  • Importance of Quality Data Management
  • How to Maximize Quality Assurance
  • Utilizing Effective Quality Assurance Strategies
  • Sitemap
  • Privacy Policy
  • DMCA
  • Trademark Information
  • Contact Us
© 2023. All Rights Reserved.IT Training and Consulting
This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish.AcceptReject Read More
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT