rem 
rem $Header:
rem 
Rem Copyright (c) 1988, 1995, 1996, 1997, 1998, 1999 by Oracle Corporation
Rem
Rem NAME
Rem   CATPART.SQL
Rem FUNCTION
Rem   Creates data dictionary views for the partitioning table.
Rem NOTES
Rem   1. Must be run when connected to SYS or INTERNAL.
Rem   2. This script is standalone, but it is also run from catalog.sql.
Rem   3. To drop these views run CATNOPRT.SQL.
Rem MODIFIED
Rem    qyu        07/27/99 - keep YES/NO value in cache columns for lob
Rem    tlee       06/28/99 - cache reads lob mode logging fix
Rem    attran     03/31/99 - PIOT: definitions of *_PART_TABLES
Rem    qyu        03/04/99 - add CACHE READS lob mode
Rem    thoang     11/13/98 - Mod views to return attribute name for ADT col    
Rem    mjaganna   11/12/98 - Fix PIOT view related bugs (750662)
Rem    ielayyan   11/02/98 - 748786, make ALL_ views visible to grantee
Rem    sbasu      09/08/98 - replace 2147483647 with NULL in decode expressions 
Rem                          for deftiniexts, defextsize, defminexts, defmaxexts,
Rem                          defextpct in *_PART_TABLE and *_PART_INDEXES views 
Rem    tlee       08/19/98 - remove to_char of extsize in lob_subpartitions
Rem    amozes     07/24/98 - global index stats                                
Rem    smuthuli   07/30/98 - Bug 696705
Rem    amozes     08/24/98 - expose endpoint actual value                      
Rem    ayalaman   07/21/98 - change tab_partitions views to show iot tabpart
Rem    akruglik   07/22/98 - in *_PART_LOBS and *_LOB_PARTITIONS (describing 
Rem                          Composite partitions) DEFAULT BUFFER_POOL will be
Rem                          represented by 0, not NULL
Rem    ayalaman   06/12/98 - add guess stats to user_ind_partitions
Rem    akruglik   05/29/98 - correct predicate in _PART_LOBS views             
Rem    amozes     06/01/98 - analyze composite statistics                      
Rem    ayalaman   06/05/98 - add COMPRESSION to *_ind_part views
Rem    syeung     05/08/98 - store unspecified dflt logging attr. for Composite
Rem                          partition in _TAB_PARTITIONS and _IND_PARTITIONS 
Rem                          views
Rem    akruglik   05/14/98 - modify definitions of *_LOB_[SUB]PARTITIONS to 
Rem                          display CHUNK size of physical fragments in bytes 
Rem                          rahter than blocks
Rem    akruglik   05/01/98 - define {USER|ALL|DBA}_PART_LOBS, 
Rem                          {USER|ALL|DBA}_LOB_PARTITIONS, and 
Rem                          {USER|ALL|DBA}_LOB_SUBPARTITIONS
Rem    syeung     02/24/98 - default tablespace for Composite partition can no
Rem                          longer be KTSNINV (i.e. remove outer join in 
Rem                          _TAB_PARTITIONS views for Composite table)
Rem    amozes     03/27/98 - add new stats information                         
Rem    sbasu      02/25/98 - support for range-composite part. local indexes
Rem			     change ref. comppart$->tabcompart$
Rem    thoang     12/15/97 - Modified views to exclude unused columns.
Rem    syeung     12/31/97 - code cleanups for partitioning project:
Rem                          - rename EXTHASH->HASH & remove HYBRID from 
Rem                            partitioning_type of {USER|ALL|DBA}_PART_TABLES
Rem                             and {USER|ALL|DBA}_PART_INDEXES views 
Rem                          - remove TABLESPACE_COUNT from _PART_TABLES and
Rem                            {USER|ALL|DBA}_TAB_PARTITIONS views
Rem                          - remove {USER|ALL|DBA}_TABLESPACE_LISTS views
Rem                          - change ref. to hybpart$->comppart$
Rem    syeung     12/16/97 - boolean column HYBRID in ALL_TAB_PARTITIONS & DBA_
Rem    syeung     12/09/97 - decode deflists and defgroups in USER_TAB_PARTITIO
Rem    syeung     11/17/97 - add object_type for _TABLESPACE_LISTS views
Rem    sbasu	  11/10/97 - alter definition of alignment field in PART_INDEXES
Rem			     family of views to correctly display local 
Rem		             SYSTEM partitioned indexes
Rem    akruglik   11/03/97 - default number of subpartitions may ne upto 64K, 
Rem    syeung     11/01/97 - refer to partobj$.spare2 for tscnt
Rem    syeung     10/07/97 - use hybpart$ for Hybrid partition information
Rem			     so the expression in *_PART_TABLES should use 
Rem			     mod 65536
Rem    nireland   09/19/97 - Remove magic numbers. #502160
Rem    akruglik   09/11/97 - add OBJECT_TYPE column to *_PART_KEY_COLUMNS views
Rem    sbasu      07/30/97 - #504196: Add freelist_groups to *_IND_PARTITIONS
Rem    syeung     07/03/97 - add SUBPART_KEY_COLUMNS family
Rem    akruglik   06/09/97 - Hybrid partitioning will no longer be
Rem                          determinable by checking patytype - subparttype
Rem                          should be checked instead
Rem    akruglik   05/21/97 - alter WHERE-clause of {USER|ALL|DBA}_PART_INDEXES
Rem			     so it does not rely on system-specific value of 
Rem                          KTSNINV
Rem    aho        04/18/97 - partitioned cache
Rem    syeung     05/07/97 - pti 8.1 project
Rem    akruglik   05/02/97 - 8.1 Partitioning Project: 
Rem			       alter definitions of view in PART_TABLES and 
Rem			       PART_INDEXES families to correctly display 
Rem			       PARTITIONING_TYPE of objects partitioned by 
Rem			       System, Extensible Hash and Hybrid
Rem    achaudhr   03/10/97 - #(454169): catnopart.sql -> catnoprt.sql
Rem    achaudhr   11/22/96 - PART_COL_STATISTICS: reorder columns
Rem    ssamu      08/13/96 - fix view xxx_part_key_columns to show index partit
Rem    mmonajje   05/20/96 - Replace timestamp col name with timestamp#
Rem    atsukerm   05/13/96 - tablespace-relative DBAs - fix TAB_PARTITIONS and
Rem                          IND_PARTITIONS
Rem    asurpur    04/08/96 - Dictionary Protection Implementation
Rem    jwijaya    03/29/96 - test for hidden columns
Rem    achaudhr   03/21/96 - fix PART_HISTOGRAMS
Rem    akruglik   03/21/96 - another change related to default tablespace of
Rem                          LOCAL indexes - since we are using an outer join
Rem                          in the definition of user|all|dba_part_indexes, 
Rem                          there is no reason to decode po.defts#
Rem    akruglik   03/20/96 - definitions of {user|dba|all}_part_indexes need to
Rem    akruglik   03/12/96 - change qualification of 
Rem                          {user|dba|all}_part_indexes to account for the 
Rem                          fact that LOCAL indexes may have no default 
Rem                          TABLESPACE associated with them
Rem    akruglik   02/28/96 - add def_logging and logging attributes to
Rem                          {USER | ALL | DBA}_PART_{INDEXES | TABLES} and 
Rem                          {USER | ALL | DBA}_{IND | TAB}_PARTITIONS
Rem    sbasu      11/21/95 - Add STATUS field to {USER|ALL_DBA}_IND_PARTITIONS and modify names 
Rem    achaudhr   10/30/95 - Create the views
Rem

