创建系统表视图

CREATE OR REPLACE VIEW SYS.ALL_COL_COMMENTS AS

select u.name, o.name, c.name, co.comment$

from sys."_CURRENT_EDITION_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

  and (o.owner# = userenv('SCHEMAID')

      or o.obj# in

        (select obj#

         from sys.objauth$

         where grantee# in ( selectkzsrorol

                          fromx$kzsro

                        )

         )

      or

        exists (select null fromv$enabledprivs

               wherepriv_number in (-45 ,

                                 -47 ,

                                 -48 ,

                                 -49 ,

                                 -50 ))

     );

//==============================================================

select distinct c.COLUMN_NAME as column_id, c.COMMENTS ascolumn_name

  from all_col_comments c

 where c.TABLE_NAME = 'EMP_ARCHIVES'

   and c.OWNER ='ZYHR'

   and c.COLUMN_NAME not in('EMPID', 'EANAME', 'EAPHOTO')

   and c.COMMENTS is notnull

 order by COLUMN_NAME