Oracle SQL - Retrieving View Definitions from the Data Dictionary

  1. HOME
  2. Oracle SQL
  3. View
  4. Views Information via Data Dictionary

Introduction

You can query the USER_VIEWS data dictionary view to retrieve your view definitions.

Demo

SQL>
SQL> set    long 999-- from   w  w w  .j a v a  2s . co m
SQL> column text format a42 word wrapped
SQL>
SQL> select view_name, text
  2  from   user_views
  3  where ROWNUM<10;

VIEW_NAME                      TEXT
------------------------------ ------------------------------------------
ALL_ALL_TABLES                 select OWNER, TABLE_NAME, TABLESPACE_NAME,
                                CLUSTER_NAME, IOT_NAME, STATUS,
                                    PCT_FREE, PCT_USED,
                                    INI_TRANS, MAX_TRANS,
                                    INITIAL_EXTENT, NEXT_EXTENT,
                                    MIN_EXTENTS, MAX_EXTENTS, PCT_INCREAS
                               E,
                                    FREELISTS, FREELIST_GROUPS, LOGGING,
                                    BACKED_UP, NUM_ROWS, BLOCKS, EMPTY_BL
                               OCKS,
                                    AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN,


VIEW_NAME                      TEXT
------------------------------ ------------------------------------------


9 rows selected.

SQL>

If you define a view with SELECT * FROM ..., the asterisk (*) gets expanded and stored as a comma-separated list of column names.

SELECT * FROM based view is not dynamic and will not encompass any future columns added to the underlying table.

Related Topic