remark
remark  FAMILY "PART_TABLES"
remark   This family of views will describe the object level partitioning 
remark   information for partitioned tables.
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_BUFFER_POOL)
as 
select o.name,
       decode(po.parttype, 1, 'RANGE', 2, 'HASH', 3, 'SYSTEM', '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),
       ts.name, po.defpctfree, 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(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# = po.obj# and po.defts# = ts.ts# and t.obj# = o.obj# and
       o.owner# = userenv('SCHEMAID') and 
       bitand(t.property, 64 + 128) = 0
union all -- NON-IOT and IOT
select o.name,
       decode(po.parttype, 1, 'RANGE', 2, 'HASH', 3, 'SYSTEM', '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'),
       decode(po.spare1, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL)
from   sys.obj$ o, sys.partobj$ po, sys.tab$ t
where  o.obj# = po.obj# and t.obj# = o.obj# and
       o.owner# = userenv('SCHEMAID') and
       bitand(t.property, 64 + 128) != 0
/ 
drop public synonym USER_PART_TABLES 
/
create public synonym USER_PART_TABLES for USER_PART_TABLES 
/
grant select on USER_PART_TABLES to PUBLIC with grant option
/
create or replace view ALL_PART_TABLES 
  (OWNER, 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_BUFFER_POOL)
as 
select u.name, o.name, 
       decode(po.parttype, 1, 'RANGE', 2, 'HASH', 3, 'SYSTEM', '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),
       ts.name, po.defpctfree, 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(po.spare1, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL)
from   sys.obj$ o, sys.partobj$ po, sys.ts$ ts, sys.tab$ t, sys.user$ u
where  o.obj# = po.obj# and po.defts# = ts.ts# and t.obj# = o.obj# and
       o.owner# = u.user# and 
       bitand(t.property, 64 + 128) = 0 and
       (o.owner# = userenv('SCHEMAID')
        or o.obj# in
            (select oa.obj#
             from sys.objauth$ oa
             where grantee# in ( select kzsrorol
                                 from x$kzsro
                               ) 
            )
        or /* user has system privileges */
         exists (select null from v$enabledprivs
                 where priv_number in (-45 /* LOCK ANY TABLE */,
                                       -47 /* SELECT ANY TABLE */,
                                       -48 /* INSERT ANY TABLE */,
                                       -49 /* UPDATE ANY TABLE */,
                                       -50 /* DELETE ANY TABLE */)
                 )
      )
union all -- NON-IOT and IOT
select u.name, o.name, 
       decode(po.parttype, 1, 'RANGE', 2, 'HASH', 3, 'SYSTEM', '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'),
       decode(po.spare1, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL)
from   sys.obj$ o, sys.partobj$ po, sys.tab$ t, sys.user$ u
where  o.obj# = po.obj# and t.obj# = o.obj# and
       o.owner# = u.user# and 
       bitand(t.property, 64 + 128) != 0 and
       (o.owner# = userenv('SCHEMAID')
        or o.obj# in
            (select oa.obj#
             from sys.objauth$ oa
             where grantee# in ( select kzsrorol
                                 from x$kzsro
                               ) 
            )
        or /* user has system privileges */
         exists (select null from v$enabledprivs
                 where priv_number in (-45 /* LOCK ANY TABLE */,
                                       -47 /* SELECT ANY TABLE */,
                                       -48 /* INSERT ANY TABLE */,
                                       -49 /* UPDATE ANY TABLE */,
                                       -50 /* DELETE ANY TABLE */)
                 )
      )
/ 
drop public synonym ALL_PART_TABLES 
/
create public synonym ALL_PART_TABLES for ALL_PART_TABLES 
/
grant select on ALL_PART_TABLES to PUBLIC with grant option
/
create or replace view DBA_PART_TABLES 
  (OWNER, 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_BUFFER_POOL)
as 
select u.name, o.name, 
       decode(po.parttype, 1, 'RANGE', 2, 'HASH', 3, 'SYSTEM', '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),
       ts.name, po.defpctfree, 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(po.spare1, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL)
from   sys.obj$ o, sys.partobj$ po, sys.ts$ ts, sys.tab$ t, sys.user$ u
where  o.obj# = po.obj# and po.defts# = ts.ts# and t.obj# = o.obj# and
       o.owner# = u.user# and
       bitand(t.property, 64 + 128) = 0
union all -- NON-IOT and IOT
select u.name, o.name, 
       decode(po.parttype, 1, 'RANGE', 2, 'HASH', 3, 'SYSTEM', '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'),
       decode(po.spare1, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL)
from   sys.obj$ o, sys.partobj$ po, sys.tab$ t, sys.user$ u
where  o.obj# = po.obj# and t.obj# = o.obj# and
       o.owner# = u.user# and
       bitand(t.property, 64 + 128) != 0
/ 
drop public synonym DBA_PART_TABLES
/
create public synonym DBA_PART_TABLES for DBA_PART_TABLES
/
grant select on DBA_PART_TABLES to select_catalog_role
/

remark
remark  FAMILY "PART_INDEXES"
remark   This family of views will describe the object level partitioning
remark   information for partitioned indexes.
remark
create or replace view USER_PART_INDEXES
  (INDEX_NAME, TABLE_NAME, PARTITIONING_TYPE, SUBPARTITIONING_TYPE, 
   PARTITION_COUNT, DEF_SUBPARTITION_COUNT, 
   PARTITIONING_KEY_COUNT, SUBPARTITIONING_KEY_COUNT, 
   LOCALITY, ALIGNMENT, DEF_TABLESPACE_NAME, 
   DEF_PCT_FREE, 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_BUFFER_POOL)
as 
select io.name, o.name,
        decode(po.parttype, 1, 'RANGE', 2, 'HASH', 3, 'SYSTEM', '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),
       decode(bitand(po.flags, 1), 1, 'LOCAL',    'GLOBAL'),
       decode(po.partkeycols, 0, 'NONE', decode(bitand(po.flags,2), 2, 'PREFIXED', 'NON_PREFIXED')),
       ts.name, po.defpctfree, 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(po.spare1, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL)
from   sys.obj$ io, sys.obj$ o, sys.partobj$ po, sys.ts$ ts, sys.ind$ i
where  io.obj# = po.obj# and po.defts# = ts.ts# (+) and i.obj# = io.obj#
       and o.obj# = i.bo# and io.owner# = userenv('SCHEMAID') 
/ 
drop public synonym USER_PART_INDEXES
/
create public synonym USER_PART_INDEXES for USER_PART_INDEXES
/
grant select on USER_PART_INDEXES to PUBLIC with grant option
/
create or replace view ALL_PART_INDEXES
  (OWNER, INDEX_NAME, TABLE_NAME, PARTITIONING_TYPE, SUBPARTITIONING_TYPE, 
   PARTITION_COUNT, DEF_SUBPARTITION_COUNT, PARTITIONING_KEY_COUNT,
   SUBPARTITIONING_KEY_COUNT, LOCALITY, ALIGNMENT, DEF_TABLESPACE_NAME,
   DEF_PCT_FREE, 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_BUFFER_POOL)
as 
select u.name, io.name, o.name,
       decode(po.parttype, 1, 'RANGE', 2, 'HASH', 3, 'SYSTEM', '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),
       decode(bitand(po.flags, 1), 1, 'LOCAL',    'GLOBAL'),
       decode(po.partkeycols, 0, 'NONE', decode(bitand(po.flags,2), 2, 'PREFIXED', 'NON_PREFIXED')),
       ts.name, po.defpctfree, 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(po.spare1, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL)
from   sys.obj$ io, sys.obj$ o, sys.partobj$ po, sys.ts$ ts, sys.ind$ i,
       sys.user$ u
where  io.obj# = po.obj# and po.defts# = ts.ts# (+) and
       i.obj# = io.obj# and o.obj# = i.bo# and u.user# = io.owner# and
       (io.owner# = userenv('SCHEMAID') 
        or
        i.bo# in ( select obj#
                    from objauth$
                    where grantee# in ( select kzsrorol
                                        from x$kzsro
                                      )
                   )
        or
         exists (select null from v$enabledprivs
                 where priv_number in (-45 /* LOCK ANY TABLE */,
                                       -47 /* SELECT ANY TABLE */,
                                       -48 /* INSERT ANY TABLE */,
                                       -49 /* UPDATE ANY TABLE */,
                                       -50 /* DELETE ANY TABLE */)
                 )
       )
/ 
drop public synonym ALL_PART_INDEXES
/
create public synonym ALL_PART_INDEXES for ALL_PART_INDEXES
/
grant select on ALL_PART_INDEXES to PUBLIC with grant option
/
create or replace view DBA_PART_INDEXES
  (OWNER, INDEX_NAME, TABLE_NAME, PARTITIONING_TYPE, SUBPARTITIONING_TYPE,
   PARTITION_COUNT, DEF_SUBPARTITION_COUNT, PARTITIONING_KEY_COUNT,
   SUBPARTITIONING_KEY_COUNT,
   LOCALITY, ALIGNMENT, DEF_TABLESPACE_NAME, DEF_PCT_FREE, 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_BUFFER_POOL)
as 
select u.name, io.name, o.name,
       decode(po.parttype, 1, 'RANGE', 2, 'HASH', 3, 'SYSTEM', '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), decode(bitand(po.flags, 1), 1, 'LOCAL',    'GLOBAL'),
       decode(po.partkeycols, 0, 'NONE', decode(bitand(po.flags,2), 2, 'PREFIXED', 'NON_PREFIXED')),       
       ts.name, po.defpctfree, 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(po.spare1, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL)
from   sys.obj$ io, sys.obj$ o, sys.partobj$ po, sys.ts$ ts, sys.ind$ i,
       sys.user$ u
where  io.obj# = po.obj# and po.defts# = ts.ts# (+) and
       i.obj# = io.obj# and o.obj# = i.bo# and u.user# = o.owner#
/ 
drop public synonym DBA_PART_INDEXES
/
create public synonym DBA_PART_INDEXES for DBA_PART_INDEXES
/
grant select on DBA_PART_INDEXES to select_catalog_role;
/

remark
remark  FAMILY "PART_KEY_COLUMNS"
remark   This family of views will describe the partitioning key columns for
remark   all partitioned objects.
remark
remark   using an UNION rather than an OR for speed.
create or replace view USER_PART_KEY_COLUMNS
  (NAME, OBJECT_TYPE, COLUMN_NAME, COLUMN_POSITION)
as
select o.name, RPAD('TABLE', 11), 
  decode(bitand(c.property, 1), 1, a.name, c.name), pc.pos#
from partcol$ pc, obj$ o, col$ c, attrcol$ a
where pc.obj# = o.obj# and pc.obj# = c.obj# and c.intcol# = pc.intcol# and
      o.owner# = userenv('SCHEMAID')
  and c.obj#    = a.obj#(+)
  and c.intcol# = a.intcol#(+)
union
select io.name, RPAD('INDEX', 11), 
  decode(bitand(c.property, 1), 1, a.name, c.name), pc.pos#
from partcol$ pc, obj$ io, col$ c, ind$ i, attrcol$ a
where pc.obj# = i.obj# and i.obj# = io.obj# and i.bo# = c.obj# and 
c.intcol# = pc.intcol# and io.owner# = userenv('SCHEMAID')
  and c.obj#    = a.obj#(+)
  and c.intcol# = a.intcol#(+)
/
drop public synonym USER_PART_KEY_COLUMNS
/
create public synonym USER_PART_KEY_COLUMNS for USER_PART_KEY_COLUMNS
/
grant select on USER_PART_KEY_COLUMNS to PUBLIC with grant option
/
create or replace view ALL_PART_KEY_COLUMNS
  (OWNER, NAME, OBJECT_TYPE, COLUMN_NAME, COLUMN_POSITION)
as
select u.name, o.name, RPAD('TABLE', 11), 
  decode(bitand(c.property, 1), 1, a.name, c.name), pc.pos#
from partcol$ pc, obj$ o, col$ c, user$ u, attrcol$ a
where pc.obj# = o.obj# and pc.obj# = c.obj# and c.intcol# = pc.intcol# and
      c.obj#    = a.obj#(+) and c.intcol# = a.intcol#(+) and
      u.user# = o.owner# and
      (o.owner# = userenv('SCHEMAID')
       or pc.obj# in
            (select oa.obj#
             from sys.objauth$ oa
             where grantee# in ( select kzsrorol
                                 from x$kzsro
                               ) 
            )
       or /* user has system privileges */
         exists (select null from v$enabledprivs
                 where priv_number in (-45 /* LOCK ANY TABLE */,
                                       -47 /* SELECT ANY TABLE */,
                                       -48 /* INSERT ANY TABLE */,
                                       -49 /* UPDATE ANY TABLE */,
                                       -50 /* DELETE ANY TABLE */)
                 )
      )
union
select u.name, io.name, RPAD('INDEX', 11), 
  decode(bitand(c.property, 1), 1, a.name, c.name), pc.pos#
from partcol$ pc, obj$ io, col$ c, user$ u, ind$ i, attrcol$ a
where pc.obj# = i.obj# and i.obj# = io.obj# and i.bo# = c.obj# and 
     c.intcol# = pc.intcol# and u.user# = io.owner# and 
     c.obj# = a.obj#(+) and c.intcol# = a.intcol#(+) and 
      (io.owner# = userenv('SCHEMAID')
       or i.bo# in
            (select oa.obj#
             from sys.objauth$ oa
             where grantee# in ( select kzsrorol
                                 from x$kzsro
                               ) 
            )
       or /* user has system privileges */
         exists (select null from v$enabledprivs
                 where priv_number in (-45 /* LOCK ANY TABLE */,
                                       -47 /* SELECT ANY TABLE */,
                                       -48 /* INSERT ANY TABLE */,
                                       -49 /* UPDATE ANY TABLE */,
                                       -50 /* DELETE ANY TABLE */)
                 )
      )
/
drop public synonym ALL_PART_KEY_COLUMNS
/
create public synonym ALL_PART_KEY_COLUMNS for ALL_PART_KEY_COLUMNS
/
grant select on ALL_PART_KEY_COLUMNS to PUBLIC with grant option
/
create or replace view DBA_PART_KEY_COLUMNS
  (OWNER, NAME, OBJECT_TYPE, COLUMN_NAME, COLUMN_POSITION)
as
select u.name, o.name, RPAD('TABLE', 11), 
  decode(bitand(c.property, 1), 1, a.name, c.name), pc.pos#
from partcol$ pc, obj$ o, col$ c, user$ u, attrcol$ a
where pc.obj# = o.obj# and pc.obj# = c.obj# and c.intcol# = pc.intcol# and
      u.user# = o.owner# and c.obj# = a.obj#(+) and c.intcol# = a.intcol#(+)
union 
select u.name, io.name, RPAD('INDEX', 11), 
  decode(bitand(c.property, 1), 1, a.name, c.name), pc.pos#
from partcol$ pc, obj$ io, col$ c, user$ u, ind$ i, attrcol$ a
where pc.obj# = i.obj# and i.obj# = io.obj# and i.bo# = c.obj# and 
        c.intcol# = pc.intcol# and u.user# = io.owner# 
        and c.obj# = a.obj#(+) and c.intcol# = a.intcol#(+)
/
drop public synonym DBA_PART_KEY_COLUMNS
/
create public synonym DBA_PART_KEY_COLUMNS for DBA_PART_KEY_COLUMNS
/
grant select on DBA_PART_KEY_COLUMNS to select_catalog_role
/

remark
remark  FAMILY "TAB_PARTITIONS"
remark   This family of views will describe, for each table partition, the
remark   partition level information, the storage parameters for the 
remark   partition, and various partition statistics determined by ANALYZE.
remark
create or replace view USER_TAB_PARTITIONS
  (TABLE_NAME, COMPOSITE, PARTITION_NAME, SUBPARTITION_COUNT, 
   HIGH_VALUE, HIGH_VALUE_LENGTH, 
   PARTITION_POSITION, TABLESPACE_NAME, PCT_FREE, PCT_USED, 
   INI_TRANS, MAX_TRANS, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENT, MAX_EXTENT,
   PCT_INCREASE, FREELISTS, FREELIST_GROUPS, LOGGING, NUM_ROWS, BLOCKS, 
   EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN, SAMPLE_SIZE, LAST_ANALYZED,
   BUFFER_POOL, GLOBAL_STATS, USER_STATS)
as 
select o.name, rpad('N0', 3), o.subname, 0,
       tp.hiboundval, tp.hiboundlen, tp.part#, ts.name, 
       tp.pctfree$, tp.pctused$, initrans, maxtrans, 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(s.lists, 0, 1, s.lists), decode(s.groups, 0, 1, s.groups),
       decode(mod(trunc(tp.flags / 4), 2), 0, 'YES', 'NO'),
       tp.rowcnt, tp.blkcnt, tp.empcnt, tp.avgspc, tp.chncnt, tp.avgrln,
       tp.samplesize, tp.analyzetime,
       decode(s.cachehint, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL),
       decode(bitand(tp.flags, 16), 0, 'NO', 'YES'),
       decode(bitand(tp.flags, 8), 0, 'NO', 'YES')
from   obj$ o, tabpart$ tp, ts$ ts, sys.seg$ s
where  o.obj# = tp.obj# and ts.ts# = tp.ts# and 
       tp.file#=s.file# and tp.block#=s.block# and tp.ts#=s.ts# and 
       o.owner# = userenv('SCHEMAID')
union all -- PIOT
select o.name, rpad('N0', 3), o.subname, 0,
       tp.hiboundval, tp.hiboundlen, tp.part#, NULL,
       TO_NUMBER(NULL),TO_NUMBER(NULL),TO_NUMBER(NULL),TO_NUMBER(NULL),
       TO_NUMBER(NULL),
       TO_NUMBER(NULL),TO_NUMBER(NULL),TO_NUMBER(NULL),TO_NUMBER(NULL),
       TO_NUMBER(NULL),TO_NUMBER(NULL),
       decode(mod(trunc(tp.flags / 4), 2), 0, 'YES', 'NO'), 
       tp.rowcnt, tp.blkcnt, tp.empcnt, tp.avgspc, tp.chncnt, tp.avgrln, 
       tp.samplesize, tp.analyzetime, NULL,
       decode(bitand(tp.flags, 16), 0, 'NO', 'YES'),
       decode(bitand(tp.flags, 8), 0, 'NO', 'YES')
from   obj$ o, tabpart$ tp
where  o.obj# = tp.obj# and tp.file#=0 and tp.block#=0 and 
       o.owner# = userenv('SCHEMAID')
union all -- subpart
select o.name, rpad('YES', 3), o.subname, tcp.subpartcnt,
       tcp.hiboundval, tcp.hiboundlen, tcp.part#, ts.name, 
       tcp.defpctfree, tcp.defpctused, tcp.definitrans, tcp.defmaxtrans,
       tcp.definiexts, tcp.defextsize, tcp.defminexts, tcp.defmaxexts, 
       tcp.defextpct, tcp.deflists, tcp.defgroups,
       decode(tcp.deflogging, 0, 'NONE', 1, 'YES', 2, 'NO', 'UNKNOWN'),
       tcp.rowcnt, tcp.blkcnt, tcp.empcnt, tcp.avgspc, tcp.chncnt, tcp.avgrln,
       tcp.samplesize, tcp.analyzetime, 
       decode(tcp.defbufpool, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL),
       decode(bitand(tcp.flags, 16), 0, 'NO', 'YES'),
       decode(bitand(tcp.flags, 8), 0, 'NO', 'YES')
from   obj$ o, tabcompart$ tcp, ts$ ts
where  o.obj# = tcp.obj# and tcp.defts# = ts.ts# and
       o.owner# = userenv('SCHEMAID') 
/
drop public synonym USER_TAB_PARTITIONS
/
create public synonym USER_TAB_PARTITIONS for USER_TAB_PARTITIONS
/
grant select on USER_TAB_PARTITIONS to PUBLIC with grant option
/
create or replace view ALL_TAB_PARTITIONS
  (TABLE_OWNER, TABLE_NAME, COMPOSITE, PARTITION_NAME, SUBPARTITION_COUNT, 
   HIGH_VALUE, HIGH_VALUE_LENGTH, 
   PARTITION_POSITION, TABLESPACE_NAME, PCT_FREE, PCT_USED, 
   INI_TRANS, MAX_TRANS, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENT, MAX_EXTENT,
   PCT_INCREASE, FREELISTS, FREELIST_GROUPS, LOGGING, NUM_ROWS, BLOCKS, 
   EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN, SAMPLE_SIZE, LAST_ANALYZED,
   BUFFER_POOL, GLOBAL_STATS, USER_STATS)
as 
select u.name, o.name, rpad('NO', 3), o.subname, 0,
       tp.hiboundval, tp.hiboundlen, tp.part#, ts.name, 
       tp.pctfree$, tp.pctused$, initrans, maxtrans, 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(s.lists, 0, 1, s.lists), decode(s.groups, 0, 1, s.groups),
       decode(mod(trunc(tp.flags / 4), 2), 0, 'YES', 'NO'),
       tp.rowcnt, tp.blkcnt, tp.empcnt, tp.avgspc, tp.chncnt, tp.avgrln,
       tp.samplesize, tp.analyzetime,
       decode(s.cachehint, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL),
       decode(bitand(tp.flags, 16), 0, 'NO', 'YES'),
       decode(bitand(tp.flags, 8), 0, 'NO', 'YES')
from   obj$ o, tabpart$ tp, ts$ ts, sys.seg$ s, user$ u
where  o.obj# = tp.obj# and ts.ts# = tp.ts# and u.user# = o.owner# and 
       tp.file#=s.file# and tp.block#=s.block# and tp.ts#=s.ts# and 
       (o.owner# = userenv('SCHEMAID') 
        or tp.bo# in
            (select oa.obj#
             from sys.objauth$ oa
             where grantee# in ( select kzsrorol
                                 from x$kzsro
                               ) 
            )
        or /* user has system privileges */
         exists (select null from v$enabledprivs
                 where priv_number in (-45 /* LOCK ANY TABLE */,
                                       -47 /* SELECT ANY TABLE */,
                                       -48 /* INSERT ANY TABLE */,
                                       -49 /* UPDATE ANY TABLE */,
                                       -50 /* DELETE ANY TABLE */)
                 )
      )
union all -- PIOT
select u.name, o.name, rpad('N0', 3), o.subname, 0,
       tp.hiboundval, tp.hiboundlen, tp.part#, NULL, 
       TO_NUMBER(NULL),TO_NUMBER(NULL),TO_NUMBER(NULL),TO_NUMBER(NULL),
       TO_NUMBER(NULL),
       TO_NUMBER(NULL),TO_NUMBER(NULL),TO_NUMBER(NULL),TO_NUMBER(NULL),
       TO_NUMBER(NULL),TO_NUMBER(NULL),
       decode(mod(trunc(tp.flags / 4), 2), 0, 'YES', 'NO'), 
       tp.rowcnt, tp.blkcnt, tp.empcnt, tp.avgspc, tp.chncnt, tp.avgrln, 
       tp.samplesize, tp.analyzetime, NULL,
       decode(bitand(tp.flags, 16), 0, 'NO', 'YES'),
       decode(bitand(tp.flags, 8), 0, 'NO', 'YES')
from   obj$ o, tabpart$ tp, user$ u
where  o.obj# = tp.obj# and o.owner# = u.user# and 
       tp.file#=0 and tp.block#=0 and
       (o.owner# = userenv('SCHEMAID')
        or tp.bo# in
            (select oa.obj#
             from sys.objauth$ oa
             where grantee# in ( select kzsrorol
                                 from x$kzsro
                               )
            )
        or /* user has system privileges */
         exists (select null from v$enabledprivs
                 where priv_number in (-45 /* LOCK ANY TABLE */,
                                       -47 /* SELECT ANY TABLE */,
                                       -48 /* INSERT ANY TABLE */,
                                       -49 /* UPDATE ANY TABLE */,
                                       -50 /* DELETE ANY TABLE */)
                 )
      )
union all -- Subpart
select u.name, o.name, rpad('YES', 3), o.subname, tcp.subpartcnt, 
       tcp.hiboundval, tcp.hiboundlen, tcp.part#, ts.name, 
       tcp.defpctfree, tcp.defpctused, tcp.definitrans, tcp.defmaxtrans, 
       tcp.definiexts, tcp.defextsize, tcp.defminexts, tcp.defmaxexts, 
       tcp.defextpct, tcp.deflists, tcp.defgroups,
       decode(tcp.deflogging, 0, 'NONE', 1, 'YES', 2, 'NO', 'UNKNOWN'),
       tcp.rowcnt, tcp.blkcnt, tcp.empcnt, tcp.avgspc, tcp.chncnt, tcp.avgrln,
       tcp.samplesize, tcp.analyzetime, 
       decode(tcp.defbufpool, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL),
       decode(bitand(tcp.flags, 16), 0, 'NO', 'YES'),
       decode(bitand(tcp.flags, 8), 0, 'NO', 'YES')
