It was probably to fix a bug that he/she introduced on 10/03...
:-)
Rem achaudhr 10/25/95 - PTI: Add lpads around degree, instances,
cache
Rem achaudhr 10/03/95 - PTI: change degree, instances, cache
-----Original Message-----
Sent: Monday, January 07, 2002 6:55 PM
To: Multiple recipients of list ORACLE-L
Yeah I saw that after I sent the email but it's still dumb IMHO. We can
either blame achaudhr or his/her boss. :-)
More text from catalog.sql:
Rem achaudhr 10/25/95 - PTI: Add lpads around degree, instances, cache
-----Original Message-----
Sent: Monday, January 07, 2002 4:30 PM
To: Multiple recipients of list ORACLE-L
It's because the definition of the column in the dba_tables view does an
lpad on the column to 5 characters... Look for ******* in the definition
below...
create or replace view DBA_TABLES
(OWNER, TABLE_NAME, TABLESPACE_NAME, CLUSTER_NAME, IOT_NAME,
PCT_FREE, PCT_USED,
INI_TRANS, MAX_TRANS,
INITIAL_EXTENT, NEXT_EXTENT,
MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE,
FREELISTS, FREELIST_GROUPS, LOGGING,
BACKED_UP, NUM_ROWS, BLOCKS, EMPTY_BLOCKS,
AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN,
AVG_SPACE_FREELIST_BLOCKS, NUM_FREELIST_BLOCKS,
DEGREE, INSTANCES, CACHE, TABLE_LOCK,
SAMPLE_SIZE, LAST_ANALYZED, PARTITIONED,
IOT_TYPE, TEMPORARY, SECONDARY, NESTED,
BUFFER_POOL, ROW_MOVEMENT,
GLOBAL_STATS, USER_STATS, DURATION, SKIP_CORRUPT, MONITORING,
CLUSTER_OWNER)
as
select u.name, o.name, decode(bitand(t.property, 4194400), 0, ts.name,
null),
decode(bitand(t.property, 1024), 0, null, co.name),
decode(bitand(t.property, 512), 0, null, co.name),
decode(bitand(t.property, 32), 0, mod(t.pctfree$, 100), null),
decode(bitand(t.property, 32+64), 0, t.pctused$, 64, 0, null),
decode(bitand(t.property, 32), 0, t.initrans, null),
decode(bitand(t.property, 32), 0, t.maxtrans, null),
s.iniexts * ts.blocksize,
decode(bitand(ts.flags, 3), 1, to_number(NULL),
s.extsize * ts.blocksize),
s.minexts, s.maxexts,
decode(bitand(ts.flags, 3), 1, to_number(NULL),
s.extpct),
decode(bitand(o.flags, 2), 2, 1, decode(s.lists, 0, 1, s.lists)),
decode(bitand(o.flags, 2), 2, 1, decode(s.groups, 0, 1, s.groups)),
decode(bitand(t.property, 32), 32, null,
decode(bitand(t.flags, 32), 0, 'YES', 'NO')),
decode(bitand(t.flags,1), 0, 'Y', 1, 'N', '?'),
t.rowcnt,
decode(bitand(t.property, 64), 0, t.blkcnt, null),
decode(bitand(t.property, 64), 0, t.empcnt, null),
t.avgspc, t.chncnt, t.avgrln, t.avgspc_flb,
decode(bitand(t.property, 64), 0, t.flbcnt, null),
lpad(decode(t.degree, 32767, 'DEFAULT', nvl(t.degree,1)),10),
lpad(decode(t.instances, 32767, 'DEFAULT', nvl(t.instances,1)),10),
lpad(decode(bitand(t.flags, 8), 8, 'Y', 'N'),5), <------------
*******
decode(bitand(t.flags, 6), 0, 'ENABLED', 'DISABLED'),
t.samplesize, t.analyzetime,
decode(bitand(t.property, 32), 32, 'YES', 'NO'),
decode(bitand(t.property, 64), 64, 'IOT',
decode(bitand(t.property, 512), 512, 'IOT_OVERFLOW', null)),
decode(bitand(o.flags, 2), 0, 'N', 2, 'Y', 'N'),
decode(bitand(o.flags, 16), 0, 'N', 16, 'Y', 'N'),
decode(bitand(t.property, 8192), 8192, 'YES',
decode(bitand(t.property, 1), 0, 'NO', 'YES')),
decode(bitand(o.flags, 2), 2, 'DEFAULT',
decode(s.cachehint, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE',
NULL)),
decode(bitand(t.flags, 131072), 131072, 'ENABLED', 'DISABLED'),
decode(bitand(t.flags, 512), 0, 'NO', 'YES'),
decode(bitand(t.flags, 256), 0, 'NO', 'YES'),
decode(bitand(o.flags, 2), 0, NULL,
decode(bitand(t.property, 8388608), 8388608,
'SYS$SESSION', 'SYS$TRANSACTION')),
decode(bitand(t.flags, 1024), 1024, 'ENABLED', 'DISABLED'),
decode(bitand(t.flags, 2097152), 2097152, 'YES', 'NO'),
decode(bitand(t.property, 1024), 0, null, cu.name)
from sys.user$ u, sys.ts$ ts, sys.seg$ s, sys.obj$ co, sys.tab$ t, sys.obj$
o,
sys.obj$ cx, sys.user$ cu
where o.owner# = u.user#
and o.obj# = t.obj#
and bitand(t.property, 1) = 0
and t.bobj# = co.obj# (+)
and t.ts# = ts.ts#
and t.file# = s.file# (+)
and t.block# = s.block# (+)
and t.ts# = s.ts# (+)
and t.dataobj# = cx.obj# (+)
and cx.owner# = cu.user# (+)
/
-----Original Message-----
Sent: Monday, January 07, 2002 5:25 PM
To: Multiple recipients of list ORACLE-L
This does not work:
select owner,table_name,cache from dba_tables
where cache='Y';
This does:
select owner,table_name,cache from dba_tables
where cache=' Y';
The datatype for the column from desc dba_tables is VARCHAR2(5).
So why would Oracle have leading spaces stored in a VARCHAR2 column?
Whining in Bozeman, MT
Steve Orr
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Orr, Steve
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Johnston, Tim
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Orr, Steve
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Johnston, Tim
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
