[20160220]12c视图一些字段显示128列.txt

[20160220]12c视图一些字段显示128列.txt

--今天在家里做一些测试时,无意中发现视图显示字段名index_name占用很大的宽度,仔细检查才发现视图里面的显示长度已经是128列.

SYS@test> @ver1
PORT_STRING          VERSION     BANNER                                                                        CON_ID
-------------------- ----------- ----------------------------------------------------------------------------- ------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0  Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production       0

SYS@test> @ desc dba_indexes
 Name          Null?    Type
 ------------- -------- -------------------------
 OWNER         NOT NULL VARCHAR2(128)
 INDEX_NAME    NOT NULL VARCHAR2(128)
 INDEX_TYPE             VARCHAR2(27)
 TABLE_OWNER   NOT NULL VARCHAR2(128)
 TABLE_NAME    NOT NULL VARCHAR2(128)
...
--可以发现INDEX_NAME, TABLE_OWNER,TABLE_NAME 定义都是128列.

SYS@test> select text from dba_views where view_name='DBA_INDEXES';
TEXT
------------------------------------------------------------------------------------
select u.name, o.name,
       decode(bitand(i.property, 16), 0, '', 'FUNCTION-BASED ') ||
        decode(i.type#, 1, 'NORMAL'||
                          decode(bitand(i.property, 4), 0, '', 4, '/REV'),
                      2, 'BITMAP', 3, 'CLUSTER', 4, 'IOT - TOP',
                      5, 'IOT - NESTED', 6, 'SECONDARY', 7, 'ANSI', 8, 'LOB',
                      9, 'DOMAIN'),
       iu.name, io.name,
       decode(io.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
                       4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 'UNDEFINED'),
       decode(bitand(i.property, 1), 0, 'NONUNIQUE', 1, 'UNIQUE', 'UNDEFINED'),
       decode(bitand(i.flags, 1073741824), 1073741824, 'ADVANCED HIGH',
              decode(bitand(i.flags, 32), 0, 'DISABLED',
                     decode(bitand(i.flags, 2147483648), 0, 'ENABLED',
                            2147483648, 'ADVANCED LOW'))),
       i.spare2,
       decode(bitand(i.property, 34), 0, decode(i.type#, 9, null, ts.name),
           2, null, decode(i.ts#, 0, null, ts.name)),
       decode(bitand(i.property, 2),0, i.initrans, null),
       decode(bitand(i.property, 2),0, i.maxtrans, null),
       decode(bitand(i.flags, 67108864), 67108864,
                     ds.initial_stg * ts.blocksize,
                     s.iniexts * ts.blocksize),
       decode(bitand(i.flags, 67108864), 67108864,
              ds.next_stg * ts.blocksize,
              s.extsize * ts.blocksize),
       decode(bitand(i.flags, 67108864), 67108864,
              ds.minext_stg, s.minexts),
       decode(bitand(i.flags, 67108864), 67108864,
              ds.maxext_stg, s.maxexts),
       decode(bitand(ts.flags, 3), 1, to_number(NULL),
              decode(bitand(i.flags, 67108864), 67108864,
                            ds.pctinc_stg, s.extpct)),
       decode(i.type#, 4, mod(i.pctthres$,256), NULL), i.trunccnt,
       decode(bitand(ts.flags, 32), 32, to_number(NULL),
       decode(bitand(o.flags, 2), 2, 1,
              decode(bitand(i.flags, 67108864), 67108864,
                     decode(ds.frlins_stg, 0, 1, ds.frlins_stg),
                     decode(s.lists, 0, 1, s.lists)))),
       decode(bitand(ts.flags, 32), 32, to_number(NULL),
              decode(bitand(o.flags, 2), 2, 1,
                     decode(bitand(i.flags, 67108864), 67108864,
                            decode(ds.maxins_stg, 0, 1, ds.maxins_stg),
                            decode(s.groups, 0, 1, s.groups)))),
       decode(bitand(i.property, 2),0,i.pctfree$,null),
       decode(bitand(i.property, 2), 2, NULL,
                decode(bitand(i.flags, 4), 0, 'YES', 'NO')),
       i.blevel, i.leafcnt, i.distkey, i.lblkkey, i.dblkkey, i.clufac,
       decode(bitand(i.property, 2), 2,
                   decode(i.type#, 9, decode(bitand(i.flags, 8),
                                        8, 'INPROGRS', 'VALID'), 'N/A'),
                     decode(bitand(i.flags, 1), 1, 'UNUSABLE',
                            decode(bitand(i.flags, 8), 8, 'INPROGRS',
                                                            'VALID'))),
       rowcnt, samplesize, analyzetime,
       decode(i.degree, 32767, 'DEFAULT', nvl(i.degree,1)),
       decode(i.instances, 32767, 'DEFAULT', nvl(i.instances,1)),
       decode(bitand(i.property, 2), 2, 'YES', 'NO'),
       decode(bitand(o.flags, 2), 0, 'N', 2, 'Y', 'N'),
       decode(bitand(o.flags, 4), 0, 'N', 4, 'Y', 'N'),
       decode(bitand(o.flags, 16), 0, 'N', 16, 'Y', 'N'),
       decode(bitand(o.flags, 2), 2, 'DEFAULT',
              decode(bitand(decode(bitand(i.flags, 67108864), 67108864,
                            ds.bfp_stg, s.cachehint), 3),
                            1, 'KEEP', 2, 'RECYCLE', 'DEFAULT')),
       decode(bitand(o.flags, 2), 2, 'DEFAULT',
              decode(bitand(decode(bitand(i.flags, 67108864), 67108864,
                            ds.bfp_stg, s.cachehint), 12)/4,
                            1, 'KEEP', 2, 'NONE', 'DEFAULT')),
       decode(bitand(o.flags, 2), 2, 'DEFAULT',
              decode(bitand(decode(bitand(i.flags, 67108864), 67108864,
                            ds.bfp_stg, s.cachehint), 48)/16,
                            1, 'KEEP', 2, 'NONE', 'DEFAULT')),
       decode(bitand(i.flags, 64), 0, 'NO', 'YES'),
       decode(bitand(o.flags, 2), 0, NULL,
           decode(bitand(i.property, 64), 64, 'SYS$SESSION', 'SYS$TRANSACTION')),
       decode(bitand(i.flags, 128), 128, mod(trunc(i.pctthres$/256),256),
              decode(i.type#, 4, mod(trunc(i.pctthres$/256),256), NULL)),
       itu.name, ito.name, i.spare4,
       decode(bitand(i.flags, 2048), 0, 'NO', 'YES'),
       decode(i.type#, 9, decode(o.status, 5, 'IDXTYP_INVLD',
                                           1, 'VALID'),  ''),
       decode(i.type#, 9, decode(bitand(i.flags, 16), 16, 'FAILED', 'VALID'), ''),
       decode(bitand(i.property, 16), 0, '',
              decode(bitand(i.flags, 1024), 0, 'ENABLED', 'DISABLED')),
       decode(bitand(i.property, 1024), 1024, 'YES', 'NO'),
       decode(bitand(i.property, 16384), 16384, 'YES', 'NO'),
       decode(bitand(o.flags, 128), 128, 'YES', 'NO'),
       decode(bitand(i.flags,2097152),2097152,'INVISIBLE','VISIBLE'),
       decode(i.type#, 9, decode(bitand(i.property, 2048), 2048,
                               'SYSTEM_MANAGED', 'USER_MANAGED'), ''),
       decode(bitand(i.flags, 67108864), 67108864, 'NO',
              decode(bitand(i.property, 2), 2, 'N/A', 'YES')),
       decode(bitand(i.flags, 268435456), 268435456, 'YES', 'NO'),
       decode(bitand(i.flags, 8388608), 8388608, 'PARTIAL', 'FULL')
from sys.ts$ ts, sys.seg$ s,
     sys.user$ iu, sys.obj$ io, sys.user$ u, sys.ind$ i, sys.obj$ o,
     sys.user$ itu, sys.obj$ ito, sys.deferred_stg$ ds
where u.user# = o.owner#
  and o.obj# = i.obj#
  and i.bo# = io.obj#
  and io.owner# = iu.user#
  and bitand(i.flags, 4096) = 0
  and bitand(o.flags, 128) = 0
  and i.ts# = ts.ts# (+)
  and i.file# = s.file# (+)
  and i.block# = s.block# (+)
  and i.ts# = s.ts# (+)
  and i.obj# = ds.obj# (+)
  and i.indmethod# = ito.obj# (+)
  and ito.owner# = itu.user# (+)

--难道oracle低层定义里已经显示定义达到128列.
SYS@test> @desc sys.obj$
Name       Null?    Type
---------- -------- --------------------
OBJ#       NOT NULL NUMBER
DATAOBJ#            NUMBER
OWNER#     NOT NULL NUMBER
NAME       NOT NULL VARCHAR2(128)
NAMESPACE  NOT NULL NUMBER
SUBNAME             VARCHAR2(128)
TYPE#      NOT NULL NUMBER
CTIME      NOT NULL DATE
MTIME      NOT NULL DATE
STIME      NOT NULL DATE
STATUS     NOT NULL NUMBER
REMOTEOWNE          VARCHAR2(128)
LINKNAME            VARCHAR2(128)
FLAGS               NUMBER
OID$                RAW(16)
SPARE1              NUMBER
SPARE2              NUMBER
SPARE3              NUMBER
SPARE4              VARCHAR2(1000)
SPARE5              VARCHAR2(1000)
SPARE6              DATE
SIGNATURE           RAW(16)
SPARE7              NUMBER
SPARE8              NUMBER
SPARE9              NUMBER

--name的定义已经是128列.这么讲oracle的低层已经支持一些对象的定义可以达到128列.

SELECT column_name, COUNT (*)
    FROM dba_tab_cols
   WHERE data_length = 128 AND table_name LIKE 'DBA%'
GROUP BY column_name having count(*)>=30
order by 2 desc;

COLUMN_NAME            COUNT(*)
-------------------- ----------
OWNER                       355
TABLE_NAME                  121
OBJECT_NAME                  74
NAME                         60
COLUMN_NAME                  46
USERNAME                     43
SCHEMA_NAME                  32
OBJECT_OWNER                 30
8 rows selected.

--可以得到一个结论,oracle的低层已经支持更长的对象名.做一个简单的测试:

SCOTT@test01p> create index i_emp_ename_0123456789011234567890 on emp(ename);
create index i_emp_ename_0123456789011234567890 on emp(ename)
             *
ERROR at line 1:
ORA-00972: identifier is too long

SCOTT@test01p> host oerr ora 972
00972, 00000, "identifier is too long"
// *Cause:  An identifier with more than 30 characters was specified.
// *Action:  Specify at most 30 characters.

--兵马未动,粮草先行.
--可以看出oracle可能在下一个版本会支持长度超过30字符,最大128字符的对象名.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-1991846/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-1991846/