RE: Data Dictionary Wierdness

Or just a lack of supervision/QA.

Scott Shafer
San Antonio, TX
210-581-6217

"Common sense will not accomplish great things. Simply become insane and desperate."

> -----Original Message-----
> From: Johnston, Tim [SMTP:TJohnston_at_quallaby.com]
> Sent: Monday, January 07, 2002 6:16 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Data Dictionary Wierdness
>
> 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: sorr_at_rightnow.com
>
> 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: ListGuru_at_fatcity.com (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: TJohnston_at_quallaby.com
>
> 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: ListGuru_at_fatcity.com (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: sorr_at_rightnow.com
>
> 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: ListGuru_at_fatcity.com (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: TJohnston_at_quallaby.com
>
> 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: ListGuru_at_fatcity.com (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: 
  INET: Scott.Shafer_at_dcpds.cpms.osd.mil

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: ListGuru_at_fatcity.com (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).