Re: user_part_tables

[oracle@TzDbDataanal admin]$ grep -i 'user_part_tables' *.sql
catnoprt.sql:drop view USER_PART_TABLES
catpart.sql:create or replace view USER_PART_TABLES
catpart.sql:create or replace public synonym USER_PART_TABLES for
USER_PART_TABLES
catpart.sql:grant select on USER_PART_TABLES to PUBLIC with grant option

From catpart.sql
remark
remark  FAMILY "PART_TABLES"
remark   This family of views will describe the object level partitioning
remark   information for partitioned tables.
remark   pctused, freelists, freelist groups are null for bitmap segments
remark
create or replace view USER_PART_TABLES
  (TABLE_NAME, PARTITIONING_TYPE, SUBPARTITIONING_TYPE,
   PARTITION_COUNT, DEF_SUBPARTITION_COUNT, PARTITIONING_KEY_COUNT,
   SUBPARTITIONING_KEY_COUNT,
   DEF_TABLESPACE_NAME, DEF_PCT_FREE, DEF_PCT_USED, DEF_INI_TRANS,
   DEF_MAX_TRANS, DEF_INITIAL_EXTENT, DEF_NEXT_EXTENT, DEF_MIN_EXTENTS,
   DEF_MAX_EXTENTS, DEF_PCT_INCREASE, DEF_FREELISTS, DEF_FREELIST_GROUPS,
   DEF_LOGGING, DEF_COMPRESSION, DEF_BUFFER_POOL)
as
select o.name,
       decode(po.parttype, 1, 'RANGE', 2, 'HASH', 3, 'SYSTEM', 4, 'LIST',
                                                        '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,
       mod(trunc(po.spare2/256), 256),
       ts.name, po.defpctfree,
       decode(bitand(ts.flags, 32), 32, to_number(NULL), po.defpctused),
       po.definitrans,
       po.defmaxtrans,
       decode(po.deftiniexts, NULL, 'DEFAULT', po.deftiniexts),
       decode(po.defextsize, NULL, 'DEFAULT', po.defextsize),
       decode(po.defminexts, NULL, 'DEFAULT', po.defminexts),
       decode(po.defmaxexts, NULL, 'DEFAULT', po.defmaxexts),
       decode(po.defextpct, NULL, 'DEFAULT', po.defextpct),
       po.deflists, po.defgroups,
       decode(po.deflogging, 0, 'NONE', 1, 'YES', 2, 'NO', 'UNKNOWN'),
       decode(mod(trunc(po.spare2/4294967296),256), 0, 'NONE', 1, 'ENABLED'=
,
                     2, 'DISABLED', 'UNKNOWN'),
       decode(po.spare1, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL)
from   sys.obj$ o, sys.partobj$ po, sys.ts$ ts, sys.tab$ t
where  o.obj# =3D po.obj# and po.defts# =3D ts.ts# and t.obj# =3D o.obj# an=
d
       o.owner# =3D userenv('SCHEMAID') and
       bitand(t.property, 64 + 128) =3D 0
union all -- NON-IOT and IOT
select o.name,
       decode(po.parttype, 1, 'RANGE', 2, 'HASH', 3, 'SYSTEM', 4, 'LIST',
                                                        'UNKNOWN'),
       decode(mod(po.spare2, 256), 0, 'NONE', 2, 'HASH', 3, 'SYSTEM',
                                      'UNKNOWN'),
       po.partcnt, mod(trunc(po.spare2/65536), 65536), po.partkeycols,
       mod(trunc(po.spare2/256), 256),
       NULL, TO_NUMBER(NULL),TO_NUMBER(NULL),TO_NUMBER(NULL),TO_NUMBER(NULL=
),
       NULL,--decode(po.deftiniexts, NULL, 'DEFAULT', po.deftiniexts),
       NULL,--decode(po.defextsize, NULL, 'DEFAULT', po.defextsize),
       NULL,--decode(po.defminexts, NULL, 'DEFAULT', po.defminexts),
       NULL,--decode(po.defmaxexts, NULL, 'DEFAULT', po.defmaxexts),
       NULL,--decode(po.defextpct, NULL, 'DEFAULT', po.defextpct),
       TO_NUMBER(NULL),TO_NUMBER(NULL),--po.deflists, po.defgroups,
       decode(po.deflogging, 0, 'NONE', 1, 'YES', 2, 'NO', 'UNKNOWN'),
       'N/A',
       decode(po.spare1, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL)
from   sys.obj$ o, sys.partobj$ po, sys.tab$ t
where  o.obj# =3D po.obj# and t.obj# =3D o.obj# and
       o.owner# =3D userenv('SCHEMAID') and
       bitand(t.property, 64 + 128) !=3D 0
/
create or replace public synonym USER_PART_TABLES for USER_PART_TABLES
/
grant select on USER_PART_TABLES to PUBLIC with grant option
/

On 4/28/05, Paul Baumgartel <paul.baumgartel@xxxxxxxxx> wrote:
> Where are you getting the view source?
>  (Repost to evade the over-quoting snare).
>=20
> On 4/27/05, Bill Coulam <bcoulam@xxxxxxxxx> wrote:
> > This question probably applies to other data dictionary views as well.
> >=3D20
>=20
> > 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.
> >=3D20
> > Which brings me to my question:  What makes the magical column name
> > transformation happen? What piece of this puzzle am I missing?
> >=3D20
>=20
> --=3D20
> Paul Baumgartel
> paul.baumgartel@xxxxxxxxx
> --
> http://www.freelists.org/webpage/oracle-l
>
--
http://www.freelists.org/webpage/oracle-l

Other related posts: