user_part_tables

From: Bill Coulam <bcoulam_at_gmail.com>
Date: Wed, 27 Apr 2005 13:53:01 -0500
Message-ID: <f51d330205042711532c727f56@mail.gmail.com>

This question probably applies to other data dictionary views as well.

If one describes user_part_tables, you get:

Name                      Type        =20
------------------------- ------------=20
TABLE_NAME                VARCHAR2(30)=20
PARTITIONING_TYPE         VARCHAR2(7) =20
SUBPARTITIONING_TYPE      VARCHAR2(7) =20
PARTITION_COUNT           NUMBER      =20
DEF_SUBPARTITION_COUNT    NUMBER      =20
PARTITIONING_KEY_COUNT    NUMBER      =20
SUBPARTITIONING_KEY_COUNT NUMBER      =20

...

I was interested in determining where the "DEF_SUBPARTITION_COUNT" was stored in the underlying system tables (partobj$ I believe). When I opened the source for view USER_PART_TABLES, I got this:

CREATE OR REPLACE VIEW sys.user_part_tables AS SELECT o.NAME,

       DECODE(po.parttype, 1, 'RANGE', 2, 'HASH', 3, 'SYSTEM', 4, 'LIST', =09=09=09=09=09=09=09'UNKNOWN'),

       DECODE(MOD(po.spare2, 256), 0, 'NONE', 2, 'HASH', 3, 'SYSTEM',
                                      4, 'LIST', 'UNKNOWN'),
       po.partcnt, MOD(TRUNC(po.spare2/65536), 65536), po.partkeycols,

...

FROM sys.obj$ o, sys.partobj$ po, sys.ts$ ts, sys.TAB$ t
...

Ordinarily, if a view has a column named "PARTITIONING_TYPE", I expect to find, in the view's source, a column or alias that reads "partitioning_type". Instead, I see unnamed derived columns, and named columns, that seem to be magically transformed to different names that show up in user_part_tables.

Which brings me to my question: What makes the magical column name transformation happen? What piece of this puzzle am I missing?

--=20
bill coulam
bcoulam_at_gmail.com

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Apr 27 2005 - 14:57:20 CDT