如何找到 Oracle 视图的基础列和表名？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3101078/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I find the underlying column and table name for an Oracle view?
提问by VinceJS
This sounds like it should be simple to do but not so! There is no Oracle (meta-data) view that I can find that gives the underlying column and table name for an Oracle view column. The only way I have found of doing it is to parse the view source SQL (which is far from and exact science).
这听起来应该很简单，但事实并非如此！我找不到提供 Oracle 视图列的基础列和表名称的 Oracle（元数据）视图。我发现这样做的唯一方法是解析视图源 SQL（这与精确科学相去甚远）。
Just to explain what I want, consider the following example view which I created in SCOTT schema:
只是为了解释我想要什么，请考虑我在 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
/
Now given the view and column name EMP_DEP.DEPARTMENT_NAME, I would like to get the underlying table and column name used by the view which is DEPT.DNAME. Does anyone know of a way to get this information that does not involve parsing the view's SQL?
现在给定视图和列名称 EMP_DEP.DEPARTMENT_NAME，我想获取视图使用的基础表和列名称，即 DEPT.DNAME。有没有人知道一种不涉及解析视图 SQL 的方法来获取此信息？
回答by TechDo
Tables used to created the view can be selected using the query:
可以使用查询选择用于创建视图的表：
select
name ,
type ,
referenced_name ,
referenced_type
from
user_dependencies
where
name = 'VIEW_NAME' and
type = 'VIEW' and
referenced_type = 'TABLE';
If view columns have the same column name of table column, then please try the below query:
如果视图列与表列的列名相同，请尝试以下查询：
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');
回答by Dan
Since the requester is looking for any instance of his table, and not with respect to a specific view, I would recommend:
由于请求者正在寻找他的表的任何实例，而不是针对特定视图，我建议：
SELECT *
FROM DBA_DEPENDENCIES
WHERE TYPE = 'VIEW'
AND REFERENCED_TYPE = 'TABLE'
AND REFERENCED_NAME = '<TABLE_NAME>'
回答by Tony Andrews
There isn't a way, because the definition of each view column is an expression, not (in general) merely a table column. For example, your view's SQL could be:
没有办法，因为每个视图列的定义是一个表达式，而不是（通常）仅仅是一个表列。例如，您的视图的 SQL 可能是：
SELECT
UPPER(ENAME) || 'xxx',
myfunction(DNAME)
FROM
emp a,
dept b
WHERE
a.deptno= b.deptno
or perhaps
也许
SELECT ename || 'xxx', dname
FROM (
SELECT
UPPER(ENAME) AS ename,
myfunction(DNAME) AS dname
FROM
emp a,
dept b
WHERE
a.deptno= b.deptno
)
What would you expect to see for the "underlying columns" in this example?
对于此示例中的“基础列”，您希望看到什么？
回答by jim mcnamara
The sql that defined the view can be found in all_views
定义视图的sql可以在all_views中找到
set long 9999
select TEXT from all_views where VIEW_NAME='MYVIEW';
This is the only way to get at the underlying tables and columns.
这是获取底层表和列的唯一方法。
回答by APC
In 11g Oracle introduced finer grained dependency tracking. So the database knows which table columns a view or package body depends on. However, they don't seem to have exposed this data in a view. But there may be x$ tables with the info.
在 11g 中，Oracle 引入了更细粒度的依赖关系跟踪。因此数据库知道视图或包主体依赖于哪些表列。但是，他们似乎没有在视图中公开这些数据。但是可能有 x$ 表包含信息。
回答by Mark Baker
The linked proceduresmay be of some help for identifying dependencies
该链接程序可能会有所帮助识别依赖
The DBA_DEPENDENCIES View will give you a list of the tables that a View is based on:
DBA_DEPENDENCIES 视图将为您提供视图所基于的表的列表：
SELECT *
FROM DBA_DEPENDENCIES
WHERE OWNER = <Schema>
AND NAME = <View_Name>
AND TYPE = 'VIEW'
回答by Ben DeMott
This works well if you want a views table and column dependencies:
如果您想要视图表和列依赖项，这很有效：
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;
Outputs
输出
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
