Monday, January 21, 2008

Get definition for view in Oracle

set LONG 10000 select TEXT from ALL_VIEWS where view_name = 'MY_VIEW';
Line one must be at least the size of the statement used to create the view. Either pick a really big number if you think the view is large or use the following to determine how long it is first. If it isn't, the text will be truncated
select text_length from all_views where view_name = 'MY_VIEW'
NOTE: name of the view is case sensitive as are most every thing in Oracle.
If you want to capture output to something other than a SQL Plus window such as a file, be sure to include do the following before you execute the select statement.
spool c:\v_person.sql
To close the file when you are done writing output to it, call the following:
spool off
To summarize:
select text_length from all_views where view_name = 'MY_VIEW'
-- whatever value you get back, use on the next statement.
set LONG 10000
spool c:\v_person.sql
select TEXT from ALL_VIEWS where view_name = 'MY_VIEW';
spool off


Anonymous said...

Excellent answer. Thanks a lot. I serched many forums to find how to get definition of few views. Every one was saying about dbms_metadata.get_ddl which did not work for me. And many saying just select text from dba_views which could not give me more than 5% of my view's definition. But only this posting worked for me. Thanks again........

Brent V said...

I'm so glad it worked for you! I like this one because it doesn't require as much in the way of special permissions, etc.