Oracle 追溯表被调用的源头SQL

最新推荐文章于 2023-04-03 12:05:26 发布

原创最新推荐文章于 2023-04-03 12:05:26 发布 · 796 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #sql #数据库

技术类同时被 2 个专栏收录

158 篇文章

订阅专栏

#Oracle

59 篇文章

订阅专栏

这篇博客提供了查询Oracle数据库中哪个视图、包或存储过程使用特定表的SQL语句。通过DBA_OBJECTS和PUBLIC_DEPENDENCIES视图，可以追溯到引用指定表的所有对象，按OWNER、OBJECT_NAME和OBJECT_TYPE排序。

Oracle 追溯表被调用的源头SQL

Oracle 根据表名查询调用该表的视图，包，存储过程等

哪些包或者存储过程使用该表

SELECT OWNER,
      OBJECT_TYPE,
      OBJECT_NAME,
      OBJECT_ID,
      STATUS
FROM DBA_OBJECTS DO
WHERE 1 = 1

AND OBJECT_ID IN (SELECT OBJECT_ID
                 FROM PUBLIC_DEPENDENCY PD
                    CONNECT BY PRIOR OBJECT_ID = REFERENCED_OBJECT_ID
                    START WITH REFERENCED_OBJECT_ID =
                               (SELECT OBJECT_ID
                                FROM DBA_OBJECTS
                                WHERE 1 = 1
                                AND OBJECT_TYPE = 'TABLE'
                                AND OBJECT_NAME = '表名'  -- HR_ORGANIZATION_UNITS
                                AND OWNER = 'OWNER名称'))
ORDER BY OWNER,
        OBJECT_NAME,
        OBJECT_TYPE
;