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
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.