How do I extract ddl for a view? I want to do it in SQL, not through another Oracle tool.

    Requires Free Membership to View

    Login

Use either the system view called user_views, all_views or dba_views and retrieve the TEXT column. 

SQL> desc user_views
 Name                     Null?    Type
 ------------------------ -------- ---------------
 VIEW_NAME                NOT NULL VARCHAR2(30)
 TEXT_LENGTH                       NUMBER
 TEXT                              LONG
 TYPE_TEXT_LENGTH                  NUMBER
 TYPE_TEXT                         VARCHAR2(4000)
 OID_TEXT_LENGTH                   NUMBER
 OID_TEXT                          VARCHAR2(4000)
 VIEW_TYPE_OWNER                   VARCHAR2(30)
 VIEW_TYPE                         VARCHAR2(30)

SQL> set long 100000
SQL> select text 
  2    from all_views
  3   where view_name = 'ALL_COL_COMMENTS' ;  <--- your view name goes here

TEXT
----------------------------------------------------------
select u.name, o.name, c.name, co.comment$
from sys.obj$ o, sys.col$ c, sys.user$ u, sys.com$ co
where o.owner# = u.user#
  and o.type# in (2, 4, 5)
  and o.obj# = c.obj#
  and c.obj# = co.obj#(+)
  and c.intcol# = co.col#(+)
  and bitand(c.property, 32) = 0 /* not hidden column */
  and (o.owner# = userenv('SCHEMAID')
       or o.obj# in
         (select obj#
          from sys.objauth$
          where grantee# in ( select kzsrorol
                              from x$kzsro
                            )
          )
       or
         exists (select null from v$enabledprivs
                 where priv_number in (-45 /* LOCK ANY TABLE */,
                                       -47 /* SELECT ANY TABLE */,
                                       -48 /* INSERT ANY TABLE */,
                                       -49 /* UPDATE ANY TABLE */,
                                       -50 /* DELETE ANY TABLE */))
      )
      
SQL>

For More Information


This was first published in April 2003

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top