如何找到 Oracle 视图的基础列和表名称？

Question

这听起来应该很简单，但事实并非如此！我找不到任何 Oracle（元数据）视图可以提供 Oracle 视图列的基础列和表名称。我发现这样做的唯一方法是解析视图源 SQL（这与精确的科学相去甚远）。

为了解释我想要什么，请考虑我在 SCOTT 模式中创建的以下示例视图：

CREATE OR REPLACE VIEW EMP_DEP
(
   EMPLOYEE_NAME,
   DEPARTMENT_NAME
)
AS
SELECT 
   ENAME,
   DNAME 
FROM
   emp a,
   dept b
WHERE
   a.deptno= b.deptno
/

现在给定视图和列名称 EMP_DEP.DEPARTMENT_NAME，我想获取视图使用的基础表和列名称 DEPT.DNAME。有谁知道不涉及解析视图 SQL 的获取此信息的方法吗？

Answer 1

没有办法，因为每个视图列的定义是一个表达式，而不仅仅是一个表列（通常）。例如，您的视图的 SQL 可能是：

SELECT 
   UPPER(ENAME) || 'xxx',
   myfunction(DNAME)
FROM
   emp a,
   dept b
WHERE
   a.deptno= b.deptno

或者

SELECT ename || 'xxx', dname
FROM (
  SELECT 
     UPPER(ENAME) AS ename,
     myfunction(DNAME) AS dname
  FROM
     emp a,
     dept b
  WHERE
     a.deptno= b.deptno
)

您希望在本示例中看到“底层列”是什么？

Answer 2

可以使用查询选择用于创建视图的表：

select 
  name , 
  type , 
  referenced_name , 
  referenced_type
from 
  user_dependencies 
where 
  name = 'VIEW_NAME' and 
  type = 'VIEW' and  
  referenced_type = 'TABLE';

如果视图列与表列具有相同的列名，请尝试以下查询：

select 
  distinct table_name, column_name 
from 
  all_tab_columns 
where table_name in (select
                        referenced_name
                      from 
                        user_dependencies 
                      where 
                        name = 'VIEW_NAME' and 
                        type = 'VIEW' and  
                        referenced_type = 'TABLE') 
 and column_name in (select 
                        column_name 
                     from 
                        all_tab_columns 
                     where 
                        table_name = 'VIEW_NAME');

Answer 3

由于请求者正在寻找其表的任何实例，而不是针对特定视图，因此我建议：

SELECT *
  FROM DBA_DEPENDENCIES
 WHERE TYPE = 'VIEW'
   AND REFERENCED_TYPE = 'TABLE'
   AND REFERENCED_NAME = '<TABLE_NAME>'

Answer 4

定义视图的 sql 可以在 all_views 中找到，

set long 9999 
select TEXT from all_views where VIEW_NAME='MYVIEW';

这是获取基础表和列的唯一方法。

Answer 5

Oracle 在 11g 中引入了 更细粒度的依赖跟踪。因此数据库知道视图或包主体依赖于哪些表列。然而，他们似乎没有在视图中公开这些数据。但可能有 x$ 表包含该信息。

Answer 6

链接过程可能是一些帮助用于识别依赖关系

DBA_DEPENDENCIES 视图将为您提供视图所基于的表的列表：

SELECT *
  FROM DBA_DEPENDENCIES
 WHERE OWNER = <Schema>
   AND NAME = <View_Name>
   AND TYPE = 'VIEW'

Answer 7

如果您想要视图表和列依赖关系，这很有效：

WITH view_dependencies (view_name, table_name) AS (
    SELECT CONNECT_BY_ROOT d.name AS view_name, d.referenced_name AS table_name
    FROM all_dependencies d
    WHERE d.referenced_type IN ('TABLE', 'VIEW')
    START WITH d.name = UPPER('jtf_rs_resource_extns_vl') AND d.type = 'VIEW'
    CONNECT BY PRIOR d.referenced_name = d.name AND PRIOR d.referenced_type = d.type
)
SELECT deps.view_name, deps.table_name, tbl.table_id, cols.column_id, 
       cols.column_name  