from   obj$ o, tabcompart$ tcp, ts$ ts, user$ u
where  o.obj# = tcp.obj# and tcp.defts# = ts.ts#  and u.user# = o.owner# and
       (o.owner# = userenv('SCHEMAID') 
        or tcp.bo# in
            (select oa.obj#
             from sys.objauth$ oa
             where grantee# in ( select kzsrorol
                                 from x$kzsro
                               ) 
            )
        or /* user has system privileges */
         exists (select null from v$enabledprivs
                 where priv_number in (-45 /* LOCK ANY TABLE */,
                                       -47 /* SELECT ANY TABLE */,
                                       -48 /* INSERT ANY TABLE */,
                                       -49 /* UPDATE ANY TABLE */,
                                       -50 /* DELETE ANY TABLE */)
                 )
      )
/
drop public synonym ALL_TAB_PARTITIONS
/
create public synonym ALL_TAB_PARTITIONS for ALL_TAB_PARTITIONS
/
grant select on ALL_TAB_PARTITIONS to PUBLIC with grant option
/
create or replace view DBA_TAB_PARTITIONS
  (TABLE_OWNER, TABLE_NAME, COMPOSITE, PARTITION_NAME, SUBPARTITION_COUNT, 
   HIGH_VALUE, HIGH_VALUE_LENGTH, 
   PARTITION_POSITION, TABLESPACE_NAME, PCT_FREE, PCT_USED, 
   INI_TRANS, MAX_TRANS, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENT, MAX_EXTENT,
   PCT_INCREASE, FREELISTS, FREELIST_GROUPS, LOGGING, NUM_ROWS, BLOCKS, 
   EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN, SAMPLE_SIZE, LAST_ANALYZED,
   BUFFER_POOL, GLOBAL_STATS, USER_STATS)
as 
select u.name, o.name, rpad('NO', 3), o.subname, 0,
       tp.hiboundval, tp.hiboundlen, tp.part#, ts.name, 
       tp.pctfree$, tp.pctused$, initrans, maxtrans, 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(s.lists, 0, 1, s.lists), decode(s.groups, 0, 1, s.groups),
       decode(mod(trunc(tp.flags / 4), 2), 0, 'YES', 'NO'),
       tp.rowcnt, tp.blkcnt, tp.empcnt, tp.avgspc, tp.chncnt, tp.avgrln,
       tp.samplesize, tp.analyzetime,
       decode(s.cachehint, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL),
       decode(bitand(tp.flags, 16), 0, 'NO', 'YES'),
       decode(bitand(tp.flags, 8), 0, 'NO', 'YES')
from   obj$ o, tabpart$ tp, ts$ ts, sys.seg$ s, user$ u
where  o.obj# = tp.obj# and ts.ts# = tp.ts# and u.user# = o.owner# and 
       tp.file#=s.file# and tp.block#=s.block# and tp.ts#=s.ts#
union all -- PIOT
select u.name, o.name, rpad('N0', 3), o.subname, 0, 
       tp.hiboundval, tp.hiboundlen, tp.part#, NULL, 
       TO_NUMBER(NULL),TO_NUMBER(NULL),TO_NUMBER(NULL),TO_NUMBER(NULL),
       TO_NUMBER(NULL),
       TO_NUMBER(NULL),TO_NUMBER(NULL),TO_NUMBER(NULL),TO_NUMBER(NULL),
       TO_NUMBER(NULL),TO_NUMBER(NULL),
       decode(mod(trunc(tp.flags / 4), 2), 0, 'YES', 'NO'), 
       tp.rowcnt, tp.blkcnt, tp.empcnt, tp.avgspc, tp.chncnt, tp.avgrln,
       tp.samplesize, tp.analyzetime, NULL,
       decode(bitand(tp.flags, 16), 0, 'NO', 'YES'),
       decode(bitand(tp.flags, 8), 0, 'NO', 'YES')
from   obj$ o, tabpart$ tp, user$ u
where  o.obj# = tp.obj# and o.owner# = u.user# and 
       tp.file#=0 and tp.block#=0 
union all -- Subpart
select u.name, o.name, rpad('YES', 3), o.subname, tcp.subpartcnt, 
       tcp.hiboundval, tcp.hiboundlen, tcp.part#, ts.name,
       tcp.defpctfree, tcp.defpctused, tcp.definitrans, tcp.defmaxtrans, 
       tcp.definiexts, tcp.defextsize, tcp.defminexts, tcp.defmaxexts, 
       tcp.defextpct, tcp.deflists, tcp.defgroups,
       decode(tcp.deflogging, 0, 'NONE', 1, 'YES', 2, 'NO', 'UNKNOWN'),
       tcp.rowcnt, tcp.blkcnt, tcp.empcnt, tcp.avgspc, tcp.chncnt, tcp.avgrln,
       tcp.samplesize, tcp.analyzetime, 
       decode(tcp.defbufpool, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL),
       decode(bitand(tcp.flags, 16), 0, 'NO', 'YES'),
       decode(bitand(tcp.flags, 8), 0, 'NO', 'YES')
from   obj$ o, tabcompart$ tcp, ts$ ts, user$ u
where  o.obj# = tcp.obj# and tcp.defts# = ts.ts# and u.user# = o.owner# 
/
drop public synonym DBA_TAB_PARTITIONS
/
create public synonym DBA_TAB_PARTITIONS for DBA_TAB_PARTITIONS
/
grant select on DBA_TAB_PARTITIONS to select_catalog_role
/

remark
remark  FAMILY "IND_PARTITIONS"
remark   This family of views will describe, for each index partition, the
remark   partition level information, the storage parameters for the 
remark   partition, and various partition statistics determined by ANALYZE.
remark
create or replace view USER_IND_PARTITIONS
  (INDEX_NAME, COMPOSITE, PARTITION_NAME, SUBPARTITION_COUNT, 
   HIGH_VALUE, HIGH_VALUE_LENGTH, 
   PARTITION_POSITION, STATUS, TABLESPACE_NAME, PCT_FREE, INI_TRANS, MAX_TRANS,
   INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENT, MAX_EXTENT, PCT_INCREASE, 
   FREELISTS, FREELIST_GROUPS, LOGGING, COMPRESSION, BLEVEL, LEAF_BLOCKS, 
   DISTINCT_KEYS, AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY, 
   CLUSTERING_FACTOR, NUM_ROWS, SAMPLE_SIZE, LAST_ANALYZED, BUFFER_POOL, 
   USER_STATS, PCT_DIRECT_ACCESS, GLOBAL_STATS)
as 
select io.name, rpad('NO', 3), io.subname, 0,
       ip.hiboundval, ip.hiboundlen, 
       ip.part#, decode(bitand(ip.flags, 1), 1, 'UNUSABLE', 'USABLE'), ts.name,
       ip.pctfree$, ip.initrans, ip.maxtrans, 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(s.lists, 0, 1, s.lists),
       decode(s.groups, 0, 1, s.groups),
       decode(mod(trunc(ip.flags / 4), 2), 0, 'YES', 'NO'), 
       decode(bitand(ip.flags, 1024), 0, 'DISABLED', 1024, 'ENABLED', null),
       ip.blevel, ip.leafcnt, ip.distkey, ip.lblkkey, ip.dblkkey, 
       ip.clufac, ip.rowcnt, ip.samplesize, ip.analyzetime,
       decode(s.cachehint, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL),
       decode(bitand(ip.flags, 8), 0, 'NO', 'YES'), ip.pctthres$,
       decode(bitand(ip.flags, 16), 0, 'NO', 'YES')
from   obj$ io, indpart$ ip, ts$ ts, sys.seg$ s
where  io.obj# = ip.obj# and ts.ts# = ip.ts# and ip.file#=s.file# and
       ip.block#=s.block# and ip.ts#=s.ts# and io.owner# = userenv('SCHEMAID')
union all
select io.name, rpad('YES', 3), io.subname,icp.subpartcnt, 
       icp.hiboundval, icp.hiboundlen, 
       icp.part#, 'N/A', ts.name,  
       icp.defpctfree, icp.definitrans, icp.defmaxtrans,
       icp.definiexts, icp.defextsize, icp.defminexts, icp.defmaxexts, 
       icp.defextpct, icp.deflists, icp.defgroups,
       decode(icp.deflogging, 0, 'NONE', 1, 'YES', 2, 'NO', 'UNKNOWN'),
       'N/A', icp.blevel, icp.leafcnt, icp.distkey, icp.lblkkey, icp.dblkkey, 
       icp.clufac, icp.rowcnt, icp.samplesize, icp.analyzetime,
       decode(icp.defbufpool, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL),
       decode(bitand(icp.flags, 8), 0, 'NO', 'YES'), TO_NUMBER(NULL),
       decode(bitand(icp.flags, 16), 0, 'NO', 'YES')
from   obj$ io, indcompart$ icp, ts$ ts
where  io.obj# = icp.obj# and icp.defts# = ts.ts# (+) and
       io.owner# = userenv('SCHEMAID')
/
drop public synonym USER_IND_PARTITIONS
/
create public synonym USER_IND_PARTITIONS for USER_IND_PARTITIONS
/
grant select on USER_IND_PARTITIONS to PUBLIC with grant option
/
create or replace view ALL_IND_PARTITIONS
  (INDEX_OWNER, INDEX_NAME, COMPOSITE, PARTITION_NAME, SUBPARTITION_COUNT, 
   HIGH_VALUE, HIGH_VALUE_LENGTH, 
   PARTITION_POSITION, STATUS, TABLESPACE_NAME, PCT_FREE, INI_TRANS, MAX_TRANS,
   INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENT, MAX_EXTENT, PCT_INCREASE, 
   FREELISTS, FREELIST_GROUPS, LOGGING, COMPRESSION, BLEVEL, LEAF_BLOCKS, 
   DISTINCT_KEYS, AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY, 
   CLUSTERING_FACTOR, NUM_ROWS, SAMPLE_SIZE, LAST_ANALYZED, BUFFER_POOL, 
   USER_STATS, PCT_DIRECT_ACCESS, GLOBAL_STATS)
as 
select u.name, io.name, rpad('NO', 3), io.subname, 0,
       ip.hiboundval, ip.hiboundlen, ip.part#, 
       decode(bitand(ip.flags, 1), 1, 'UNUSABLE', 'USABLE'),ts.name,
       ip.pctfree$, ip.initrans, ip.maxtrans, 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(s.lists, 0, 1, s.lists),
       decode(s.groups, 0, 1, s.groups),
       decode(mod(trunc(ip.flags / 4), 2), 0, 'YES', 'NO'), 
       decode(bitand(ip.flags, 1024), 0, 'DISABLED', 1024, 'ENABLED', null),
       ip.blevel, ip.leafcnt, ip.distkey, ip.lblkkey, ip.dblkkey, 
       ip.clufac, ip.rowcnt, ip.samplesize, ip.analyzetime,
       decode(s.cachehint, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL),
       decode(bitand(ip.flags, 8), 0, 'NO', 'YES'), ip.pctthres$,
       decode(bitand(ip.flags, 16), 0, 'NO', 'YES')
from obj$ io, indpart$ ip, ts$ ts, sys.seg$ s, ind$ i, sys.user$ u
where io.obj# = ip.obj# and ts.ts# = ip.ts# and ip.file#=s.file# and
      ip.block#=s.block# and ip.ts#=s.ts# and io.owner# = u.user# and 
      i.obj# = ip.bo# and
        (io.owner# = userenv('SCHEMAID') 
        or
        i.bo# in (select obj#
                    from objauth$
                    where grantee# in ( select kzsrorol
                                        from x$kzsro
                                      )
                   )
        or
         exists (select null from v$enabledprivs
                 where priv_number in (-45 /* LOCK ANY TABLE */,
                                       -47 /* SELECT ANY TABLE */,
                                       -48 /* INSERT ANY TABLE */,
                                       -49 /* UPDATE ANY TABLE */,
                                       -50 /* DELETE ANY TABLE */)
                 )
       )
union all
select u.name, io.name, rpad('YES', 3), io.subname, icp.subpartcnt,
       icp.hiboundval, icp.hiboundlen, icp.part#, 'N/A', ts.name,
       icp.defpctfree, icp.definitrans, icp.defmaxtrans,
       icp.definiexts, icp.defextsize, icp.defminexts, icp.defmaxexts, 
       icp.defextpct, icp.deflists, icp.defgroups,
       decode(icp.deflogging, 0, 'NONE', 1, 'YES', 2, 'NO', 'UNKNOWN'),
       'N/A', icp.blevel, icp.leafcnt, icp.distkey, icp.lblkkey, icp.dblkkey, 
       icp.clufac, icp.rowcnt, icp.samplesize, icp.analyzetime,
       decode(icp.defbufpool, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL),
       decode(bitand(icp.flags, 8), 0, 'NO', 'YES'), TO_NUMBER(NULL),
       decode(bitand(icp.flags, 16), 0, 'NO', 'YES')
from   obj$ io, indcompart$ icp, ts$ ts, ind$ i, user$ u
where  io.obj# = icp.obj# and icp.defts# = ts.ts# (+) and io.owner# = u.user# and
       i.obj# = icp.bo# and
       (io.owner# = userenv('SCHEMAID') 
        or 
	i.bo# in (select oa.obj#
             	    from sys.objauth$ oa
                    where grantee# in ( select kzsrorol
                                        from x$kzsro
                                      ) 
                   )
        or /* user has system privileges */
         exists (select null from v$enabledprivs
                 where priv_number in (-45 /* LOCK ANY TABLE */,
                                       -47 /* SELECT ANY TABLE */,
                                       -48 /* INSERT ANY TABLE */,
                                       -49 /* UPDATE ANY TABLE */,
                                       -50 /* DELETE ANY TABLE */)
                 )
      )
/
drop public synonym ALL_IND_PARTITIONS
/
create public synonym ALL_IND_PARTITIONS for ALL_IND_PARTITIONS
/
grant select on ALL_IND_PARTITIONS to PUBLIC with grant option
/
create or replace view DBA_IND_PARTITIONS
  (INDEX_OWNER, INDEX_NAME, COMPOSITE, PARTITION_NAME, SUBPARTITION_COUNT, 
   HIGH_VALUE, HIGH_VALUE_LENGTH, 
   PARTITION_POSITION, STATUS, TABLESPACE_NAME, PCT_FREE, INI_TRANS, MAX_TRANS,
   INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENT, MAX_EXTENT, PCT_INCREASE, 
   FREELISTS, FREELIST_GROUPS, LOGGING, COMPRESSION, BLEVEL, LEAF_BLOCKS, 
   DISTINCT_KEYS, AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY, 
   CLUSTERING_FACTOR, NUM_ROWS, SAMPLE_SIZE, LAST_ANALYZED, BUFFER_POOL, 
   USER_STATS, PCT_DIRECT_ACCESS, GLOBAL_STATS)
as 
select u.name, io.name, rpad('NO', 3), io.subname, 0, 
       ip.hiboundval, ip.hiboundlen, ip.part#,
       decode(bitand(ip.flags, 1), 1, 'UNUSABLE', 'USABLE'), ts.name,
       ip.pctfree$,ip.initrans, ip.maxtrans, 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(s.lists, 0, 1, s.lists),
       decode(s.groups, 0, 1, s.groups),
       decode(mod(trunc(ip.flags / 4), 2), 0, 'YES', 'NO'), 
       decode(bitand(ip.flags, 1024), 0, 'DISABLED', 1024, 'ENABLED', null),
       ip.blevel, ip.leafcnt, ip.distkey, ip.lblkkey, ip.dblkkey, 
       ip.clufac, ip.rowcnt, ip.samplesize, ip.analyzetime,
       decode(s.cachehint, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL),
       decode(bitand(ip.flags, 8), 0, 'NO', 'YES'), ip.pctthres$,
       decode(bitand(ip.flags, 16), 0, 'NO', 'YES')
from   obj$ io, indpart$ ip, ts$ ts, sys.seg$ s, user$ u
where  io.obj# = ip.obj# and ts.ts# = ip.ts# and ip.file#=s.file# and
       ip.block#=s.block# and ip.ts#=s.ts# and io.owner# = u.user#
      union all
select u.name, io.name, rpad('YES', 3), io.subname, icp.subpartcnt,
       icp.hiboundval, icp.hiboundlen, icp.part#, 'N/A', ts.name,
       icp.defpctfree, icp.definitrans, icp.defmaxtrans,
       icp.definiexts, icp.defextsize, icp.defminexts, icp.defmaxexts, 
       icp.defextpct, icp.deflists, icp.defgroups,
       decode(icp.deflogging, 0, 'NONE', 1, 'YES', 2, 'NO', 'UNKNOWN'),
       'N/A', icp.blevel, icp.leafcnt, icp.distkey, icp.lblkkey, icp.dblkkey, 
       icp.clufac, icp.rowcnt, icp.samplesize, icp.analyzetime,
       decode(icp.defbufpool, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL),
       decode(bitand(icp.flags, 8), 0, 'NO', 'YES'), TO_NUMBER(NULL),
       decode(bitand(icp.flags, 16), 0, 'NO', 'YES')
