如何检索用于在 Oracle 中创建视图的 SQL？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23292159/
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 to retrieve the SQL used to create a view in Oracle?
提问by Andrew
In Oracle, to retrieve the SQL used to create a Function, Package, etc, the user_source view can be queried. However, views are not included in this view - nor do they exist in the underlying sys.source$. To access the text of views, the user_views.textcolumn can be used, but this is not exact because Oracle will re-write some parts of the query, for example it will do glob expansion.
在 Oracle 中，要检索用于创建函数、包等的 SQL，可以查询 user_source 视图。但是，视图不包含在此视图中 - 它们也不存在于基础sys.source$. 要访问视图的文本，user_views.text可以使用列，但这并不准确，因为 Oracle 将重新编写查询的某些部分，例如它会进行 glob 扩展。
How can I retrieve the SQL used to create a view, exactly as it was entered, without glob expansion?
如何在没有全局扩展的情况下检索用于创建视图的 SQL，与输入的完全相同？
采纳答案by vav
I think the original text is lost:
我认为原文丢失了：
create table t1(id number)
/
create view t1_vw as select * from t1
/
alter table t1 add val varchar2(20)
/
alter view t1_vw compile
/
select * from t1_vw
/
will return only id column. Interesting, but for materialized views original text is preserved.
将只返回 id 列。有趣，但对于物化视图，原始文本被保留。
回答by Joseph B
You can use the following query:
您可以使用以下查询：
SELECT VIEW_NAME, TEXT
FROM USER_VIEWS;
or you can use ALL_VIEWS, as in
或者您可以使用 ALL_VIEWS，如
SELECT VIEW_NAME, TEXT
FROM ALL_VIEWS;
References:
参考资料：
回答by Patrick Bacon
I use dbms_metadata.get_ddl as follows:
我使用 dbms_metadata.get_ddl 如下：
select
dbms_metadata.get_ddl('VIEW', 'VIEW_NAME', 'VIEW_OWNER')
from
dual;
Here are the parameter:
下面是参数：
DBMS_METADATA.GET_DDL (
object_type IN VARCHAR2,
name IN VARCHAR2,
schema IN VARCHAR2 DEFAULT NULL,
version IN VARCHAR2 DEFAULT 'COMPATIBLE',
model IN VARCHAR2 DEFAULT 'ORACLE',
transform IN VARCHAR2 DEFAULT 'DDL')
RETURN CLOB;
It has a public synonym. For objects not owned by user, one can obtain the system privilege, SELECT_CATALOG_ROLE, and one can see all DDL for all objects.
它有一个公共的同义词。对于非用户所有的对象，可以获得系统权限，SELECT_CATALOG_ROLE，并且可以看到所有对象的所有DDL。
In Oracle SQL Developer, one can see the clob in the query results window (many people have created utilities to transform clobs as well).
在 Oracle SQL Developer 中，可以在查询结果窗口中看到 clob（许多人也创建了用于转换 clob 的实用程序）。
Most SQL IDE tools have some DDL viewing mechanism, though DBMS_METADATA is seeded Oracle database functionality (one does not need some fancy, expensive tool).
大多数 SQL IDE 工具都有一些 DDL 查看机制，尽管 DBMS_METADATA 是 Oracle 数据库功能的种子（不需要一些花哨、昂贵的工具）。
回答by Eng. Samer T
I think as developer the interest of view is the "select" which make it valid and runnable, and the easiest way to edit a view is by using another application like pl/sql Developer or by TOAD.
我认为作为开发人员，视图的兴趣是使其有效和可运行的“选择”，编辑视图的最简单方法是使用其他应用程序，如 pl/sql Developer 或 TOAD。
editing any object in oracle database by text editor only like sqlplus.exe takes a long time for simple tasks, and it is a headache way to do so.
仅像sqlplus.exe这样的文本编辑器编辑oracle数据库中的任何对象对于简单的任务都需要很长时间，这是一种令人头疼的方法。
回答by Joseph B
You can use data dictionary views (V$SQL, V$SQLTEXT) to see the actual SQL entered.
您可以使用数据字典视图（V$SQL、V$SQLTEXT）查看输入的实际 SQL。
Please see this related question.
请参阅此相关问题。
