sql - w3schools - update view in oracle
How to retrieve the SQL used to create a view in Oracle? (4)
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.text column 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.
How can I retrieve the SQL used to create a view, exactly as it was entered, without glob expansion?
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.
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.
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.
You can use data dictionary views (V$SQL, V$SQLTEXT) to see the actual SQL entered.
Please see this related question.
You can use the following query:
SELECT VIEW_NAME, TEXT FROM USER_VIEWS;
or you can use ALL_VIEWS, as in
SELECT VIEW_NAME, TEXT FROM ALL_VIEWS;