from   obj$ io, indcompart$ icp, ts$ ts, user$ u
where  io.obj# = icp.obj# and icp.defts# = ts.ts# (+) and u.user# = io.owner#
/
drop public synonym DBA_IND_PARTITIONS
/
create public synonym DBA_IND_PARTITIONS for DBA_IND_PARTITIONS
/
grant select on DBA_IND_PARTITIONS to select_catalog_role
/

remark
remark  FAMILY "TAB_SUBPARTITIONS"
remark   This family of views will describe, for each table subpartition,
remark   the subpartition level information, the storage parameters for the
remark   subpartition, and various subpartition statistics determined by
remark   ANALYZE.
create or replace view USER_TAB_SUBPARTITIONS
  (TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, SUBPARTITION_POSITION,
   TABLESPACE_NAME, PCT_FREE, PCT_USED,
   INI_TRANS, MAX_TRANS, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENT, MAX_EXTENT,
   PCT_INCREASE, FREELISTS, FREELIST_GROUPS, LOGGING, NUM_ROWS, BLOCKS,
   EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN, SAMPLE_SIZE, LAST_ANALYZED,
   BUFFER_POOL, GLOBAL_STATS, USER_STATS)
as
select po.name, po.subname, so.subname, tsp.subpart#,
       ts.name,  tsp.pctfree$, tsp.pctused$, tsp.initrans, tsp.maxtrans,
       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(s.lists, 0, 1, s.lists),
       decode(s.groups, 0, 1, s.groups),
       decode(mod(trunc(tsp.flags / 4), 2), 0, 'YES', 'NO'),
       tsp.rowcnt, tsp.blkcnt, tsp.empcnt, tsp.avgspc, tsp.chncnt, 
       tsp.avgrln, tsp.samplesize, tsp.analyzetime,
       decode(s.cachehint, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL),
       decode(bitand(tsp.flags, 16), 0, 'NO', 'YES'),
       decode(bitand(tsp.flags, 8), 0, 'NO', 'YES')
from   sys.obj$ so, sys.obj$ po, sys.tabsubpart$ tsp, sys.ts$ ts, sys.seg$ s
where  so.obj# = tsp.obj# and po.obj# = tsp.pobj# and tsp.ts# = ts.ts# and
       tsp.file# = s.file# and tsp.block# = s.block# and tsp.ts# = s.ts# and
       po.owner# = userenv('SCHEMAID')
/
drop public synonym USER_TAB_SUBPARTITIONS
/
create public synonym USER_TAB_SUBPARTITIONS for USER_TAB_SUBPARTITIONS
/
grant select on USER_TAB_SUBPARTITIONS to PUBLIC with grant option
/
create or replace view ALL_TAB_SUBPARTITIONS
  (TABLE_OWNER, TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, 
   SUBPARTITION_POSITION, TABLESPACE_NAME, PCT_FREE, PCT_USED, 
   INI_TRANS, MAX_TRANS, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENT, MAX_EXTENT,
   PCT_INCREASE, FREELISTS, FREELIST_GROUPS, LOGGING, NUM_ROWS, BLOCKS, 
   EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN, SAMPLE_SIZE, LAST_ANALYZED,
   BUFFER_POOL, GLOBAL_STATS, USER_STATS)
as 
select u.name, po.name, po.subname, so.subname, tsp.subpart#, 
       ts.name, tsp.pctfree$, tsp.pctused$, 
       tsp.initrans, tsp.maxtrans, 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(s.lists, 0, 1, s.lists), decode(s.groups, 0, 1, s.groups),
       decode(mod(trunc(tsp.flags / 4), 2), 0, 'YES', 'NO'),
       tsp.rowcnt, tsp.blkcnt, tsp.empcnt, tsp.avgspc, tsp.chncnt, 
       tsp.avgrln, tsp.samplesize, tsp.analyzetime,
       decode(s.cachehint, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL),
       decode(bitand(tsp.flags, 16), 0, 'NO', 'YES'),
       decode(bitand(tsp.flags, 8), 0, 'NO', 'YES')
from   obj$ po, obj$ so, tabcompart$ tcp, tabsubpart$ tsp, ts$ ts, sys.seg$ s,
       user$ u
where  so.obj# = tsp.obj# and po.obj# = tcp.obj# and tcp.obj# = tsp.pobj# and
       ts.ts# = tsp.ts# and u.user# = po.owner# and tsp.file#=s.file# and
       tsp.block#=s.block# and tsp.ts#=s.ts# and
       (po.owner# = userenv('SCHEMAID') 
        or tcp.bo# in
            (select oa.obj#
             from sys.objauth$ oa
             where grantee# in ( select kzsrorol
                                 from x$kzsro
                               ) 
            )
        or /* user has system privileges */
         exists (select null from v$enabledprivs
                 where priv_number in (-45 /* LOCK ANY TABLE */,
                                       -47 /* SELECT ANY TABLE */,
                                       -48 /* INSERT ANY TABLE */,
                                       -49 /* UPDATE ANY TABLE */,
                                       -50 /* DELETE ANY TABLE */)
                 )
      )
/
drop public synonym ALL_TAB_SUBPARTITIONS
/
create public synonym ALL_TAB_SUBPARTITIONS for ALL_TAB_SUBPARTITIONS
/
grant select on ALL_TAB_SUBPARTITIONS to PUBLIC with grant option
/
create or replace view DBA_TAB_SUBPARTITIONS
  (TABLE_OWNER, TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME,
   SUBPARTITION_POSITION, TABLESPACE_NAME, PCT_FREE, PCT_USED,
   INI_TRANS, MAX_TRANS, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENT, MAX_EXTENT,
   PCT_INCREASE, FREELISTS, FREELIST_GROUPS, LOGGING, NUM_ROWS, BLOCKS,
   EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN, SAMPLE_SIZE, LAST_ANALYZED,
   BUFFER_POOL, GLOBAL_STATS, USER_STATS)
as
select u.name, po.name, po.subname, so.subname, tsp.subpart#,
       ts.name,  tsp.pctfree$, tsp.pctused$, tsp.initrans, tsp.maxtrans,
       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(s.lists, 0, 1, s.lists),
       decode(s.groups, 0, 1, s.groups),
       decode(mod(trunc(tsp.flags / 4), 2), 0, 'YES', 'NO'),
       tsp.rowcnt, tsp.blkcnt, tsp.empcnt, tsp.avgspc, tsp.chncnt, 
       tsp.avgrln, tsp.samplesize, tsp.analyzetime,
       decode(s.cachehint, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL),
       decode(bitand(tsp.flags, 16), 0, 'NO', 'YES'),
       decode(bitand(tsp.flags, 8), 0, 'NO', 'YES')
from   sys.obj$ so, sys.obj$ po, sys.tabsubpart$ tsp, sys.ts$ ts, 
       sys.seg$ s, sys.user$ u
where  so.obj# = tsp.obj# and po.obj# = tsp.pobj# and tsp.ts# = ts.ts# and
       u.user# = po.owner# and tsp.file# = s.file# and tsp.block# = s.block# and
       tsp.ts# = s.ts#
/
drop public synonym DBA_TAB_SUBPARTITIONS
/
create public synonym DBA_TAB_SUBPARTITIONS for DBA_TAB_SUBPARTITIONS
/
grant select on DBA_TAB_SUBPARTITIONS to select_catalog_role
/
remark
remark  FAMILY "IND_SUBPARTITIONS"
remark   This family of views will describe, for each index subpartition,
remark   the subpartition level information, the storage parameters for the
remark   subpartition, and various subpartition statistics determined by
remark   ANALYZE.
create or replace view USER_IND_SUBPARTITIONS
  (INDEX_NAME, PARTITION_NAME, SUBPARTITION_NAME, SUBPARTITION_POSITION,
   STATUS, TABLESPACE_NAME, PCT_FREE, 
   INI_TRANS, MAX_TRANS, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENT, MAX_EXTENT,
   PCT_INCREASE, FREELISTS, FREELIST_GROUPS, LOGGING, BLEVEL, LEAF_BLOCKS, 
   DISTINCT_KEYS, AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY, 
   CLUSTERING_FACTOR, NUM_ROWS, SAMPLE_SIZE, LAST_ANALYZED, BUFFER_POOL,
   USER_STATS, GLOBAL_STATS)
as
select po.name, po.subname, so.subname, isp.subpart#,
       decode(bitand(isp.flags, 1), 1, 'UNUSABLE', 'USABLE'), ts.name,  
       isp.pctfree$, isp.initrans, isp.maxtrans,
       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(s.lists, 0, 1, s.lists),
       decode(s.groups, 0, 1, s.groups),
       decode(mod(trunc(isp.flags / 4), 2), 0, 'YES', 'NO'),
       isp.blevel, isp.leafcnt, isp.distkey, isp.lblkkey, isp.dblkkey, 
       isp.clufac, isp.rowcnt, isp.samplesize, isp.analyzetime,
       decode(s.cachehint, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL),
       decode(bitand(isp.flags, 8), 0, 'NO', 'YES'),
       decode(bitand(isp.flags, 16), 0, 'NO', 'YES')
from   sys.obj$ so, sys.obj$ po, sys.indsubpart$ isp, sys.ts$ ts, sys.seg$ s
where  so.obj# = isp.obj# and po.obj# = isp.pobj# and isp.ts# = ts.ts# and
       isp.file# = s.file# and isp.block# = s.block# and isp.ts# = s.ts# and
       po.owner# = userenv('SCHEMAID')
/
drop public synonym USER_IND_SUBPARTITIONS
/
create public synonym USER_IND_SUBPARTITIONS for USER_IND_SUBPARTITIONS
/
grant select on USER_IND_SUBPARTITIONS to PUBLIC with grant option
/

create or replace view ALL_IND_SUBPARTITIONS
  (INDEX_OWNER, INDEX_NAME, PARTITION_NAME, SUBPARTITION_NAME, SUBPARTITION_POSITION,
   STATUS, TABLESPACE_NAME, PCT_FREE, 
   INI_TRANS, MAX_TRANS, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENT, MAX_EXTENT,
   PCT_INCREASE, FREELISTS, FREELIST_GROUPS, LOGGING, BLEVEL, LEAF_BLOCKS, 
   DISTINCT_KEYS, AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY, 
   CLUSTERING_FACTOR, NUM_ROWS, SAMPLE_SIZE, LAST_ANALYZED, BUFFER_POOL,
   USER_STATS, GLOBAL_STATS)
as
select u.name, po.name, po.subname, so.subname, isp.subpart#,
       decode(bitand(isp.flags, 1), 1, 'UNUSABLE', 'USABLE'), ts.name,  
       isp.pctfree$, isp.initrans, isp.maxtrans,
       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(s.lists, 0, 1, s.lists),
       decode(s.groups, 0, 1, s.groups),
       decode(mod(trunc(isp.flags / 4), 2), 0, 'YES', 'NO'),
       isp.blevel, isp.leafcnt, isp.distkey, isp.lblkkey, isp.dblkkey, 
       isp.clufac, isp.rowcnt, isp.samplesize, isp.analyzetime,
       decode(s.cachehint, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL),
       decode(bitand(isp.flags, 8), 0, 'NO', 'YES'),
       decode(bitand(isp.flags, 16), 0, 'NO', 'YES')
from   obj$ so, sys.obj$ po, ind$ i, indcompart$ icp, indsubpart$ isp, ts$ ts,
       seg$ s, user$ u
where  so.obj# = isp.obj# and po.obj# = icp.obj# and icp.obj# = isp.pobj# and
       i.obj# = icp.bo# and ts.ts# = isp.ts# and isp.file# = s.file# and
       isp.block# = s.block# and isp.ts# = s.ts# and u.user# = po.owner# and
       (po.owner# = userenv('SCHEMAID')
        or i.bo# in
            (select oa.obj#
             from sys.objauth$ oa
             where grantee# in ( select kzsrorol
                                 from x$kzsro
                               ) 
            )
        or /* user has system privileges */
         exists (select null from v$enabledprivs
                 where priv_number in (-45 /* LOCK ANY TABLE */,
                                       -47 /* SELECT ANY TABLE */,
                                       -48 /* INSERT ANY TABLE */,
                                       -49 /* UPDATE ANY TABLE */,
                                       -50 /* DELETE ANY TABLE */)
                 )
      )
/
drop public synonym ALL_IND_SUBPARTITIONS
/
create public synonym ALL_IND_SUBPARTITIONS for ALL_IND_SUBPARTITIONS
/
grant select on ALL_IND_SUBPARTITIONS to PUBLIC with grant option
/

create or replace view DBA_IND_SUBPARTITIONS
  (INDEX_OWNER, INDEX_NAME, PARTITION_NAME, SUBPARTITION_NAME, SUBPARTITION_POSITION,
   STATUS, TABLESPACE_NAME, PCT_FREE, 
   INI_TRANS, MAX_TRANS, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENT, MAX_EXTENT,
   PCT_INCREASE, FREELISTS, FREELIST_GROUPS, LOGGING, BLEVEL, LEAF_BLOCKS, 
   DISTINCT_KEYS, AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY, 
   CLUSTERING_FACTOR, NUM_ROWS, SAMPLE_SIZE, LAST_ANALYZED, BUFFER_POOL,
   USER_STATS, GLOBAL_STATS)
as
select u.name, po.name, po.subname, so.subname, isp.subpart#,
       decode(bitand(isp.flags, 1), 1, 'UNUSABLE', 'USABLE'), ts.name,  
       isp.pctfree$, isp.initrans, isp.maxtrans,
       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(s.lists, 0, 1, s.lists),
       decode(s.groups, 0, 1, s.groups),
       decode(mod(trunc(isp.flags / 4), 2), 0, 'YES', 'NO'),
       isp.blevel, isp.leafcnt, isp.distkey, isp.lblkkey, isp.dblkkey, 
       isp.clufac, isp.rowcnt, isp.samplesize, isp.analyzetime,
       decode(s.cachehint, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL),
       decode(bitand(isp.flags, 8), 0, 'NO', 'YES'),
       decode(bitand(isp.flags, 16), 0, 'NO', 'YES')
from   sys.obj$ so, sys.obj$ po, sys.indsubpart$ isp, sys.ts$ ts, 
       sys.seg$ s, sys.user$ u
where  so.obj# = isp.obj# and po.obj# = isp.pobj# and isp.ts# = ts.ts# and
       u.user# = po.owner# and isp.file# = s.file# and isp.block# = s.block# and
       isp.ts# = s.ts#
/
drop public synonym DBA_IND_SUBPARTITIONS
/
create public synonym DBA_IND_SUBPARTITIONS for DBA_IND_SUBPARTITIONS
/
grant select on DBA_IND_SUBPARTITIONS to select_catalog_role
/

remark 
remark  FAMILY "PART_COL_STATISTICS"
remark   These views contain column statistics and histogram information
remark   for table partitions.
remark
create or replace view TP$ as
select tp.obj#, tp.bo#, c.intcol#, 
      decode(bitand(c.property, 1), 1, a.name, c.name) cname
      from sys.col$ c, sys.tabpart$ tp, attrcol$ a 
      where tp.bo# = c.obj# and
      c.obj# = a.obj#(+) and c.intcol# = a.intcol#(+) and 
      bitand(c.property,32768) != 32768    /* not unused columns */
union
select tcp.obj#, tcp.bo#, c.intcol#, 
      decode(bitand(c.property, 1), 1, a.name, c.name) cname
      from sys.col$ c, sys.tabcompart$ tcp, attrcol$ a 
      where tcp.bo# = c.obj# and
      c.obj# = a.obj#(+) and c.intcol# = a.intcol#(+) and 
      bitand(c.property,32768) != 32768    /* not unused columns */
/
grant select on TP$ to select_catalog_role
/
create or replace view USER_PART_COL_STATISTICS 
  (TABLE_NAME, PARTITION_NAME, COLUMN_NAME, NUM_DISTINCT, LOW_VALUE,
   HIGH_VALUE, DENSITY, NUM_NULLS, NUM_BUCKETS, SAMPLE_SIZE, LAST_ANALYZED,
   GLOBAL_STATS, USER_STATS, AVG_COL_LEN)
