Re: ALL_IND_COLUMNS

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 01 Sep 1999 14:01:17 GMT
Message-ID: <37d0314b.98734112@newshost.us.oracle.com>

A copy of this was sent to ray_stanley_at_my-deja.com (if that email address didn't require changing) On Wed, 01 Sep 1999 13:23:57 GMT, you wrote:

>Does anyone know why Oracle switched the
>ALL_IND_COLUMNS.COLUMN_NAME to varchar2(4000) in
>Oracle 8.0.5.0.0? It would seem that they would
>have left the column at varchar2(30) as was in
>7.3.4.
>
>Thanks for any info!
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

it jumped to 4000 on lots of column name related fields. this is because of the object option support. the text of the view in question is now:

tkyte_at_8.0> select text from all_views where view_name = 'ALL_IND_COLUMNS';

TEXT

---

select io.name, idx.name, bo.name, base.name,

       decode(bitand(c.property, 1), 1, ac.name, c.name), ic.pos#, c.length from sys.col$ c, sys.obj$ idx, sys.obj$ base, sys.icol$ ic,

     sys.user$ io, sys.user$ bo, sys.ind$ i, sys.attrcol$ ac where base.obj# = c.obj#

  and ic.intcol# = c.intcol#
  and ic.bo# = base.obj#
  and io.user# = idx.owner#
  and bo.user# = base.owner#
  and ic.obj# = idx.obj#

  and idx.obj# = i.obj#

  and i.type# in (1, 2, 3, 4, 6, 7)
  and c.obj# = ac.obj#(+)
  and c.intcol# = ac.intcol#(+)
  and (idx.owner# = userenv('SCHEMAID') or
       base.owner# = userenv('SCHEMAID')
       or
       base.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 */)
                 )
       )

see the decode() in there. it returns either ac.name (object column information) or c.name -- the sys.col$ column name.

ac.name is a varchar2(4000). Its the cause.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Sep 01 1999 - 09:01:17 CDT