What is the SQLPlus command to view a VIEW statement?

Question

I just found out that one of the queries that I run does not come from the actual table, but from VIEW.

I would like to see the SQL query in this VIEW. If I try to describe , I get an object, there is no error. When I select from a view, I get some data.

Answer 1

To see the SQL underlying the view, you need to query the data dictionary. Try

 select view_name, text from user_views where view_name = 'MY_VIEW'; 

If your user does not own the view, try all_views ;

Answer 2

If you want to see the actual SQL used to create the view, you can use the dbms_metadata.get_ddl function, which returns clob:

 select dbms_metadata.get_ddl ( 'VIEW' , 'MY_VIEW' -- view name ) from dual 

There are several more options if you need them.

If you just want to describe it as usual. If this does not work, you are mistaken in the scheme or the object does not exist:

 DESC MY_VIEW 

If you are in the wrong scheme, you can use:

 select dbms_metadata.get_ddl ( 'VIEW' , 'MY_VIEW', -- view name , 'MY_SCHEMA' ) from dual 

or

 DESC MY_SCHEMA.MY_VIEW 

Answer 3

Or better yet, click on the view in the left pane of the connections in SQLDeveloper. After opening the view, click the SQL tab to see the underlying SQL. To find out which user / schema is used in the view

 select owner from all_objects where object_name='VIEW_NAME' and object_type='VIEW';