as
select o.name, o.subname, tp.cname, h.distcnt, h.lowval, h.hival, h.density, 
       h.null_cnt, decode(h.row_cnt, 0, 1, 1, 1, h.row_cnt-1),
       h.sample_size, h.timestamp#,
       decode(bitand(h.spare2, 2), 2, 'YES', 'NO'),
       decode(bitand(h.spare2, 1), 1, 'YES', 'NO'),
       h.avgcln
from  obj$ o, sys.hist_head$ h, tp$ tp
where o.obj# = tp.obj#
  and tp.obj# = h.obj#(+) and tp.intcol# = h.intcol#(+)
  and o.type# = 19 /* TABLE PARTITION */
  and o.owner# = userenv('SCHEMAID')
/
drop public synonym USER_PART_COL_STATISTICS 
/
create public synonym USER_PART_COL_STATISTICS for USER_PART_COL_STATISTICS 
/
grant select on USER_PART_COL_STATISTICS to PUBLIC with grant option
/
create or replace view ALL_PART_COL_STATISTICS 
  (OWNER, TABLE_NAME, PARTITION_NAME, COLUMN_NAME, NUM_DISTINCT, LOW_VALUE,
   HIGH_VALUE, DENSITY, NUM_NULLS, NUM_BUCKETS, SAMPLE_SIZE, LAST_ANALYZED,
   GLOBAL_STATS, USER_STATS, AVG_COL_LEN)
as
select u.name, o.name, o.subname, tp.cname, h.distcnt, h.lowval, h.hival, 
       h.density, h.null_cnt, decode(h.row_cnt, 0, 1, 1, 1, h.row_cnt-1), 
       h.sample_size, h.timestamp#,
       decode(bitand(h.spare2, 2), 2, 'YES', 'NO'),
       decode(bitand(h.spare2, 1), 1, 'YES', 'NO'),
       h.avgcln
from sys.obj$ o, sys.tabpart$ t, sys.hist_head$ h, tp$ tp, user$ u
where o.obj# = tp.obj# and t.obj# = tp.obj# and o.owner# = u.user#
  and tp.obj# = h.obj#(+) and tp.intcol# = h.intcol#(+)
  and o.type# = 19 /* TABLE PARTITION */
  and (o.owner# = userenv('SCHEMAID')
        or t.bo# in
            (select oa.obj#
             from sys.objauth$ oa
             where grantee# in ( select kzsrorol
                                 from x$kzsro
                               ) 
            )
        or /* user has system privileges */
         exists (select null from v$enabledprivs
                 where priv_number in (-45 /* LOCK ANY TABLE */,
                                       -47 /* SELECT ANY TABLE */,
                                       -48 /* INSERT ANY TABLE */,
                                       -49 /* UPDATE ANY TABLE */,
                                       -50 /* DELETE ANY TABLE */)
                 )
      )
/
drop public synonym ALL_PART_COL_STATISTICS 
/
create public synonym ALL_PART_COL_STATISTICS for ALL_PART_COL_STATISTICS 
/
grant select on ALL_PART_COL_STATISTICS to PUBLIC with grant option
/
create or replace view DBA_PART_COL_STATISTICS 
  (OWNER, TABLE_NAME, PARTITION_NAME, COLUMN_NAME, NUM_DISTINCT, LOW_VALUE,
   HIGH_VALUE, DENSITY, NUM_NULLS, NUM_BUCKETS, SAMPLE_SIZE, LAST_ANALYZED,
   GLOBAL_STATS, USER_STATS, AVG_COL_LEN)
as
select u.name, o.name, o.subname, tp.cname, h.distcnt, h.lowval, h.hival, 
       h.density, h.null_cnt, decode(h.row_cnt, 0, 1, 1, 1, h.row_cnt-1), 
        h.sample_size, h.timestamp#,
       decode(bitand(h.spare2, 2), 2, 'YES', 'NO'),
       decode(bitand(h.spare2, 1), 1, 'YES', 'NO'),
       h.avgcln
from sys.obj$ o, sys.hist_head$ h, tp$ tp, user$ u
where o.obj# = tp.obj# and o.owner# = u.user#
  and tp.obj# = h.obj#(+) and tp.intcol# = h.intcol#(+)
  and o.type# = 19 /* TABLE PARTITION */
/
drop public synonym DBA_PART_COL_STATISTICS
/
create public synonym DBA_PART_COL_STATISTICS for DBA_PART_COL_STATISTICS
/
grant select on DBA_PART_COL_STATISTICS to select_catalog_role
/

remark
remark  FAMILY "PART_HISTOGRAMS"
remark   These views contain the actual histogram data (end-points per
remark   histogram) for histograms on table partitions.
remark
create or replace view USER_PART_HISTOGRAMS
  (TABLE_NAME, PARTITION_NAME, COLUMN_NAME, BUCKET_NUMBER, 
   ENDPOINT_VALUE, ENDPOINT_ACTUAL_VALUE)
as
select o.name, o.subname,
       tp.cname,
       h.bucket,
       h.endpoint,
       h.epvalue
from sys.obj$ o, sys.histgrm$ h, tp$ tp
where o.obj# = h.obj# and h.obj# = tp.obj#
  and tp.intcol# = h.intcol#
  and o.type# = 19 /* TABLE PARTITION */
  and o.owner# = userenv('SCHEMAID')
union
select o.name, o.subname,
       tp.cname,
       0,
       h.minimum,
       null
from sys.obj$ o, sys.hist_head$ h, tp$ tp
where o.obj# = tp.obj# and tp.obj# = h.obj#
  and tp.intcol# = h.intcol#
  and o.type# = 19 /* TABLE PARTITION */
  and h.bucket_cnt = 1
  and o.owner# = userenv('SCHEMAID')
union
select o.name, o.subname,
       tp.cname,
       1,
       h.maximum,
       null
from sys.obj$ o, sys.hist_head$ h, tp$ tp
where o.obj# = tp.obj# and tp.obj# = h.obj#
  and tp.intcol# = h.intcol#
  and o.type# = 19 /* TABLE PARTITION */
  and h.bucket_cnt = 1
  and o.owner# = userenv('SCHEMAID')
/
drop public synonym USER_PART_HISTOGRAMS
/
create public synonym USER_PART_HISTOGRAMS for USER_PART_HISTOGRAMS
/
grant select on USER_PART_HISTOGRAMS to PUBLIC with grant option
/
create or replace view ALL_PART_HISTOGRAMS
  (OWNER, TABLE_NAME, PARTITION_NAME, COLUMN_NAME, BUCKET_NUMBER, 
   ENDPOINT_VALUE, ENDPOINT_ACTUAL_VALUE)
as
select u.name,
       o.name, o.subname,
       tp.cname,
       h.bucket,
       h.endpoint,
       h.epvalue
from sys.obj$ o, sys.histgrm$ h, sys.user$ u, tp$ tp
where o.obj# = tp.obj# and tp.obj# = h.obj#
      and tp.intcol# = h.intcol#
      and o.type# = 19 /* TABLE PARTITION */
      and o.owner# = u.user# and
      (o.owner# = userenv('SCHEMAID')
        or
        tp.bo# in ( select obj#
                    from sys.objauth$
                    where grantee# in ( select kzsrorol
                                         from x$kzsro
                                       )
                  )
        or /* user has system privileges */
          exists (select null from v$enabledprivs
                  where priv_number in (-45 /* LOCK ANY TABLE */,
                                        -47 /* SELECT ANY TABLE */,
                                        -48 /* INSERT ANY TABLE */,
                                        -49 /* UPDATE ANY TABLE */,
                                        -50 /* DELETE ANY TABLE */)
                  )
       )
union
select u.name,
       o.name, o.subname,
       tp.cname,
       0,
       h.minimum,
       null
from sys.obj$ o, sys.hist_head$ h, sys.user$ u, tp$ tp
where o.obj# = tp.obj# and tp.obj# = h.obj#
      and tp.intcol# = h.intcol#
      and o.type# = 19 /* TABLE PARTITION */
      and h.bucket_cnt = 1
      and o.owner# = u.user# and
      (o.owner# = userenv('SCHEMAID')
        or
        tp.bo# in ( select obj#
                    from sys.objauth$
                    where grantee# in ( select kzsrorol
                                         from x$kzsro
                                       )
                  )
        or /* user has system privileges */
          exists (select null from v$enabledprivs
                  where priv_number in (-45 /* LOCK ANY TABLE */,
                                        -47 /* SELECT ANY TABLE */,
                                        -48 /* INSERT ANY TABLE */,
                                        -49 /* UPDATE ANY TABLE */,
                                        -50 /* DELETE ANY TABLE */)
                  )
       )
union
select u.name,
       o.name, o.subname,
       tp.cname,
       1,
       h.maximum,
       null
from sys.obj$ o, sys.hist_head$ h, sys.user$ u, tp$ tp
where o.obj# = tp.obj# and tp.obj# = h.obj#
      and tp.intcol# = h.intcol#
      and o.type# = 19 /* TABLE PARTITION */
      and h.bucket_cnt = 1
      and o.owner# = u.user# and
      (o.owner# = userenv('SCHEMAID')
        or
        tp.bo# in ( select obj#
                    from sys.objauth$
                    where grantee# in ( select kzsrorol
                                         from x$kzsro
                                       )
                  )
        or /* user has system privileges */
          exists (select null from v$enabledprivs
                  where priv_number in (-45 /* LOCK ANY TABLE */,
                                        -47 /* SELECT ANY TABLE */,
                                        -48 /* INSERT ANY TABLE */,
                                        -49 /* UPDATE ANY TABLE */,
                                        -50 /* DELETE ANY TABLE */)
                  )
       )
/
drop public synonym ALL_PART_HISTOGRAMS
/
create public synonym ALL_PART_HISTOGRAMS for ALL_PART_HISTOGRAMS
/
grant select on ALL_PART_HISTOGRAMS to PUBLIC with grant option
/
create or replace view DBA_PART_HISTOGRAMS
  (OWNER, TABLE_NAME, PARTITION_NAME, COLUMN_NAME, BUCKET_NUMBER, 
   ENDPOINT_VALUE, ENDPOINT_ACTUAL_VALUE)
as
select u.name,
       o.name, o.subname,
       tp.cname,
       h.bucket,
       h.endpoint,
       h.epvalue
from sys.obj$ o, sys.histgrm$ h, sys.user$ u, tp$ tp
where o.obj# = tp.obj# and tp.obj# = h.obj# 
  and tp.intcol# = h.intcol#
  and o.type# = 19 /* TABLE PARTITION */
  and o.owner# = u.user#
union
select u.name,
       o.name, o.subname,
       tp.cname,
       0,
       h.minimum,
       null
from sys.obj$ o, sys.hist_head$ h, sys.user$ u, tp$ tp
where o.obj# = tp.obj# and tp.obj# = h.obj# 
  and tp.intcol# = h.intcol#
  and o.type# = 19 /* TABLE PARTITION */
  and h.bucket_cnt = 1
  and o.owner# = u.user#
union
select u.name,
       o.name, o.subname,
       tp.cname,
       1,
       h.maximum,
       null
from sys.obj$ o, sys.hist_head$ h, sys.user$ u, tp$ tp
where o.obj# = tp.obj# and tp.obj# = h.obj# 
  and tp.intcol# = h.intcol#
  and o.type# = 19 /* TABLE PARTITION */
  and h.bucket_cnt = 1
  and o.owner# = u.user#
/
drop public synonym DBA_PART_HISTOGRAMS
/
create public synonym DBA_PART_HISTOGRAMS for DBA_PART_HISTOGRAMS
/
grant select on DBA_PART_HISTOGRAMS to select_catalog_role
/

remark 
remark  FAMILY "SUBPART_COL_STATISTICS"
remark   These views contain column statistics and histogram information
remark   for table subpartitions.
remark
create or replace view TSP$ as
select tsp.obj#, tcp.bo#, c.intcol#, 
      decode(bitand(c.property, 1), 1, a.name, c.name) cname
      from sys.col$ c, sys.tabsubpart$ tsp, sys.tabcompart$ tcp, attrcol$ a 
      where tsp.pobj# = tcp.obj# and tcp.bo# = c.obj#
      and bitand(c.property,32768) != 32768    /* not unused columns */
      and c.obj# = a.obj#(+) and c.intcol# = a.intcol#(+)
/
grant select on TSP$ to select_catalog_role
/
create or replace view USER_SUBPART_COL_STATISTICS 
  (TABLE_NAME, SUBPARTITION_NAME, COLUMN_NAME, NUM_DISTINCT, LOW_VALUE,
   HIGH_VALUE, DENSITY, NUM_NULLS, NUM_BUCKETS, SAMPLE_SIZE, LAST_ANALYZED,
   GLOBAL_STATS, USER_STATS, AVG_COL_LEN)
as
select o.name, o.subname, tsp.cname, h.distcnt, h.lowval, h.hival, h.density, 
       h.null_cnt, decode(h.row_cnt, 0, 1, 1, 1, h.row_cnt-1),
       h.sample_size, h.timestamp#,
       decode(bitand(h.spare2, 2), 2, 'YES', 'NO'),
       decode(bitand(h.spare2, 1), 1, 'YES', 'NO'),
       h.avgcln
from sys.obj$ o, sys.hist_head$ h, tsp$ tsp
where o.obj# = tsp.obj#
  and tsp.obj# = h.obj#(+) and tsp.intcol# = h.intcol#(+)
  and o.type# = 34 /* TABLE SUBPARTITION */
  and o.owner# = userenv('SCHEMAID')
/
drop public synonym USER_SUBPART_COL_STATISTICS 
/
create public synonym USER_SUBPART_COL_STATISTICS for USER_SUBPART_COL_STATISTICS 
/
grant select on USER_SUBPART_COL_STATISTICS to PUBLIC with grant option
/
create or replace view ALL_SUBPART_COL_STATISTICS 
  (OWNER, TABLE_NAME, SUBPARTITION_NAME, COLUMN_NAME, NUM_DISTINCT, LOW_VALUE,
   HIGH_VALUE, DENSITY, NUM_NULLS, NUM_BUCKETS, SAMPLE_SIZE, LAST_ANALYZED,
   GLOBAL_STATS, USER_STATS, AVG_COL_LEN)
as
select u.name, o.name, o.subname, tsp.cname, h.distcnt, h.lowval, h.hival, 
       h.density, h.null_cnt, decode(h.row_cnt, 0, 1, 1, 1, h.row_cnt-1), 
       h.sample_size, h.timestamp#,
       decode(bitand(h.spare2, 2), 2, 'YES', 'NO'),
       decode(bitand(h.spare2, 1), 1, 'YES', 'NO'),
       h.avgcln
from sys.obj$ o, sys.hist_head$ h, tsp$ tsp, user$ u
where o.obj# = tsp.obj# and tsp.obj# = h.obj#(+)
  and tsp.intcol# = h.intcol#(+)
  and o.type# = 34 /* TABLE SUBPARTITION */
  and o.owner# = u.user#
  and (o.owner# = userenv('SCHEMAID')
        or tsp.bo# in
            (select oa.obj#
             from sys.objauth$ oa
             where grantee# in ( select kzsrorol
                                 from x$kzsro
                               ) 
            )
        or /* user has system privileges */
         exists (select null from v$enabledprivs
                 where priv_number in (-45 /* LOCK ANY TABLE */,
                                       -47 /* SELECT ANY TABLE */,
                                       -48 /* INSERT ANY TABLE */,
                                       -49 /* UPDATE ANY TABLE */,
                                       -50 /* DELETE ANY TABLE */)
                 )
      )
/
drop public synonym ALL_SUBPART_COL_STATISTICS 
/
create public synonym ALL_SUBPART_COL_STATISTICS for ALL_SUBPART_COL_STATISTICS 
/
grant select on ALL_SUBPART_COL_STATISTICS to PUBLIC with grant option
/
create or replace view DBA_SUBPART_COL_STATISTICS 
  (OWNER, TABLE_NAME, SUBPARTITION_NAME, COLUMN_NAME, NUM_DISTINCT, LOW_VALUE,
   HIGH_VALUE, DENSITY, NUM_NULLS, NUM_BUCKETS, SAMPLE_SIZE, LAST_ANALYZED,
   GLOBAL_STATS, USER_STATS, AVG_COL_LEN)
as
select u.name, o.name, o.subname, tsp.cname, h.distcnt, h.lowval, h.hival, 
       h.density, h.null_cnt, decode(h.row_cnt, 0, 1, 1, 1, h.row_cnt-1), 
       h.sample_size, h.timestamp#,
       decode(bitand(h.spare2, 2), 2, 'YES', 'NO'),
       decode(bitand(h.spare2, 1), 1, 'YES', 'NO'),
       h.avgcln
