Re: How to determine query that created view?

Hugo Toledo, Jr. (hugo_at_mcs.net) wrote:
: clark murray wrote:
: >
: > Given a view is it possible to find out the query that created it.
:
: By query I gather you actually mean the Data Definition Language (DDL)
: that created it. The answer is yes. Look at SYS.VIEW$, if you can (you
: must be granted access to it). The DDL is in the field called "TEXT".
: However, it is stored as a LONG. If using SQL*Plus, set the LONG option
: (which controls how many bytes of LONG type fields are displayed) to
: display more than the default 80 characters allotted.
: --

Or, query user_views. I use the following simple sqlplus script to view views (make sure to SET LONG nnnnn as Hugo suggests). I name the script VIEWL.SQL and run it by typing VIEWL viewname%. Note you can use wildcards (or not) in the viewname.

COLUMN VIEW_NAME       FORMAT A20
COLUMN TEXT            FORMAT A55

SELECT VIEW_NAME, TEXT
FROM USER_VIEWS
WHERE VIEW_NAME LIKE '&1'
ORDER BY 1
/

COLUMN VIEW_NAME CLEAR
COLUMN TEXT CLEAR Tony Damon Received on Mon Apr 01 1996 - 00:00:00 CEST