FROM view_dependencies deps
    LEFT JOIN FND_TABLES tbl ON tbl.table_name = deps.table_name
    LEFT JOIN FND_COLUMNS cols ON tbl.table_id = cols.table_id
ORDER BY deps.view_name,  deps.table_name, cols.column_sequence;

输出

VIEW_NAME                   TABLE_NAME            TABLE_ID COLUMN_ID COLUMN_NAME
=======================================================================================
JTF_RS_RESOURCE_EXTNS_VL    JTF_RS_RESOURCE_EXTNS   80056   563724  RESOURCE_ID
JTF_RS_RESOURCE_EXTNS_VL    JTF_RS_RESOURCE_EXTNS   80056   563712  CREATED_BY
JTF_RS_RESOURCE_EXTNS_VL    JTF_RS_RESOURCE_EXTNS   80056   563713  CREATION_DATE
JTF_RS_RESOURCE_EXTNS_VL    JTF_RS_RESOURCE_EXTNS   80056   563718  LAST_UPDATED_BY
JTF_RS_RESOURCE_EXTNS_VL    JTF_RS_RESOURCE_EXTNS   80056   563719  LAST_UPDATE_DATE
JTF_RS_RESOURCE_EXTNS_VL    JTF_RS_RESOURCE_EXTNS   80056   563720  LAST_UPDATE_LOGIN
JTF_RS_RESOURCE_EXTNS_VL    JTF_RS_RESOURCE_EXTNS   80056   563704  CATEGORY
JTF_RS_RESOURCE_EXTNS_VL    JTF_RS_RESOURCE_EXTNS   80056   563725  RESOURCE_NUMBER
JTF_RS_RESOURCE_EXTNS_VL    JTF_RS_RESOURCE_EXTNS   80056   563729  SOURCE_ID
JTF_RS_RESOURCE_EXTNS_VL    JTF_RS_RESOURCE_EXTNS   80056   563686  ADDRESS_ID
JTF_RS_RESOURCE_EXTNS_VL    JTF_RS_RESOURCE_EXTNS   80056   563709  CONTACT_ID
JTF_RS_RESOURCE_EXTNS_VL    JTF_RS_RESOURCE_EXTNS   80056   563721  MANAGING_EMPLOYEE_ID
JTF_RS_RESOURCE_EXTNS_VL    JTF_RS_RESOURCE_EXTNS   80056   563730  START_DATE_ACTIVE
JTF_RS_RESOURCE_EXTNS_VL    JTF_RS_RESOURCE_EXTNS   80056   563714  END_DATE_ACTIVE
JTF_RS_RESOURCE_EXTNS_VL    JTF_RS_RESOURCE_EXTNS   80056   563732  TIME_ZONE
JTF_RS_RESOURCE_EXTNS_VL    JTF_RS_RESOURCE_EXTNS   80056   563711  COST_PER_HR
JTF_RS_RESOURCE_EXTNS_VL    JTF_RS_RESOURCE_EXTNS   80056   563723  PRIMARY_LANGUAGE
JTF_RS_RESOURCE_EXTNS_VL    JTF_RS_RESOURCE_EXTNS   80056   563726  SECONDARY_LANGUAGE
JTF_RS_RESOURCE_EXTNS_VL    JTF_RS_RESOURCE_EXTNS   80056   563717  IES_AGENT_LOGIN
JTF_RS_RESOURCE_EXTNS_VL    JTF_RS_RESOURCE_EXTNS   80056   563728  SERVER_GROUP_ID
JTF_RS_RESOURCE_EXTNS_VL    JTF_RS_RESOURCE_EXTNS   80056   563687  ASSIGNED_TO_GROUP_ID
JTF_RS_RESOURCE_EXTNS_VL    JTF_RS_RESOURCE_EXTNS   80056   563710  COST_CENTER