from sys.obj$ o, sys.hist_head$ h, tsp$ tsp, user$ u
where o.obj# = tsp.obj# and tsp.obj# = h.obj#(+)
  and tsp.intcol# = h.intcol#(+)
  and o.type# = 34 /* TABLE SUBPARTITION */
  and o.owner# = u.user#
/
drop public synonym DBA_SUBPART_COL_STATISTICS
/
create public synonym DBA_SUBPART_COL_STATISTICS for DBA_SUBPART_COL_STATISTICS
/
grant select on DBA_SUBPART_COL_STATISTICS to select_catalog_role
/

remark
remark  FAMILY "SUBPART_HISTOGRAMS"
remark   These views contain the actual histogram data (end-points per
remark   histogram) for histograms on table subpartitions.
remark
create or replace view USER_SUBPART_HISTOGRAMS
  (TABLE_NAME, SUBPARTITION_NAME, COLUMN_NAME, BUCKET_NUMBER, 
   ENDPOINT_VALUE, ENDPOINT_ACTUAL_VALUE)
as
select o.name, o.subname,
       tsp.cname,
       h.bucket,
       h.endpoint,
       h.epvalue
from sys.obj$ o, sys.histgrm$ h, tsp$ tsp
where o.obj# = h.obj# and h.obj# = tsp.obj#
  and tsp.intcol# = h.intcol#
  and o.type# = 34 /* TABLE SUBPARTITION */
  and o.owner# = userenv('SCHEMAID')
union
select o.name, o.subname,
       tsp.cname,
       0,
       h.minimum,
       null
from sys.obj$ o, sys.hist_head$ h, tsp$ tsp
where o.obj# = tsp.obj# and tsp.obj# = h.obj#
  and tsp.intcol# = h.intcol#
  and o.type# = 34 /* TABLE SUBPARTITION */
  and h.bucket_cnt = 1
  and o.owner# = userenv('SCHEMAID')
union
select o.name, o.subname,
       tsp.cname,
       1,
       h.maximum,
       null
from sys.obj$ o, sys.hist_head$ h, tsp$ tsp
where o.obj# = tsp.obj# and tsp.obj# = h.obj#
  and tsp.intcol# = h.intcol#
  and o.type# = 34 /* TABLE SUBPARTITION */
  and h.bucket_cnt = 1
  and o.owner# = userenv('SCHEMAID')
/
drop public synonym USER_SUBPART_HISTOGRAMS
/
create public synonym USER_SUBPART_HISTOGRAMS for USER_SUBPART_HISTOGRAMS
/
grant select on USER_SUBPART_HISTOGRAMS to PUBLIC with grant option
/
create or replace view ALL_SUBPART_HISTOGRAMS
  (OWNER, TABLE_NAME, SUBPARTITION_NAME, COLUMN_NAME, BUCKET_NUMBER, 
   ENDPOINT_VALUE, ENDPOINT_ACTUAL_VALUE)
as
select u.name,
       o.name, o.subname,
       tsp.cname,
       h.bucket,
       h.endpoint,
       h.epvalue
from sys.obj$ o, sys.histgrm$ h, sys.user$ u, tsp$ tsp
where o.obj# = tsp.obj# and tsp.obj# = h.obj#
  and tsp.intcol# = h.intcol#
  and o.type# = 34 /* TABLE SUBPARTITION */
  and o.owner# = u.user#
  and (o.owner# = userenv('SCHEMAID')
        or
        tsp.bo# in ( select obj#
                    from sys.objauth$
                    where grantee# in ( select kzsrorol
                                         from x$kzsro
                                       )
                  )
        or /* user has system privileges */
          exists (select null from v$enabledprivs
                  where priv_number in (-45 /* LOCK ANY TABLE */,
                                        -47 /* SELECT ANY TABLE */,
                                        -48 /* INSERT ANY TABLE */,
                                        -49 /* UPDATE ANY TABLE */,
                                        -50 /* DELETE ANY TABLE */)
                  )
       )
union
select u.name,
       o.name, o.subname,
       tsp.cname,
       0,
       h.minimum,
       null
from sys.obj$ o, sys.hist_head$ h, sys.user$ u, tsp$ tsp
where o.obj# = tsp.obj# and tsp.obj# = h.obj#
  and tsp.intcol# = h.intcol#
  and o.type# = 34 /* TABLE SUBPARTITION */
  and h.bucket_cnt = 1
  and o.owner# = u.user#
  and (o.owner# = userenv('SCHEMAID')
        or
        tsp.bo# in ( select obj#
                    from sys.objauth$
                    where grantee# in ( select kzsrorol
                                         from x$kzsro
                                       )
                  )
        or /* user has system privileges */
          exists (select null from v$enabledprivs
                  where priv_number in (-45 /* LOCK ANY TABLE */,
                                        -47 /* SELECT ANY TABLE */,
                                        -48 /* INSERT ANY TABLE */,
                                        -49 /* UPDATE ANY TABLE */,
                                        -50 /* DELETE ANY TABLE */)
                  )
       )
union
select u.name,
       o.name, o.subname,
       tsp.cname,
       1,
       h.maximum,
       null
from sys.obj$ o, sys.hist_head$ h, sys.user$ u, tsp$ tsp
where o.obj# = tsp.obj# and tsp.obj# = h.obj#
  and tsp.intcol# = h.intcol#
  and o.type# = 34 /* TABLE SUBPARTITION */
  and h.bucket_cnt = 1
  and o.owner# = u.user#
  and (o.owner# = userenv('SCHEMAID')
        or
        tsp.bo# in ( select obj#
                    from sys.objauth$
                    where grantee# in ( select kzsrorol
                                         from x$kzsro
                                       )
                  )
        or /* user has system privileges */
          exists (select null from v$enabledprivs
                  where priv_number in (-45 /* LOCK ANY TABLE */,
                                        -47 /* SELECT ANY TABLE */,
                                        -48 /* INSERT ANY TABLE */,
                                        -49 /* UPDATE ANY TABLE */,
                                        -50 /* DELETE ANY TABLE */)
                  )
       )
/
drop public synonym ALL_SUBPART_HISTOGRAMS
/
create public synonym ALL_SUBPART_HISTOGRAMS for ALL_SUBPART_HISTOGRAMS
/
grant select on ALL_SUBPART_HISTOGRAMS to PUBLIC with grant option
/
create or replace view DBA_SUBPART_HISTOGRAMS
  (OWNER, TABLE_NAME, SUBPARTITION_NAME, COLUMN_NAME, BUCKET_NUMBER, 
   ENDPOINT_VALUE, ENDPOINT_ACTUAL_VALUE)
as
select u.name,
       o.name, o.subname,
       tsp.cname,
       h.bucket,
       h.endpoint,
       h.epvalue
from sys.obj$ o, sys.histgrm$ h, sys.user$ u, tsp$ tsp
where o.obj# = tsp.obj# and tsp.obj# = h.obj#
  and tsp.intcol# = h.intcol#
  and o.type# = 34 /* TABLE SUBPARTITION */
  and o.owner# = u.user#
union
select u.name,
       o.name, o.subname,
       tsp.cname,
       0,
       h.minimum,
       null
from sys.obj$ o, sys.hist_head$ h, sys.user$ u, tsp$ tsp
where o.obj# = tsp.obj# and tsp.obj# = h.obj#
  and tsp.intcol# = h.intcol#
  and o.type# = 34 /* TABLE SUBPARTITION */
  and h.bucket_cnt = 1
  and o.owner# = u.user#
union
select u.name,
       o.name, o.subname,
       tsp.cname,
       1,
       h.maximum,
       null
from sys.obj$ o, sys.hist_head$ h, sys.user$ u, tsp$ tsp
where o.obj# = tsp.obj# and tsp.obj# = h.obj#
  and tsp.intcol# = h.intcol#
  and o.type# = 34 /* TABLE SUBPARTITION */
  and h.bucket_cnt = 1
  and o.owner# = u.user#
/
drop public synonym DBA_SUBPART_HISTOGRAMS
/
create public synonym DBA_SUBPART_HISTOGRAMS for DBA_SUBPART_HISTOGRAMS
/
grant select on DBA_SUBPART_HISTOGRAMS to select_catalog_role
/


remark
remark  FAMILY "SUBPART_KEY_COLUMNS"
remark   This family of views will describe the subpartitioning key columns for
remark   all Range Composite (R+H) partitioned objects.
remark
remark   using an UNION rather than an OR for speed.
create or replace view USER_SUBPART_KEY_COLUMNS
      (NAME, OBJECT_TYPE, COLUMN_NAME, COLUMN_POSITION)
as
select o.name, RPAD('TABLE', 11), 
  decode(bitand(c.property, 1), 1, a.name, c.name), spc.pos#
from   obj$ o, subpartcol$ spc, col$ c, attrcol$ a
where  spc.obj# = o.obj# and spc.obj# = c.obj#
       and c.intcol# = spc.intcol# and o.owner# = userenv('SCHEMAID')
       and c.obj# = a.obj#(+) and c.intcol# = a.intcol#(+)
union
select o.name, RPAD('INDEX', 11), 
  decode(bitand(c.property, 1), 1, a.name, c.name), spc.pos#
from   obj$ o, subpartcol$ spc, col$ c, ind$ i, attrcol$ a
where  spc.obj# = i.obj# and i.obj# = o.obj# and i.bo# = c.obj#
       and c.intcol# = spc.intcol# and o.owner# = userenv('SCHEMAID')
       and c.obj# = a.obj#(+) and c.intcol# = a.intcol#(+)
/
drop public synonym USER_SUBPART_KEY_COLUMNS
/
create public synonym USER_SUBPART_KEY_COLUMNS for USER_SUBPART_KEY_COLUMNS
/
grant select on USER_SUBPART_KEY_COLUMNS to PUBLIC with grant option
/
create or replace view ALL_SUBPART_KEY_COLUMNS
  (OWNER, NAME, OBJECT_TYPE, COLUMN_NAME, COLUMN_POSITION)
as
select u.name, o.name, RPAD('TABLE', 11), 
  decode(bitand(c.property, 1), 1, a.name, c.name), spc.pos#
from   obj$ o, subpartcol$ spc, col$ c, user$ u, attrcol$ a
where  spc.obj# = o.obj# and spc.obj# = c.obj#
       and c.intcol# = spc.intcol#
       and u.user# = o.owner# and
       c.obj# = a.obj#(+) and c.intcol# = a.intcol#(+) and 
      (o.owner# = userenv('SCHEMAID')
       or spc.obj# in
            (select oa.obj#
             from sys.objauth$ oa
             where grantee# in ( select kzsrorol
                                 from x$kzsro
                               ) 
            )
       or /* user has system privileges */
         exists (select null from v$enabledprivs
                 where priv_number in (-45 /* LOCK ANY TABLE */,
                                       -47 /* SELECT ANY TABLE */,
                                       -48 /* INSERT ANY TABLE */,
                                       -49 /* UPDATE ANY TABLE */,
                                       -50 /* DELETE ANY TABLE */)
                 )
      )
union
select u.name, o.name, RPAD('INDEX', 11), 
  decode(bitand(c.property, 1), 1, a.name, c.name), spc.pos#
from   obj$ o, subpartcol$ spc, col$ c, user$ u, ind$ i, attrcol$ a
where spc.obj# = i.obj# and i.obj# = o.obj# and i.bo# = c.obj# 
      and c.intcol# = spc.intcol#
      and u.user# = o.owner# and 
      c.obj# = a.obj#(+) and c.intcol# = a.intcol#(+) and 
      (o.owner# = userenv('SCHEMAID')
       or i.bo# in
            (select oa.obj#
             from sys.objauth$ oa
             where grantee# in ( select kzsrorol
                                 from x$kzsro
                               ) 
            )
       or /* user has system privileges */
         exists (select null from v$enabledprivs
                 where priv_number in (-45 /* LOCK ANY TABLE */,
                                       -47 /* SELECT ANY TABLE */,
                                       -48 /* INSERT ANY TABLE */,
                                       -49 /* UPDATE ANY TABLE */,
                                       -50 /* DELETE ANY TABLE */)
                 )
      )
/
drop public synonym ALL_SUBPART_KEY_COLUMNS
/
create public synonym ALL_SUBPART_KEY_COLUMNS for ALL_SUBPART_KEY_COLUMNS
/
grant select on ALL_SUBPART_KEY_COLUMNS to PUBLIC with grant option
/
create or replace view DBA_SUBPART_KEY_COLUMNS
  (OWNER, NAME, OBJECT_TYPE, COLUMN_NAME, COLUMN_POSITION)
as
select u.name, o.name, RPAD('TABLE', 11), 
  decode(bitand(c.property, 1), 1, a.name, c.name), spc.pos#
from   obj$ o, subpartcol$ spc, col$ c, user$ u, attrcol$ a
where  spc.obj# = o.obj# and spc.obj# = c.obj#
       and c.intcol# = spc.intcol#
       and u.user# = o.owner# 
       and c.obj# = a.obj#(+) and c.intcol# = a.intcol#(+)
union 
select u.name, o.name, RPAD('INDEX', 11), 
  decode(bitand(c.property, 1), 1, a.name, c.name), spc.pos#
from   obj$ o, subpartcol$ spc, col$ c, user$ u, ind$ i, attrcol$ a
where  spc.obj# = i.obj# and i.obj# = o.obj# and i.bo# = c.obj#
       and c.intcol# = spc.intcol#
       and u.user# = o.owner# 
       and c.obj# = a.obj#(+) and c.intcol# = a.intcol#(+)
/
drop public synonym DBA_SUBPART_KEY_COLUMNS
/
create public synonym DBA_SUBPART_KEY_COLUMNS for DBA_SUBPART_KEY_COLUMNS
/
grant select on DBA_SUBPART_KEY_COLUMNS to select_catalog_role
/
remark
remark  FAMILY "PART_LOBS"
remark   This family of views will describe the object level information
remark   for LOB columns contained in partitioned tables.
remark
create or replace view USER_PART_LOBS 
  (TABLE_NAME, COLUMN_NAME, LOB_NAME, LOB_INDEX_NAME, DEF_CHUNK,
   DEF_PCTVERSION, DEF_CACHE, DEF_IN_ROW,
   DEF_TABLESPACE_NAME, DEF_INITIAL_EXTENT, DEF_NEXT_EXTENT, DEF_MIN_EXTENTS, 
   DEF_MAX_EXTENTS, DEF_PCT_INCREASE, DEF_FREELISTS, DEF_FREELIST_GROUPS,
   DEF_LOGGING, DEF_BUFFER_POOL)
as 
select o.name,
       decode(bitand(c.property, 1), 1, a.name, c.name),
       lo.name, 
       io.name,
       plob.defchunk,
       plob.defpctver$,
       decode(bitand(plob.defflags, 27), 1, 'NO', 2, 'NO', 8, 'CACHEREADS',
                                         16, 'CACHEREADS', 'YES'),
       decode(plob.defpro, 0, 'NO', 'YES'),
       ts.name,
       decode(plob.definiexts, NULL, 'DEFAULT', plob.definiexts),
       decode(plob.defextsize, NULL, 'DEFAULT', plob.defextsize),
       decode(plob.defminexts, NULL, 'DEFAULT', plob.defminexts),
       decode(plob.defmaxexts, NULL, 'DEFAULT', plob.defmaxexts),
       decode(plob.defextpct,  NULL, 'DEFAULT', plob.defextpct),
       decode(plob.deflists,   NULL, 'DEFAULT', plob.deflists),
       decode(plob.defgroups,  NULL, 'DEFAULT', plob.defgroups),
       decode(bitand(plob.defflags,30), 0,'NONE', 4,'YES', 2,'NO', 8,'YES', 
                                        16,'NO', 'UNKNOWN'),
       decode(plob.defbufpool, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL)
from   sys.obj$ o, sys.col$ c, sys.lob$ l, sys.partlob$ plob, 
       sys.obj$ lo, sys.obj$ io, sys.ts$ ts, attrcol$ a
where o.owner# = userenv('SCHEMAID')
  and o.obj# = c.obj#
  and c.obj# = l.obj#
  and c.intcol# = l.intcol#
  and l.lobj# = lo.obj#
  and l.ind# = io.obj#
  and l.lobj# = plob.lobj#
  and plob.defts# = ts.ts# (+)
  and bitand(c.property,32768) != 32768           /* not unused column */
  and c.obj# = a.obj#(+) and c.intcol# = a.intcol#(+)
