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

Dig deeper on Oracle database backup and recovery

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close