当前位置:首页 > 开发 > 数据库 > 正文

Oracle 11g Fine-grained Dependency Management(原创)

发表于: 2013-10-27   作者:czmmiao   来源:转载   浏览次数:
摘要: Minimal Invalidation of Dependent ObjectsIn the previous release, Oracle automatically invalidated all dependent views and PL/SQL packages during an online redefinition, even if those objects weren’t

Minimal Invalidation of Dependent Objects
In the previous release, Oracle automatically invalidated all dependent views and PL/SQL packages during an online redefinition, even if those objects weren’t logically affected. For example, if you dropped a table column during redefinition, all procedures and views that referenced the table were automatically invalidated. Unlike in the previous releases, Oracle Database 11g invalidates only the logically affected objects such as views and synonyms during an online redefinition. It doesn’t automatically invalidate all dependent views and PL/SQL packages as before. In the case of a dropped column, Oracle will invalidate a procedure or view only if the object used the dropped column. This new concept of minimal validation of dependent objects is called fine-grained dependency management, under which the database tracks object dependencies at the level of the element within a unit.
Triggers continue to be automatically invalidated as before during an online redefinition.
Objects such as views, synonyms, and other similar table-dependent objects aren’t logically affected by a table redefinition and thus aren’t invalidated. Thus, for example, if an object referenced during an online redefinition isn’t modified during the redefinition, the object remains valid. All triggers that are defined on a redefined table will be invalidated, but the database automatically revalidates them when the next DML statement execution takes place.The use of fine-grained dependencies leads to more precise dependency metadata.
In Oracle Database 10g, the object dependency metadata was looked at from the object level. For example, let’s say a view depends on a specific table. Even though the addition of a new column to the table has no bearing on the view, the database still invalidates the view because it treats the entire object as the unit of reference. In Oracle Database 11g, the fact that a new column has been added to a table doesn’t invalidate a view that uses a table if the view doesn’t use the newly added column. Similar fine-grained dependency management applies to PL/SQL objects such as procedures and functions. If you add a new procedure or function to a package, that will invalidate other procedures and functions in that package only if those objects have a dependency on the altered or new procedure or function.
The fine-grained dependency management is easy to use, as it doesn’t need any configuration on your part. Your application availability will be higher as a result, especially during an application upgrade.

Sample

In previous releases, object dependencies were managed at the object level, so altering an object automatically invalidated all dependent objects. Oracle 11g has more granular dependency management, so only changes that directly affect an object will cause an invalidation. To show this create a table with a dependent package, which is in turn used by a view.
CREATE TABLE dep_tab (
  id          NUMBER,
  description VARCHAR2(50),
  CONSTRAINT dep_tab_pk PRIMARY KEY (id)
);
CREATE OR REPLACE PACKAGE dep_api AS
  FUNCTION get_desc (p_id  IN  dep_tab.id%TYPE)
    RETURN dep_tab.description%TYPE;
END dep_api;
/
CREATE OR REPLACE PACKAGE BODY dep_api AS
  FUNCTION get_desc (p_id  IN  dep_tab.id%TYPE)
    RETURN dep_tab.description%TYPE
  AS
    l_description dep_tab.description%TYPE;
  BEGIN
    SELECT description
    INTO   l_description
    FROM   dep_tab
    WHERE  id = p_id;
    RETURN l_description;
  END get_desc;
END dep_api;
/
CREATE OR REPLACE VIEW dept_tab_v AS
SELECT id, dep_api.get_desc(id) AS description
FROM   dep_tab;
The objects in the schema are valid.
COLUMN object_name FORMAT A30
SELECT object_name, object_type, status FROM user_objects;
OBJECT_NAME                    OBJECT_TYPE         STATUS
------------------------------ ------------------- -------
DEP_TAB                        TABLE               VALID
DEP_TAB_PK                     INDEX               VALID
DEP_API                        PACKAGE             VALID
DEP_API                        PACKAGE BODY        VALID
DEPT_TAB_V                     VIEW                VALID
5 rows selected.
Add a column to the table and check the status of the schema objects. Prior to 11g we would expect both the package and the view to be invalidated by this operation.
ALTER TABLE dep_tab ADD (
  record_type NUMBER(1)
);
SELECT object_name, object_type, status FROM user_objects;
OBJECT_NAME                    OBJECT_TYPE         STATUS
------------------------------ ------------------- -------
DEP_TAB                        TABLE               VALID
DEP_TAB_PK                     INDEX               VALID
DEP_API                        PACKAGE             VALID
DEP_API                        PACKAGE BODY        INVALID
DEPT_TAB_V                     VIEW                VALID
5 rows selected.
Notice the package specification and the view are not invalidated, because the table columns they reference are not changed by the addition of the new column. The package body is invalidated, but this is of little consequence since dependent objects reference the package specification, not the package body.
Next, recreate the package specification, adding the prototype for a new procedure, then check the status of the schema objects. We would expect this operation to invalidate the dependent view in releases prior to 11g.
CREATE OR REPLACE PACKAGE dep_api AS
  FUNCTION get_desc (p_id  IN  dep_tab.id%TYPE)
    RETURN dep_tab.description%TYPE;
  PROCEDURE stub;
END dep_api;
/
SELECT object_name, object_type, status FROM user_objects;
OBJECT_NAME                    OBJECT_TYPE         STATUS
------------------------------ ------------------- -------
DEP_TAB                        TABLE               VALID
DEP_TAB_PK                     INDEX               VALID
DEP_API                        PACKAGE             VALID
DEP_API                        PACKAGE BODY        INVALID
DEPT_TAB_V                     VIEW                VALID
5 rows selected.
The dependent view is unaffected by the modification to the package specification.

 

参考至: 《McGraw.Hill.OCP.Oracle.Database.11g.New.Features.for.Administrators.Exam.Guide.Apr.2008》            http://www.oracle-base.com/articles/11g/enhanced-finer-grained-dependency-management-11gr1.php
本文原创,转载请注明出处、作者

如有错误,欢迎指正

邮箱:czmcj@163.com

Oracle 11g Fine-grained Dependency Management(原创)

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
Overview In Oracle Database 10g, Oracle introduced the new Transparent Data Encryption (TDE)
Automatic Diagnostic Repository The automatic diagnostic repository tracks all problems and i
WCF中的Session 我们知道,WCF是MS基于SOA建立的一套在分布式环境中各个相对独立的Application进行Co
原文地址:http://www.2cto.com/database/201208/150620.html 一、Oracle 下载 注意Oracle分成两个文
oracle 11g 装好以后好久木有动了,今天找健健来一起研究,真是好。 先把oracle服务打开,这家伙太
一、Oracle 下载 注意Oracle分成两个文件,下载完后,将两个文件解压到同一目录下即可。 路径名称中
Overview of Table Clusters A table cluster is a group of tables that share common columns and
一、硬件检测 1、内存检测 oracle11g要求最低1GB内存 命令: grep MemTotal /proc/meninfo 2、交换
数据库和client在不同的机器之上。 在安装之前,在安装Oracle数据库的server上导航到以下的文件夹。
数据库和client在不同的机器之上。 在安装之前,在安装Oracle数据库的server上导航到以下的文件夹。
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号