Re: user_part_tables

From: jame tong <jametong_at_gmail.com>
Date: Thu, 28 Apr 2005 12:59:12 +0800
Message-ID: <264ff93405042721592e1b4aa4@mail.gmail.com>

[oracle_at_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_at_gmail.com> wrote:
> Where are you getting the view source?
> (Repost to evade the over-quoting snare).
>=20
> On 4/27/05, Bill Coulam <bcoulam_at_gmail.com> 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_at_gmail.com
> --
> http://www.freelists.org/webpage/oracle-l
>

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

Received on Thu Apr 28 2005 - 01:03:46 CDT