Q

Extracting ddl for a view

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

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 last published in April 2003

Dig Deeper on Oracle database backup and recovery

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close