Simple Queries in Oracle

Question

what are the queries to do the following in Oracle?

1. get names of all views which are present ? (Similar to select * from view, but i want to get views not tables)
2. See source of a particular view.

Answer 1

This query will get the names of views in your schema.

select object_name
  from user_objects
 where object_type = 'VIEW'

The query for getting the view source?

SQL> select dbms_metadata.get_ddl('VIEW', 'VIEW_NAME', 'USERNAME')
 2     from dual
 3   /

Answer 2

1. get names of all views:

   a. which are owned by the current user:

   SELECT view_name
   FROM USER_VIEWS;
   

   b. which are visible to the current user:

   SELECT view_name
   FROM ALL_VIEWS;
   

   c. which are present:

   SELECT view_name
   FROM DBA_VIEWS;
   

2. See source of a particular view

   SELECT text FROM xxx_VIEWS WHERE view_name = :myviewname;

(xxx can be USER, ALL or DBA)

DBA_VIEWS

Answer 3

views owned by user

 select * from USER_VIEWS ;

check for oracle objects metadata

http://cisnet.baruch.cuny.edu/holowczak/oracle/sqlplus/

Answer 4

To see the definition of a view in sqlplus:

describe MY_VIEW;