创建系统表视图

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 ( select kzsrorol

                              from x$kzsro

                            )

          )

        or

          exists (select null from v$enabledprivs

                  where priv_number in (-45 ,

                                        -47 ,

                                        -48 ,

                                        -49 ,

                                        -50 ))

      );

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

select distinct c.COLUMN_NAME as column_id, c.COMMENTS as column_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 not null

  order by COLUMN_NAME