/ 
drop public synonym USER_PART_LOBS 
/
create public synonym USER_PART_LOBS for USER_PART_LOBS 
/
grant select on USER_PART_LOBS to PUBLIC with grant option
/
create or replace view ALL_PART_LOBS 
  (TABLE_OWNER, TABLE_NAME, COLUMN_NAME, LOB_NAME, LOB_INDEX_NAME, DEF_CHUNK,
   DEF_PCTVERSION, DEF_CACHE, DEF_IN_ROW,
   DEF_TABLESPACE_NAME, DEF_INITIAL_EXTENT, DEF_NEXT_EXTENT, DEF_MIN_EXTENTS, 
   DEF_MAX_EXTENTS, DEF_PCT_INCREASE, DEF_FREELISTS, DEF_FREELIST_GROUPS,
   DEF_LOGGING, DEF_BUFFER_POOL)
as 
select u.name, 
       o.name,
       decode(bitand(c.property, 1), 1, a.name, c.name),
       lo.name, 
       io.name,
       plob.defchunk,
       plob.defpctver$,
       decode(bitand(plob.defflags, 27), 1, 'NO', 2, 'NO', 8, 'CACHEREADS',
                                         16, 'CACHEREADS', 'YES'), 
       decode(plob.defpro, 0, 'NO', 'YES'),
       ts.name,
       decode(plob.definiexts, NULL, 'DEFAULT', plob.definiexts),
       decode(plob.defextsize, NULL, 'DEFAULT', plob.defextsize),
       decode(plob.defminexts, NULL, 'DEFAULT', plob.defminexts),
       decode(plob.defmaxexts, NULL, 'DEFAULT', plob.defmaxexts),
       decode(plob.defextpct,  NULL, 'DEFAULT', plob.defextpct),
       decode(plob.deflists,   NULL, 'DEFAULT', plob.deflists),
       decode(plob.defgroups,  NULL, 'DEFAULT', plob.defgroups),
       decode(bitand(plob.defflags,22), 0,'NONE', 4,'YES', 2,'NO',8,'YES',
                                        16,'NO', 'UNKNOWN'),
       decode(plob.defbufpool, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL)
from   sys.obj$ o, sys.col$ c, sys.lob$ l, sys.partlob$ plob, 
       sys.obj$ lo, sys.obj$ io, sys.ts$ ts, sys.user$ u, attrcol$ a
where o.owner# = u.user#
  and o.obj# = c.obj#
  and c.obj# = l.obj#
  and c.intcol# = l.intcol#
  and l.lobj# = lo.obj#
  and l.ind# = io.obj#
  and l.lobj# = plob.lobj#
  and plob.defts# = ts.ts# (+)
  and bitand(c.property,32768) != 32768           /* not unused column */
  and c.obj# = a.obj#(+) and c.intcol# = a.intcol#(+)
  and (o.owner# = userenv('SCHEMAID')
       or o.obj# in
            (select oa.obj#
             from sys.objauth$ oa
             where grantee# in ( select kzsrorol
                                 from x$kzsro
                               ) 
            )
       or exists (select null from v$enabledprivs
                  where priv_number in (-45 /* LOCK ANY TABLE */,
                                        -47 /* SELECT ANY TABLE */,
                                        -48 /* INSERT ANY TABLE */,
                                        -49 /* UPDATE ANY TABLE */,
                                        -50 /* DELETE ANY TABLE */)
                 )
      )
/ 
drop public synonym ALL_PART_LOBS 
/
create public synonym ALL_PART_LOBS for ALL_PART_LOBS 
/
grant select on ALL_PART_LOBS to PUBLIC with grant option
/
create or replace view DBA_PART_LOBS 
  (TABLE_OWNER, TABLE_NAME, COLUMN_NAME, LOB_NAME, LOB_INDEX_NAME, DEF_CHUNK,
   DEF_PCTVERSION, DEF_CACHE, DEF_IN_ROW,
   DEF_TABLESPACE_NAME, DEF_INITIAL_EXTENT, DEF_NEXT_EXTENT, DEF_MIN_EXTENTS, 
   DEF_MAX_EXTENTS, DEF_PCT_INCREASE, DEF_FREELISTS, DEF_FREELIST_GROUPS,
   DEF_LOGGING, DEF_BUFFER_POOL)
as 
select u.name, 
       o.name,
       decode(bitand(c.property, 1), 1, a.name, c.name),
       lo.name, 
       io.name,
       plob.defchunk,
       plob.defpctver$,
       decode(bitand(plob.defflags, 27), 1, 'NO', 2, 'NO', 8, 'CACHEREADS',
                                         16, 'CACHEREADS', 'YES'), 
       decode(plob.defpro, 0, 'NO', 'YES'),
       ts.name,
       decode(plob.definiexts, NULL, 'DEFAULT', plob.definiexts),
       decode(plob.defextsize, NULL, 'DEFAULT', plob.defextsize),
       decode(plob.defminexts, NULL, 'DEFAULT', plob.defminexts),
       decode(plob.defmaxexts, NULL, 'DEFAULT', plob.defmaxexts),
       decode(plob.defextpct,  NULL, 'DEFAULT', plob.defextpct),
       decode(plob.deflists,   NULL, 'DEFAULT', plob.deflists),
       decode(plob.defgroups,  NULL, 'DEFAULT', plob.defgroups),
       decode(bitand(plob.defflags,22), 0,'NONE', 4,'YES', 2,'NO', 8,'YES', 
                                        16,'NO', 'UNKNOWN'),
       decode(plob.defbufpool, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL)
from   sys.obj$ o, sys.col$ c, sys.lob$ l, sys.partlob$ plob, 
       sys.obj$ lo, sys.obj$ io, sys.ts$ ts, sys.user$ u, attrcol$ a
where o.owner# = u.user#
  and o.obj# = c.obj#
  and c.obj# = l.obj#
  and c.intcol# = l.intcol#
  and l.lobj# = lo.obj#
  and l.ind# = io.obj#
  and l.lobj# = plob.lobj#
  and plob.defts# = ts.ts# (+)
  and bitand(c.property,32768) != 32768           /* not unused column */
  and c.obj# = a.obj#(+) and c.intcol# = a.intcol#(+)
/ 
drop public synonym DBA_PART_LOBS
/
create public synonym DBA_PART_LOBS for DBA_PART_LOBS
/
grant select on DBA_PART_LOBS to select_catalog_role
/
remark
remark  FAMILY "LOB_PARTITIONS"
remark   This family of views will describe partitions of LOB columns 
remark   belonging to partitioned tables
remark
create or replace view USER_LOB_PARTITIONS 
  (TABLE_NAME, COLUMN_NAME, LOB_NAME, 
   PARTITION_NAME, LOB_PARTITION_NAME, LOB_INDPART_NAME, PARTITION_POSITION, 
   COMPOSITE, CHUNK, PCTVERSION, CACHE, IN_ROW,
   TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, 
   MAX_EXTENTS, PCT_INCREASE, FREELISTS, FREELIST_GROUPS,
   LOGGING, BUFFER_POOL)
as 
select o.name,
       decode(bitand(c.property, 1), 1, a.name, c.name),
       lo.name,
       po.subname,
       lpo.subname,
       lipo.subname,
       lf.frag#,
       rpad('N0', 3),
       lf.chunk * ts.blocksize,
       lf.pctversion$,
       decode(bitand(lf.fragflags,27), 1, 'NO', 2, 'NO', 8, 'CACHEREADS',
                                       16, 'CACHEREADS', 'YES'), 
       decode(lf.fragpro, 0, 'NO', 'YES'),
       ts.name,
       to_char(s.iniexts * ts.blocksize), 
       to_char(decode(bitand(ts.flags, 3), 1, to_number(NULL),
            s.extsize * ts.blocksize)),
       to_char(s.minexts),
       to_char(s.maxexts),
       to_char(decode(bitand(ts.flags, 3), 1, to_number(NULL),s.extpct)),
       to_char(decode(s.lists, 0, 1, s.lists)), 
       to_char(decode(s.groups, 0, 1, s.groups)),
       decode(bitand(lf.fragflags, 18), 2, 'NO', 16, 'NO', 'YES'),
       decode(s.cachehint, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL)
from   sys.obj$ o, sys.col$ c, 
       sys.lob$ l, sys.obj$ lo, 
       sys.lobfrag$ lf, sys.obj$ lpo, 
       sys.obj$ po, sys.obj$ lipo, 
       sys.partobj$ pobj,
       sys.ts$ ts, sys.seg$ s, attrcol$ a
where o.owner# = userenv('SCHEMAID')
  and pobj.obj# = o.obj#
  and mod(pobj.spare2, 256) = 0
  and o.obj# = c.obj#
  and c.obj# = l.obj#
  and c.intcol# = l.intcol#
  and l.lobj# = lo.obj#
  and l.lobj# = lf.parentobj#
  and lf.tabfragobj# = po.obj#
  and lf.fragobj# = lpo.obj#
  and lf.indfragobj# = lipo.obj#
  and lf.file# = s.file#
  and lf.block# = s.block#
  and lf.ts# = ts.ts#
  and bitand(c.property,32768) != 32768           /* not unused column */
  and c.obj# = a.obj#(+) and c.intcol# = a.intcol#(+)
union all
select o.name,
       decode(bitand(c.property, 1), 1, a.name, c.name),
       lo.name,
       po.subname,
       lpo.subname,
       lipo.subname,
       lcp.part#,
       rpad('YES', 3),
       lcp.defchunk,
       lcp.defpctver$,
       decode(bitand(lcp.defflags,27), 1, 'NO', 2, 'NO', 8, 'CACHEREADS',
                                       16, 'CACHEREADS', 'YES'), 
       decode(lcp.defpro, 0, 'NO', 'YES'),
       ts.name,
       decode(lcp.definiexts, NULL, 'DEFAULT', lcp.definiexts),
       decode(lcp.defextsize, NULL, 'DEFAULT', lcp.defextsize),
       decode(lcp.defminexts, NULL, 'DEFAULT', lcp.defminexts),
       decode(lcp.defmaxexts, NULL, 'DEFAULT', lcp.defmaxexts),
       decode(lcp.defextpct,  NULL, 'DEFAULT', lcp.defextpct),
       decode(lcp.deflists,   NULL, 'DEFAULT', lcp.deflists),
       decode(lcp.defgroups,  NULL, 'DEFAULT', lcp.defgroups),
       decode(bitand(lcp.defflags,22), 0,'NONE', 4,'YES', 2,'NO', 16,'NO', 'UNKNOWN'),
       decode(lcp.defbufpool, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL)
from   sys.obj$ o, sys.col$ c, 
       sys.lob$ l, sys.obj$ lo, 
       sys.lobcomppart$ lcp, sys.obj$ lpo, 
       sys.obj$ po, sys.obj$ lipo, 
       sys.ts$ ts, partobj$ pobj, attrcol$ a
where o.owner# = userenv('SCHEMAID')
  and pobj.obj# = o.obj#
  and mod(pobj.spare2, 256) != 0
  and o.obj# = c.obj#
  and c.obj# = l.obj#
  and c.intcol# = l.intcol#			
  and l.lobj# = lo.obj#
  and l.lobj# = lcp.lobj#
  and lcp.tabpartobj# = po.obj#
  and lcp.partobj# = lpo.obj#
  and lcp.indpartobj# = lipo.obj#
  and lcp.defts# = ts.ts# (+)
  and bitand(c.property,32768) != 32768           /* not unused column */
  and c.obj# = a.obj#(+) and c.intcol# = a.intcol#(+)
/ 
drop public synonym USER_LOB_PARTITIONS 
/
create public synonym USER_LOB_PARTITIONS for USER_LOB_PARTITIONS 
/
grant select on USER_LOB_PARTITIONS to PUBLIC with grant option
/
create or replace view ALL_LOB_PARTITIONS 
  (TABLE_OWNER, TABLE_NAME, COLUMN_NAME, LOB_NAME, 
   PARTITION_NAME, LOB_PARTITION_NAME, LOB_INDPART_NAME, PARTITION_POSITION, 
   COMPOSITE, CHUNK, PCTVERSION, CACHE, IN_ROW,
   TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, 
   MAX_EXTENTS, PCT_INCREASE, FREELISTS, FREELIST_GROUPS,
   LOGGING, BUFFER_POOL)
as 
select u.name,
       o.name,
       decode(bitand(c.property, 1), 1, a.name, c.name),
       lo.name,
       po.subname,
       lpo.subname,
       lipo.subname,
       lf.frag#,
       rpad('N0', 3),
       lf.chunk * ts.blocksize,
       lf.pctversion$,
       decode(bitand(lf.fragflags,27), 1, 'NO', 2, 'NO', 8, 'CACHEREADS',
                                       16, 'CACHEREADS', 'YES'), 
       decode(lf.fragpro, 0, 'NO', 'YES'),
       ts.name,
       to_char(s.iniexts * ts.blocksize), 
       to_char(decode(bitand(ts.flags, 3), 1, to_number(NULL),
            s.extsize * ts.blocksize)),
       to_char(s.minexts),
       to_char(s.maxexts),
       to_char(decode(bitand(ts.flags, 3), 1, to_number(NULL),s.extpct)),
       to_char(decode(s.lists, 0, 1, s.lists)), 
       to_char(decode(s.groups, 0, 1, s.groups)),
       decode(bitand(lf.fragflags, 18), 2, 'NO', 16, 'NO', 'YES'),
       decode(s.cachehint, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL)
from   sys.obj$ o, sys.col$ c, 
       sys.lob$ l, sys.obj$ lo, 
       sys.lobfrag$ lf, sys.obj$ lpo, 
       sys.obj$ po, sys.obj$ lipo, 
       sys.partobj$ pobj,
       sys.ts$ ts, sys.seg$ s, sys.user$ u, attrcol$ a
where o.owner# = u.user#
  and pobj.obj# = o.obj#
  and mod(pobj.spare2, 256) = 0
  and o.obj# = c.obj#
  and c.obj# = l.obj#
  and c.intcol# = l.intcol#
  and l.lobj# = lo.obj#
  and l.lobj# = lf.parentobj#
  and lf.tabfragobj# = po.obj#
  and lf.fragobj# = lpo.obj#
  and lf.indfragobj# = lipo.obj#
  and lf.file# = s.file#
  and lf.block# = s.block#
  and lf.ts# = ts.ts#
  and bitand(c.property,32768) != 32768           /* not unused column */
  and c.obj# = a.obj#(+) and c.intcol# = a.intcol#(+)
  and (o.owner# = userenv('SCHEMAID')
       or o.obj# in
            (select oa.obj#
             from sys.objauth$ oa
             where grantee# in ( select kzsrorol
                                 from x$kzsro
                               ) 
            )
       or exists (select null from v$enabledprivs
                  where priv_number in (-45 /* LOCK ANY TABLE */,
                                        -47 /* SELECT ANY TABLE */,
                                        -48 /* INSERT ANY TABLE */,
                                        -49 /* UPDATE ANY TABLE */,
                                        -50 /* DELETE ANY TABLE */)
                 )
      )
union all
select u.name,
       o.name,
       decode(bitand(c.property, 1), 1, a.name, c.name),
       lo.name,
       po.subname,
       lpo.subname,
       lipo.subname,
       lcp.part#,
       rpad('YES', 3),
       lcp.defchunk,
       lcp.defpctver$,
       decode(bitand(lcp.defflags, 27), 1, 'NO', 2, 'NO', 8, 'CACHEREADS',
                                       16, 'CACHEREADS', 'YES'), 
       decode(lcp.defpro, 0, 'NO', 'YES'),
       ts.name,
       decode(lcp.definiexts, NULL, 'DEFAULT', lcp.definiexts),
       decode(lcp.defextsize, NULL, 'DEFAULT', lcp.defextsize),
       decode(lcp.defminexts, NULL, 'DEFAULT', lcp.defminexts),
       decode(lcp.defmaxexts, NULL, 'DEFAULT', lcp.defmaxexts),
       decode(lcp.defextpct,  NULL, 'DEFAULT', lcp.defextpct),
       decode(lcp.deflists,   NULL, 'DEFAULT', lcp.deflists),
       decode(lcp.defgroups,  NULL, 'DEFAULT', lcp.defgroups),
       decode(bitand(lcp.defflags,22), 0,'NONE', 4,'YES', 2,'NO', 16,'NO', 'UNKNOWN'),
       decode(lcp.defbufpool, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL)
