| |
 |
|
DBA_NESTED_TABLE_COLS view tips
Oracle Tips by Burleson Consulting |
DBA_NESTED_TABLE_COLS
<< Return to the index
Oracle 11g makes the following comments about the DBA_NESTED_TABLE_COLS table: "Columns of nested tables"
Related notes on DBA_NESTED_TABLE_COLS:
Column description of the DBA_NESTED_TABLE_COLS view:OWNERNo comments exist for the OWNER column of the DBA_NESTED_TABLE_COLS table. TABLE_NAMEDescription of DBA_NESTED_TABLE_COLS.TABLE_NAME: "Nested table name" COLUMN_NAMEDescription of DBA_NESTED_TABLE_COLS.COLUMN_NAME: "Column name" DATA_TYPEDescription of DBA_NESTED_TABLE_COLS.DATA_TYPE: "Datatype of the column" DATA_TYPE_MODDescription of DBA_NESTED_TABLE_COLS.DATA_TYPE_MOD: "Datatype modifier of the column" DATA_TYPE_OWNERDescription of DBA_NESTED_TABLE_COLS.DATA_TYPE_OWNER: "Owner of the datatype of the column" DATA_LENGTHDescription of DBA_NESTED_TABLE_COLS.DATA_LENGTH: "Length of the column in bytes" DATA_PRECISIONDescription of DBA_NESTED_TABLE_COLS.DATA_PRECISION: "Length: decimal digits (NUMBER) or binary digits (FLOAT)" DATA_SCALEDescription of DBA_NESTED_TABLE_COLS.DATA_SCALE: "Digits to right of decimal point in a number" NULLABLEDescription of DBA_NESTED_TABLE_COLS.NULLABLE: "Does column allow NULL values?" COLUMN_IDDescription of DBA_NESTED_TABLE_COLS.COLUMN_ID: "Sequence number of the column as created" DEFAULT_LENGTHDescription of DBA_NESTED_TABLE_COLS.DEFAULT_LENGTH: "Length of default value for the column" DATA_DEFAULTDescription of DBA_NESTED_TABLE_COLS.DATA_DEFAULT: "Default value for the column" NUM_DISTINCTDescription of DBA_NESTED_TABLE_COLS.NUM_DISTINCT: "The number of distinct values in the column" LOW_VALUEDescription of DBA_NESTED_TABLE_COLS.LOW_VALUE: "The low value in the column" HIGH_VALUEDescription of DBA_NESTED_TABLE_COLS.HIGH_VALUE: "The high value in the column" DENSITYDescription of DBA_NESTED_TABLE_COLS.DENSITY: "The density of the column" NUM_NULLSDescription of DBA_NESTED_TABLE_COLS.NUM_NULLS: "The number of nulls in the column" NUM_BUCKETSDescription of DBA_NESTED_TABLE_COLS.NUM_BUCKETS: "The number of buckets in histogram for the column" LAST_ANALYZEDDescription of DBA_NESTED_TABLE_COLS.LAST_ANALYZED: "The date of the most recent time this column was analyzed" SAMPLE_SIZEDescription of DBA_NESTED_TABLE_COLS.SAMPLE_SIZE: "The sample size used in analyzing this column" CHARACTER_SET_NAMEDescription of DBA_NESTED_TABLE_COLS.CHARACTER_SET_NAME: "Character set name" CHAR_COL_DECL_LENGTHDescription of DBA_NESTED_TABLE_COLS.CHAR_COL_DECL_LENGTH: "Declaration length of character type column" GLOBAL_STATSDescription of DBA_NESTED_TABLE_COLS.GLOBAL_STATS: "Are the statistics calculated without merging underlying partitions?" USER_STATSDescription of DBA_NESTED_TABLE_COLS.USER_STATS: "Were the statistics entered directly by the user?" AVG_COL_LENDescription of DBA_NESTED_TABLE_COLS.AVG_COL_LEN: "The average length of the column in bytes" CHAR_LENGTHDescription of DBA_NESTED_TABLE_COLS.CHAR_LENGTH: "The maximum length of the column in characters" CHAR_USEDDescription of DBA_NESTED_TABLE_COLS.CHAR_USED: "C if the width was specified in characters, B if in bytes" V80_FMT_IMAGEDescription of DBA_NESTED_TABLE_COLS.V80_FMT_IMAGE: "Is column data in 8.0 image format?" DATA_UPGRADEDDescription of DBA_NESTED_TABLE_COLS.DATA_UPGRADED: "Has column data been upgraded to the latest type version format?" HIDDEN_COLUMNDescription of DBA_NESTED_TABLE_COLS.HIDDEN_COLUMN: "Is this a hidden column?" VIRTUAL_COLUMNDescription of DBA_NESTED_TABLE_COLS.VIRTUAL_COLUMN: "Is this a virtual column?" SEGMENT_COLUMN_IDDescription of DBA_NESTED_TABLE_COLS.SEGMENT_COLUMN_ID: "Sequence number of the column in the segment" INTERNAL_COLUMN_IDDescription of DBA_NESTED_TABLE_COLS.INTERNAL_COLUMN_ID: "Internal sequence number of the column" HISTOGRAMNo comments exist for the HISTOGRAM column of the DBA_NESTED_TABLE_COLS table. QUALIFIED_COL_NAMEDescription of DBA_NESTED_TABLE_COLS.QUALIFIED_COL_NAME: "Qualified column name"
DBA_NESTED_TABLE_COLS View SourceOracle 11g's data dictionary defines the DBA_NESTED_TABLE_COLS view using the following source query:
select u.name, o.name,
c.name,
decode(c.type#, 1, decode(c.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'),
2, decode(c.scale, null,
decode(c.precision#, null, 'NUMBER', 'FLOAT'),
'NUMBER'),
8, 'LONG',
9, decode(c.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'),
12, 'DATE',
23, 'RAW', 24, 'LONG RAW',
58, nvl2(ac.synobj#, (select o.name from obj$ o
where o.obj#=ac.synobj#), ot.name),
69, 'ROWID',
96, decode(c.charsetform, 2, 'NCHAR', 'CHAR'),
100, 'BINARY_FLOAT',
101, 'BINARY_DOUBLE',
105, 'MLSLABEL',
106, 'MLSLABEL',
111, nvl2(ac.synobj#, (select o.name from obj$ o
where o.obj#=ac.synobj#), ot.name),
112, decode(c.charsetform, 2, 'NCLOB', 'CLOB'),
113, 'BLOB', 114, 'BFILE', 115, 'CFILE',
121, nvl2(ac.synobj#, (select o.name from obj$ o
where o.obj#=ac.synobj#), ot.name),
122, nvl2(ac.synobj#, (select o.name from obj$ o
where o.obj#=ac.synobj#), ot.name),
123, nvl2(ac.synobj#, (select o.name from obj$ o
where o.obj#=ac.synobj#), ot.name),
178, 'TIME(' ||c.scale|| ')',
179, 'TIME(' ||c.scale|| ')' || ' WITH TIME ZONE',
180, 'TIMESTAMP(' ||c.scale|| ')',
181, 'TIMESTAMP(' ||c.scale|| ')' || ' WITH TIME ZONE',
231, 'TIMESTAMP(' ||c.scale|| ')' || ' WITH LOCAL TIME ZONE',
182, 'INTERVAL YEAR(' ||c.precision#||') TO MONTH',
183, 'INTERVAL DAY(' ||c.precision#||') TO SECOND(' ||
c.scale || ')',
208, 'UROWID',
'UNDEFINED'),
decode(c.type#, 111, 'REF'),
nvl2(ac.synobj#, (select u.name from "_BASE_USER" u, obj$ o
where o.owner#=u.user# and o.obj#=ac.synobj#), ut.name),
c.length, c.precision#, c.scale,
decode(sign(c.null$),-1,'D', 0, 'Y', 'N'),
decode(c.col#, 0, to_number(null), c.col#), c.deflength,
c.default$, h.distcnt, h.lowval, h.hival, h.density, h.null_cnt,
case when nvl(h.distcnt,0) = 0 then h.distcnt
when h.row_cnt = 0 then 1
when (h.bucket_cnt > 255
or
(h.bucket_cnt > h.distcnt
and h.row_cnt = h.distcnt
and h.density*h.bucket_cnt < 1))
then h.row_cnt
else h.bucket_cnt
end,
h.timestamp#, h.sample_size,
decode(c.charsetform, 1, 'CHAR_CS',
2, 'NCHAR_CS',
3, NLS_CHARSET_NAME(c.charsetid),
4, 'ARG:'||c.charsetid),
decode(c.charsetid, 0, to_number(NULL),
nls_charset_decl_len(c.length, c.charsetid)),
decode(bitand(h.spare2, 2), 2, 'YES', 'NO'),
decode(bitand(h.spare2, 1), 1, 'YES', 'NO'),
h.avgcln,
c.spare3,
decode(c.type#, 1, decode(bitand(c.property, 8388608), 0, 'B', 'C'),
96, decode(bitand(c.property, 8388608), 0, 'B', 'C'),
null),
decode(bitand(ac.flags, 128), 128, 'YES', 'NO'),
decode(o.status, 1, decode(bitand(ac.flags, 256), 256, 'NO', 'YES'),
decode(bitand(ac.flags, 2), 2, 'NO',
decode(bitand(ac.flags, 4), 4, 'NO',
decode(bitand(ac.flags, 8), 8, 'NO',
'N/A')))),
decode(c.property, 0, 'NO', decode(bitand(c.property, 32), 32, 'YES',
'NO')),
decode(c.property, 0, 'NO', decode(bitand(c.property, 8), 8, 'YES',
'NO')),
decode(c.segcol#, 0, to_number(null), c.segcol#), c.intcol#,
case when nvl(h.row_cnt,0) = 0 then 'NONE'
when (h.bucket_cnt > 255
or
(h.bucket_cnt > h.distcnt and h.row_cnt = h.distcnt
and h.density*h.bucket_cnt < 1))
then 'FREQUENCY'
else 'HEIGHT BALANCED'
end,
decode(bitand(c.property, 1024), 1024,
(select decode(bitand(cl.property, 1), 1, rc.name, cl.name)
from sys.col$ cl, attrcol$ rc where cl.intcol# = c.intcol#-1
and cl.obj# = c.obj# and c.obj# = rc.obj#(+) and
cl.intcol# = rc.intcol#(+)),
decode(bitand(c.property, 1), 0, c.name,
(select tc.name from sys.attrcol$ tc
where c.obj# = tc.obj# and c.intcol# = tc.intcol#)))
from sys.col$ c, sys.obj$ o, sys.hist_head$ h, sys.user$ u,
sys.coltype$ ac, sys.obj$ ot, sys."_BASE_USER" ut, sys.tab$ t
where o.obj# = c.obj#
and o.owner# = u.user#
and c.obj# = h.obj#(+) and c.intcol# = h.intcol#(+)
and c.obj# = ac.obj#(+) and c.intcol# = ac.intcol#(+)
and ac.toid = ot.oid$(+)
and ot.type#(+) = 13
and ot.owner# = ut.user#(+)
and o.obj# = t.obj#
and bitand(t.property, 8192) = 8192 /* nested tables */
 |
If you like Oracle tuning, see the book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
 |
 |
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
