[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字符的对象名.