from   sys.obj$ o, sys.col$ c, 
       sys.lob$ l, sys.obj$ lo, 
       sys.lobcomppart$ lcp, sys.obj$ lpo, 
       sys.obj$ po, sys.obj$ lipo, 
       sys.ts$ ts, partobj$ pobj, sys.user$ u, attrcol$ a
where o.owner# = u.user#
  and pobj.obj# = o.obj#
  and mod(pobj.spare2, 256) != 0
  and o.obj# = c.obj#
  and c.obj# = l.obj#
  and c.intcol# = l.intcol#			
  and l.lobj# = lo.obj#
  and l.lobj# = lcp.lobj#
  and lcp.tabpartobj# = po.obj#
  and lcp.partobj# = lpo.obj#
  and lcp.indpartobj# = lipo.obj#
  and lcp.defts# = ts.ts# (+)
  and bitand(c.property,32768) != 32768           /* not unused column */
  and c.obj# = a.obj#(+) and c.intcol# = a.intcol#(+)
  and (o.owner# = userenv('SCHEMAID')
       or o.obj# in
            (select oa.obj#
             from sys.objauth$ oa
             where grantee# in ( select kzsrorol
                                 from x$kzsro
                               ) 
            )
       or exists (select null from v$enabledprivs
                  where priv_number in (-45 /* LOCK ANY TABLE */,
                                        -47 /* SELECT ANY TABLE */,
                                        -48 /* INSERT ANY TABLE */,
                                        -49 /* UPDATE ANY TABLE */,
                                        -50 /* DELETE ANY TABLE */)
                 )
      )
/ 
drop public synonym ALL_LOB_PARTITIONS 
/
create public synonym ALL_LOB_PARTITIONS for ALL_LOB_PARTITIONS 
/
grant select on ALL_LOB_PARTITIONS to PUBLIC with grant option
/
create or replace view DBA_LOB_PARTITIONS 
  (TABLE_OWNER, TABLE_NAME, COLUMN_NAME, LOB_NAME, 
   PARTITION_NAME, LOB_PARTITION_NAME, LOB_INDPART_NAME, PARTITION_POSITION, 
   COMPOSITE, CHUNK, PCTVERSION, CACHE, IN_ROW,
   TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, 
   MAX_EXTENTS, PCT_INCREASE, FREELISTS, FREELIST_GROUPS,
   LOGGING, BUFFER_POOL)
as 
select u.name,
       o.name,
       decode(bitand(c.property, 1), 1, a.name, c.name),
       lo.name,
       po.subname,
       lpo.subname,
       lipo.subname,
       lf.frag#,
       rpad('N0', 3),
       lf.chunk * ts.blocksize,
       lf.pctversion$,
       decode(bitand(lf.fragflags,27), 1, 'NO', 2, 'NO', 8, 'CACHEREADS',
                                       16, 'CACHEREADS', 'YES'), 
       decode(lf.fragpro, 0, 'NO', 'YES'),
       ts.name,
       to_char(s.iniexts * ts.blocksize), 
       to_char(decode(bitand(ts.flags, 3), 1, to_number(NULL),
            s.extsize * ts.blocksize)),
       to_char(s.minexts),
       to_char(s.maxexts),
       to_char(decode(bitand(ts.flags, 3), 1, to_number(NULL),s.extpct)),
       to_char(decode(s.lists, 0, 1, s.lists)), 
       to_char(decode(s.groups, 0, 1, s.groups)),
       decode(bitand(lf.fragflags, 18), 2, 'NO', 16, 'NO', 'YES'),
       decode(s.cachehint, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL)
from   sys.obj$ o, sys.col$ c, 
       sys.lob$ l, sys.obj$ lo, 
       sys.lobfrag$ lf, sys.obj$ lpo, 
       sys.obj$ po, sys.obj$ lipo, 
       sys.partobj$ pobj,
       sys.ts$ ts, sys.seg$ s, sys.user$ u, attrcol$ a
where o.owner# = u.user#
  and pobj.obj# = o.obj#
  and mod(pobj.spare2, 256) = 0
  and o.obj# = c.obj#
  and c.obj# = l.obj#
  and c.intcol# = l.intcol#
  and l.lobj# = lo.obj#
  and l.lobj# = lf.parentobj#
  and lf.tabfragobj# = po.obj#
  and lf.fragobj# = lpo.obj#
  and lf.indfragobj# = lipo.obj#
  and lf.file# = s.file#
  and lf.block# = s.block#
  and lf.ts# = ts.ts#
  and bitand(c.property,32768) != 32768           /* not unused column */
  and c.obj# = a.obj#(+) and c.intcol# = a.intcol#(+)
union all
select u.name,
       o.name,
       decode(bitand(c.property, 1), 1, a.name, c.name),
       lo.name,
       po.subname,
       lpo.subname,
       lipo.subname,
       lcp.part#,
       rpad('YES', 3),
       lcp.defchunk,
       lcp.defpctver$,
       decode(bitand(lcp.defflags, 27), 1, 'NO', 2, 'NO', 8, 'CACHEREADS',
                                       16, 'CACHEREADS', 'YES'), 
       decode(lcp.defpro, 0, 'NO', 'YES'),
       ts.name,
       decode(lcp.definiexts, NULL, 'DEFAULT', lcp.definiexts),
       decode(lcp.defextsize, NULL, 'DEFAULT', lcp.defextsize),
       decode(lcp.defminexts, NULL, 'DEFAULT', lcp.defminexts),
       decode(lcp.defmaxexts, NULL, 'DEFAULT', lcp.defmaxexts),
       decode(lcp.defextpct,  NULL, 'DEFAULT', lcp.defextpct),
       decode(lcp.deflists,   NULL, 'DEFAULT', lcp.deflists),
       decode(lcp.defgroups,  NULL, 'DEFAULT', lcp.defgroups),
       decode(bitand(lcp.defflags,22), 0,'NONE', 4,'YES', 2,'NO', 16,'NO', 'UNKNOWN'),
       decode(lcp.defbufpool, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL)
from   sys.obj$ o, sys.col$ c, 
       sys.lob$ l, sys.obj$ lo, 
       sys.lobcomppart$ lcp, sys.obj$ lpo, 
       sys.obj$ po, sys.obj$ lipo, 
       sys.ts$ ts, partobj$ pobj, sys.user$ u, attrcol$ a
where o.owner# = u.user#
  and pobj.obj# = o.obj#
  and mod(pobj.spare2, 256) != 0
  and o.obj# = c.obj#
  and c.obj# = l.obj#
  and c.intcol# = l.intcol#			
  and l.lobj# = lo.obj#
  and l.lobj# = lcp.lobj#
  and lcp.tabpartobj# = po.obj#
  and lcp.partobj# = lpo.obj#
  and lcp.indpartobj# = lipo.obj#
  and lcp.defts# = ts.ts# (+)
  and bitand(c.property,32768) != 32768           /* not unused column */
  and c.obj# = a.obj#(+) and c.intcol# = a.intcol#(+)
/ 
drop public synonym DBA_LOB_PARTITIONS
/
create public synonym DBA_LOB_PARTITIONS for DBA_LOB_PARTITIONS
/
grant select on DBA_LOB_PARTITIONS to select_catalog_role
/
remark
remark  FAMILY "LOB_SUBPARTITIONS"
remark   This family of views will describe subpartitions of LOB columns 
remark   belonging to partitioned tables
remark
create or replace view USER_LOB_SUBPARTITIONS 
  (TABLE_NAME, COLUMN_NAME, LOB_NAME, LOB_PARTITION_NAME, 
   SUBPARTITION_NAME, LOB_SUBPARTITION_NAME, LOB_INDSUBPART_NAME, 
   SUBPARTITION_POSITION, 
   CHUNK, PCTVERSION, CACHE, IN_ROW,
   TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, 
   MAX_EXTENTS, PCT_INCREASE, FREELISTS, FREELIST_GROUPS,
   LOGGING, BUFFER_POOL)
as 
select o.name,
       decode(bitand(c.property, 1), 1, a.name, c.name),
       lo.name,
       lpo.subname,
       spo.subname,
       lspo.subname,
       lispo.subname,
       lf.frag#,
       lf.chunk * ts.blocksize,
       lf.pctversion$,
       decode(bitand(lf.fragflags,27), 1, 'NO', 2, 'NO', 8, 'CACHEREADS',
                                       16, 'CACHEREADS', 'YES'), 
       decode(lf.fragpro, 0, 'NO', 'YES'),
       ts.name,
       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(s.lists, 0, 1, s.lists), 
       decode(s.groups, 0, 1, s.groups),
       decode(bitand(lf.fragflags, 18), 2, 'NO', 16, 'NO', 'YES'),
       decode(s.cachehint, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL)
from   sys.obj$ o, sys.col$ c, 
       sys.lob$ l, sys.obj$ lo, 
       sys.lobcomppart$ lcp, sys.obj$ lpo,
       sys.lobfrag$ lf, sys.obj$ lspo, 
       sys.obj$ spo, sys.obj$ lispo, 
       sys.partobj$ pobj,
       sys.ts$ ts, sys.seg$ s, attrcol$ a
where o.owner# = userenv('SCHEMAID')
  and pobj.obj# = o.obj#
  and mod(pobj.spare2, 256) != 0
  and o.obj# = c.obj#
  and c.obj# = l.obj#
  and c.intcol# = l.intcol#
  and l.lobj# = lo.obj#
  and l.lobj# = lcp.lobj#
  and lcp.partobj# = lpo.obj#
  and lf.parentobj# = lcp.partobj#
  and lf.tabfragobj# = spo.obj#
  and lf.fragobj# = lspo.obj#
  and lf.indfragobj# = lispo.obj#
  and lf.file# = s.file#
  and lf.block# = s.block#
  and lf.ts# = ts.ts#
  and bitand(c.property,32768) != 32768           /* not unused column */
  and c.obj# = a.obj#(+) and c.intcol# = a.intcol#(+)
/ 
drop public synonym USER_LOB_SUBPARTITIONS 
/
create public synonym USER_LOB_SUBPARTITIONS for USER_LOB_SUBPARTITIONS 
/
grant select on USER_LOB_SUBPARTITIONS to PUBLIC with grant option
/
create or replace view ALL_LOB_SUBPARTITIONS 
  (TABLE_OWNER, TABLE_NAME, COLUMN_NAME, LOB_NAME, LOB_PARTITION_NAME, 
   SUBPARTITION_NAME, LOB_SUBPARTITION_NAME, LOB_INDSUBPART_NAME, 
   SUBPARTITION_POSITION, 
   CHUNK, PCTVERSION, CACHE, IN_ROW,
   TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, 
   MAX_EXTENTS, PCT_INCREASE, FREELISTS, FREELIST_GROUPS,
   LOGGING, BUFFER_POOL)
as 
select u.name,
       o.name,
       decode(bitand(c.property, 1), 1, a.name, c.name),
       lo.name,
       lpo.subname,
       spo.subname,
       lspo.subname,
       lispo.subname,
       lf.frag#,
       lf.chunk * ts.blocksize,
       lf.pctversion$,
       decode(bitand(lf.fragflags,27), 1, 'NO', 2, 'NO', 8, 'CACHEREADS',
                                       16, 'CACHEREADS', 'YES'), 
       decode(lf.fragpro, 0, 'NO', 'YES'),
       ts.name,
       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(s.lists, 0, 1, s.lists), 
       decode(s.groups, 0, 1, s.groups),
       decode(bitand(lf.fragflags, 18), 2, 'NO', 16, 'NO', 'YES'),
       decode(s.cachehint, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL)
from   sys.obj$ o, sys.col$ c, 
       sys.lob$ l, sys.obj$ lo, 
       sys.lobcomppart$ lcp, sys.obj$ lpo,
       sys.lobfrag$ lf, sys.obj$ lspo, 
       sys.obj$ spo, sys.obj$ lispo, 
       sys.partobj$ pobj,
       sys.ts$ ts, sys.seg$ s, sys.user$ u, attrcol$ a
where o.owner# = u.user#
  and pobj.obj# = o.obj#
  and mod(pobj.spare2, 256) != 0
  and o.obj# = c.obj#
  and c.obj# = l.obj#
  and c.intcol# = l.intcol#
  and l.lobj# = lo.obj#
  and l.lobj# = lcp.lobj#
  and lcp.partobj# = lpo.obj#
  and lf.parentobj# = lcp.partobj#
  and lf.tabfragobj# = spo.obj#
  and lf.fragobj# = lspo.obj#
  and lf.indfragobj# = lispo.obj#
  and lf.file# = s.file#
  and lf.block# = s.block#
  and lf.ts# = ts.ts#
  and bitand(c.property,32768) != 32768           /* not unused column */
  and c.obj# = a.obj#(+) and c.intcol# = a.intcol#(+)
  and (o.owner# = userenv('SCHEMAID')
       or o.obj# in
            (select oa.obj#
             from sys.objauth$ oa
             where grantee# in ( select kzsrorol
                                 from x$kzsro
                               ) 
            )
       or exists (select null from v$enabledprivs
                  where priv_number in (-45 /* LOCK ANY TABLE */,
                                        -47 /* SELECT ANY TABLE */,
                                        -48 /* INSERT ANY TABLE */,
                                        -49 /* UPDATE ANY TABLE */,
                                        -50 /* DELETE ANY TABLE */)
                 )
      )
/ 
drop public synonym ALL_LOB_SUBPARTITIONS 
/
create public synonym ALL_LOB_SUBPARTITIONS for ALL_LOB_SUBPARTITIONS 
/
grant select on ALL_LOB_SUBPARTITIONS to PUBLIC with grant option
/
create or replace view DBA_LOB_SUBPARTITIONS 
  (TABLE_OWNER, TABLE_NAME, COLUMN_NAME, LOB_NAME, LOB_PARTITION_NAME, 
   SUBPARTITION_NAME, LOB_SUBPARTITION_NAME, LOB_INDSUBPART_NAME, 
   SUBPARTITION_POSITION, 
   CHUNK, PCTVERSION, CACHE, IN_ROW,
   TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, 
   MAX_EXTENTS, PCT_INCREASE, FREELISTS, FREELIST_GROUPS,
   LOGGING, BUFFER_POOL)
as 
select u.name,
       o.name,
       decode(bitand(c.property, 1), 1, a.name, c.name),
       lo.name,
       lpo.subname,
       spo.subname,
       lspo.subname,
       lispo.subname,
       lf.frag#,
       lf.chunk * ts.blocksize,
       lf.pctversion$,
       decode(bitand(lf.fragflags,27), 1, 'NO', 2, 'NO', 8, 'CACHEREADS',
                                       16, 'CACHEREADS', 'YES'), 
       decode(lf.fragpro, 0, 'NO', 'YES'),
       ts.name,
       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(s.lists, 0, 1, s.lists), 
       decode(s.groups, 0, 1, s.groups),
       decode(bitand(lf.fragflags, 18), 2, 'NO', 16, 'NO', 'YES'),
       decode(s.cachehint, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL)
from   sys.obj$ o, sys.col$ c, 
       sys.lob$ l, sys.obj$ lo, 
       sys.lobcomppart$ lcp, sys.obj$ lpo,
       sys.lobfrag$ lf, sys.obj$ lspo, 
       sys.obj$ spo, sys.obj$ lispo, 
       sys.partobj$ pobj,
       sys.ts$ ts, sys.seg$ s, sys.user$ u, attrcol$ a
where o.owner# = u.user#
  and pobj.obj# = o.obj#
  and mod(pobj.spare2, 256) != 0
  and o.obj# = c.obj#
  and c.obj# = l.obj#
  and c.intcol# = l.intcol#
  and l.lobj# = lo.obj#
  and l.lobj# = lcp.lobj#
  and lcp.partobj# = lpo.obj#
  and lf.parentobj# = lcp.partobj#
  and lf.tabfragobj# = spo.obj#
  and lf.fragobj# = lspo.obj#
  and lf.indfragobj# = lispo.obj#
  and lf.file# = s.file#
  and lf.block# = s.block#
  and lf.ts# = ts.ts#
  and bitand(c.property,32768) != 32768           /* not unused column */
  and c.obj# = a.obj#(+) and c.intcol# = a.intcol#(+)
/ 
drop public synonym DBA_LOB_SUBPARTITIONS
/
create public synonym DBA_LOB_SUBPARTITIONS for DBA_LOB_SUBPARTITIONS
/
grant select on DBA_LOB_SUBPARTITIONS to select_catalog_role
/
