How to analyze Object Dependency

最新推荐文章于 2025-05-11 22:38:37 发布

haiross

最新推荐文章于 2025-05-11 22:38:37 发布

阅读量684

点赞数

分类专栏： oracle转载博文未看 oracle数据字典表学习 Oracle数据库基本管理 oracle故障排除 oracle常用实验或管理脚本 oracle安装升级与卸载

Oracle数据库基本管理同时被 3 个专栏收录

337 篇文章

订阅专栏

oracle转载博文未看

321 篇文章

订阅专栏

oracle故障排除

116 篇文章

订阅专栏

本文讨论了Oracle数据库对象部署中对象依赖性的分析，包括包、过程、函数、表、索引等。未进行依赖性分析可能导致数据库出现问题、服务中断及企业愿景受损。文章提供了检查对象依赖性的查询，并详细说明了不同操作对数据库的影响，如SPEC的使用、添加或删除列、添加或删除约束、创建索引和触发器等。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

How to analyze Object Dependency?

July 31, 2011 talipozturk 1 comment

In this article, I will talk about object dependency analysis for oracle database object deployments (package, procedure, function, table, index).

Without dependency analyzing, deployments can cause serious problems in the database. Also it can cause service outage and damage the corporate vision. May be too much dependence of object which we want to deploy on the database. And depended objects may be invalidated. Invalidated object does not constitute any problem if you compile it before using that object by the end user. If invalidated object called by the end user frequently then it cause to take an error by the end user.

Performance as a service quality, is one of the most important factors for us.

So what should we be looking object deployments? How to analyze object dependency? Now let’s search the answer for these questions.

You can check object dependency with following query. As a result of the following query, you must get at least one record (of the object itself). This situation shows the existence of the object. If there is no record in the database then there is no such an object.

SELECT name FROM dba_dependencies d  WHERE (d.referenced_name,d.referenced_type) in(
SELECT name,type FROM dba_dependencies d1 WHERE d1.referenced_name IN ('OBJECT_NAME')
)
UNION
SELECT name FROM dba_dependencies d2 WHERE d2.referenced_name IN ('OBJECT_NAME')
UNION
SELECT object_NAME FROM dba_objects WHERE object_NAME IN ('OBJECT_NAME')

1- SP DEPLOYMENTS:
You must check existence of SPEC on package deployments.If there is SPEC then it will be invalidate all depended objects. In this situation you must check usage of this package. If usage of this package is too much then you musn’t deploy it. Because it causes concurrency waits on database. You can check usage an object with following query;

SELECT name, loads, executions, pins,c.*
FROM v$db_object_cache c
WHERE name in
(SELECT name FROM dba_dependencies d  WHERE (d.referenced_name,d.referenced_type) in(
SELECT name,type FROM dba_dependencies d1 WHERE d1.referenced_name IN ('OBJECT_NAME')
)
UNION
SELECT name FROM dba_dependencies d2 WHERE d2.referenced_name IN ('OBJECT_NAME')
UNION
SELECT object_NAME FROM dba_objects WHERE object_NAME IN ('OBJECT_NAME'))
AND pins >0;

2- ADD, DROP, MODIFY COLUMN Commands:
Adding, Dropping, Modifying a column(ADD, DROP, MODIFY) and modifying constraint will be invalidate packages or stored procedures used by table.You should make dependency analysis for the table. And you can do planned deployment.

3- ADD, DROP CONSTRAINT Commands:
Adding, Dropping constraints (ADD, DROP CONSTRAINT) will not invalidate objects. But it locks the table when the constraint type is PK/FK.

4- CREATE INDEX :
CREATE INDEX command locks the table when it creating index . Be sure that table is not used frequently.

5- CREATE TRIGGER :
Create trigger command doesn’t lock the table. You can create dml trigger on the table during day. When you create insert trigger on a table that insert statements are frequently on, it causes the short-term pin problem.

6- CREATE TABLE:
You must examine constaints in table create script. If there is PK/FK constraint, be sure! because you must consider 3th step.