what is SQLPlus command to view a VIEW statement?

Question

I just found out that one of the queries I am running is not coming from actual table, it is coming from a VIEW.

I would like to see the SQL query in that VIEW. If I try to describe I get an object does not exist error. When I SELECT from the view I get some data.

Answer 1

To see the SQL underlying a view, you'll 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, then try all_views;

Answer 2

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

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

There are a few more options if you need them.

If you want to just describe it as usual. If this is not working you're in the wrong schema or the object does not exist:

DESC MY_VIEW

If you're in the incorrect schema you can use:

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

or

DESC MY_SCHEMA.MY_VIEW