RE: Data Dictionary Wierdness

From: Orr, Steve <sorr_at_rightnow.com>
Date: Mon, 07 Jan 2002 16:13:15 -0800
Message-ID: <F001.003E90D1.20020107155522@fatcity.com>

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
--------------------------------------------------------------------

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------