Oracle 对象类型判断与依赖表清单分析

在 Oracle 数据库中，开发和运维过程中，经常会遇两类需求：

1. 根据对象名判断类型（表、视图、存储过程、函数或包）
2. 分析对象依赖，整理最终使用的表清单（包括视图嵌套、存储过程调用等复杂场景）

本文将系统介绍如何利用 Oracle 数据字典视图完成这两类任务，并提供完整示例 SQL。

1️⃣ 根据对象名判断类型（表 / 视图 / 存储过程 / 函数 / 包）

1.1 数据字典视图概览

Oracle 提供了多个数据字典视图，用于查看对象信息：

1.2 基础查询

SELECT object_name, object_type
FROM user_objects
WHERE object_name = UPPER('你的对象名');

注意：Oracle 对象默认大写，如果不加 UPPER() 可能匹配不到。

可能返回很多不相关对象，例如：索引、序列、表分区等。如果只关心核心类型，需要额外过滤：

SELECT object_name, object_type
FROM user_objects
WHERE object_name = UPPER('你的对象名')
  AND object_type IN ('TABLE', 'VIEW', 'PROCEDURE', 'FUNCTION', 'PACKAGE');

1.3 多 Schema 支持

如果对象可能存在于其他用户下：

SELECT owner, object_name, object_type
FROM all_objects
WHERE object_name = UPPER('你的对象名')
  AND object_type IN ('TABLE', 'VIEW', 'PROCEDURE', 'FUNCTION', 'PACKAGE');

2️⃣ 分析对象依赖，整理最终表清单

2.1 问题背景

在复杂系统中：

• 视图可能依赖视图 → 再依赖表
• 存储过程可能依赖子存储过程 → 视图 → 表
• 动态 SQL（EXECUTE IMMEDIATE）不会被自动追踪

目标是 递归展开依赖链，最终得到使用的 物理表清单，同时排除系统对象（如 SYS.DUAL）。

2.2 使用 ALL_DEPENDENCIES 递归查询

通用递归 SQL（视图 / 存储过程 /函数 / 包）

SELECT *
FROM (
    SELECT *
    FROM all_dependencies d
    CONNECT BY PRIOR d.referenced_owner = d.owner
           AND PRIOR d.referenced_name  = d.name
           AND PRIOR d.referenced_type  = d.type
    START WITH d.name IN (
               '你的对象名',
               '...'
           )
           AND d.type IN ('VIEW', 'PROCEDURE', 'FUNCTION', 'PACKAGE')
)
WHERE referenced_type = 'TABLE'  -- 只保留最终表
  AND referenced_owner NOT IN ('SYS', 'SYSTEM')  -- 排除系统对象
  AND referenced_name  <> 'DUAL'
ORDER BY referenced_owner, referenced_name;

结果说明

2.3 注意事项

1. ALL_DEPENDENCIES 只能跟踪 静态 SQL，动态 SQL 需要解析 ALL_SOURCE。
2. 如果对象跨 schema，递归查询时不要限制 OWNER，否则可能漏掉依赖。
3. 为了完整追踪依赖链，CONNECT BY PRIOR 建议加上 referenced_type 比较。

3️⃣ 总结

• 对象类型判断：使用 USER_OBJECTS / ALL_OBJECTS + object_type 过滤即可
• 依赖分析：使用 ALL_DEPENDENCIES 结合层次查询 CONNECT BY PRIOR，最终过滤 TABLE，排除系统对象
• 结合这两者，就可以快速得到 对象类型 + 最终表清单，方便数据库维护、权限审查或数据血缘分析