rem rem $Header: rem Rem Copyright (c) 1988, 2010, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem cdpart.sql - Catalog DPART.bsq views Rem (Previously known as CATPART.SQL) Rem Rem FUNCTION Rem Creates data dictionary views for the partitioning table. Rem Rem DESCRIPTION Rem This script contains catalog views for objects in dpart.bsq. Rem 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 slynn 06/09/10 - Fix lrg-4689610: views don't handle def. seg Rem creation. Rem sursridh 05/21/10 - Bug 8937971: Return freelists, freelist_groups Rem correctly for deferred case. Rem kquinn 03/06/10 - 9444204: add predicate to dba/all_lob_templates Rem vaselvap 02/15/10 - 8334498: Change PARTITION view families to compute Rem (sub)partition position in situ. Rem sursridh 12/29/09 - Bug 9091681: SEGMENT_CREATED for IOTs. Rem atomar 12/14/09 - bug 9208109 Rem sursridh 09/29/09 - Project 31043: Deferred segment creation for Rem partitioned objects. Rem slynn 04/17/08 - Fix SecureFile Retention Problems. Rem bvaranas 11/06/08 - Support deferred segment creation for partitioned Rem objects Rem rmacnico 06/11/09 - ARCHIVE LOW/HIGH Rem bvaranas 05/22/09 - Expose interval in *_tab_partitions Rem rmacnico 04/16/09 - Bug 8252432 - archive compressiohn levels Rem adalee 03/06/09 - new cachehint Rem ramekuma 03/16/09 - Fix storage parameters in *_IND_PARTITIONS, Rem *_IND_SUBPARTITIONS views for deferred segments Rem ramekuma 11/04/08 - add SEGMENT_CREATED to *_IND_PARTITIONS, Rem *_IND_SUBPARTITIONS Rem schakrab 03/23/08 - print out nested table partition information Rem weizhang 03/13/08 - storage clause INITIAL/NEXT for ASSM segment Rem geadon 01/28/08 - bug 6642210: fix part_indexes for ref ptn local Rem index Rem schoi 07/19/07 - add 'compress_for' column to _part_tables view Rem baleti 07/15/07 - fix typo user_lob_part Rem vmarwah 06/29/07 - bug-6145832: composite partition compress bit fix Rem vmarwah 05/23/07 - Compression Type for views Rem slynn 05/17/07 - Fix bug-6059863: _part_lobs returns incorrect Rem values for SecureFiles. Rem slynn 11/20/06 - remove SYNC Rem shshanka 02/06/07 - #(5865983) - Add interval to part views Rem slynn 10/12/06 - smartfile->securefile Rem schakkap 09/20/06 - move statistics views to cdoptim.sql Rem smuthuli 09/05/06 - SmartFile support Rem slynn 07/31/06 - change csce keywords. Rem bvaranas 07/13/06 - Bug 5307067: Print out range subpartitioning Rem information for user_part_tables Rem jforsyth 06/14/06 - typo Rem jforsyth 06/06/06 - CSCE columns in lob views empty for NOLOCAL Rem geadon 05/24/06 - changes for partition by reference Rem cdilling 05/09/06 - Rename catpart.sql to cdpart.sql Rem jforsyth 03/07/06 - shifting encryption, compression, and sharing Rem flags Rem jforsyth 03/06/06 - adding ENCRYPT, COMPRESS, and SHARE columns to Rem lob views Rem mbaloglu 03/06/06 - Add SYNC column to LOB_PARTITIONS views Rem geadon 01/19/06 - Handle ts# = KTSINV for ref-part tables Rem mtakahar 10/28/05 - #(4704779) fix wrong histogram type Rem bvaranas 05/17/05 - Bug 4339223: Modify definitions of Rem partitioning views to better use index on obj$ Rem mtakahar 01/18/05 - #(3536027) fix wrong histogram type shown Rem mtakahar 09/09/04 - mon_mods$ -> mon_mods_all$ Rem vshukla 07/12/04 - #(3699607): hide *IND|LOB|*PARTITIONS entries Rem for an unusable table Rem schakkap 06/08/04 - stale_stats column for *STATISTICS views. Rem table_owner, table_name, stattype_locked Rem for *IND_STATISTICS views. Rem expose physical part# in *partv views. Rem vshukla 05/25/04 - fix iot join condition Rem vshukla 05/06/04 - add STATUS field to *_PART_TABLES, filter out Rem rows belonging to unusable tables Rem mtakahar 12/18/03 - fix NUM_BUCKETS/HISTOGRAM columns Rem kshergil 11/03/03 - 3092470: remove LOB indexes from Rem ALL_PART_INDEXES and ALL_IND_[SUB]PARTITIONS Rem schakkap 10/29/03 - #(3223755): fix *_IND_STATISTICS views Rem schakkap 09/09/03 - comment on stattype_locked Rem vshukla 09/15/03 - #3087013: add the missing the predicate in Rem [USER | DBA | ALL]_lob_[sub]partitions Rem koi 06/17/03 - 2994527: change *_TAB_PARTITIONS for IOT LOGGING Rem araghava 05/08/03 - add COMPRESSION to *_IND_SUBPARTITIONS Rem schakkap 04/15/03 - Change values for lock bits Rem schakkap 01/01/03 - lock/unlock statistics Rem schakkap 12/17/02 - add missing cols for *_TAB_STATS and *_IND_STATS Rem *_TAB_STATS -> *_TAB_STATISTICS Rem *_IND_STATS -> *_IND_STATISTICS Rem fixed table stats in *_TAB_STATISTICS Rem mtyulene 10/20/02 - add [ALL DBA USER]_TAB_STATS AND Rem [ALL DBA USER]_IND_STATS Rem mtakahar 08/06/02 - #(2352663) fix num_buckets, add histogram col Rem arithikr 01/16/02 - 1534815: fix dba_part_indexes view Rem attran 12/05/01 - List Sub_PIOT. Rem araghava 11/21/01 - 1912886: use row_number function to get Rem partition_position. Rem shshanka 11/16/01 - Fix user_lob_templates view. Rem vshukla 10/28/01 - hsc: row movement course correction!. Rem vshukla 09/26/01 - modify *_PART_TABLES, *_TAB_[SUB]PARTITIONS. Rem to include compression/row movement. Rem sbedarka 10/01/01 - #(1551726) correct COMPOSITE column typo Rem aime 08/28/01 - Fix view ALL_IND_SUBPARTITIONS Rem sbasu 08/16/01 - modify views for Range List partitioning. Rem shshanka 07/17/01 - Add views on top of defsubpart$ and defsubpartlob$. Rem gviswana 05/24/01 - CREATE AND REPLACE SYNONYM Rem htseng 04/12/01 - eliminate execute twice (remove ;). Rem smuthuli 03/15/01 - pctused,freelist,flgroups=NULL for bitmapsegs Rem spsundar 10/17/00 - fix *_ind_partitions for null tblspc for dom idx Rem attran 04/28/00 - PIOT: List Partitioning *_PART_TABLES. Rem ayalaman 03/28/00 - overflow statistics for IOT Rem spsundar 02/14/00 - update *_part_indexes and *_ind_partitions Rem sbasu 02/03/00 - support for List partitioning Rem attran 09/22/99 - PIOT: TABPART$/blkcnt Rem qyu 09/16/99 - undo cache reads lob mode logging fix 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 pctused, freelists, freelist groups are null for bitmap segments remark create or replace view USER_PART_TABLES (TABLE_NAME, PARTITIONING_TYPE, SUBPARTITIONING_TYPE, PARTITION_COUNT, DEF_SUBPARTITION_COUNT, PARTITIONING_KEY_COUNT, SUBPARTITIONING_KEY_COUNT, STATUS, 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_MAX_SIZE, DEF_PCT_INCREASE, DEF_FREELISTS, DEF_FREELIST_GROUPS, DEF_LOGGING, DEF_COMPRESSION, DEF_COMPRESS_FOR, DEF_BUFFER_POOL, DEF_FLASH_CACHE, DEF_CELL_FLASH_CACHE, REF_PTN_CONSTRAINT_NAME, INTERVAL, IS_NESTED, DEF_SEGMENT_CREATION) as select o.name, decode(po.parttype, 1, 'RANGE', 2, 'HASH', 3, 'SYSTEM', 4, 'LIST', 5, 'REFERENCE', 'UNKNOWN'), decode(mod(po.spare2, 256), 0, 'NONE', 1, 'RANGE', 2, 'HASH', 3, 'SYSTEM', 4, 'LIST', 5, 'REFERENCE', 'UNKNOWN'), po.partcnt, mod(trunc(po.spare2/65536), 65536), po.partkeycols, mod(trunc(po.spare2/256), 256), decode(bitand(t.trigflag, 1073741824), 1073741824, 'UNUSABLE', 'VALID'), ts.name, po.defpctfree, decode(bitand(ts.flags, 32), 32, to_number(NULL), po.defpctused), po.definitrans, po.defmaxtrans, decode(po.deftiniexts, NULL, 'DEFAULT', po.deftiniexts), decode(po.defextsize, NULL, 'DEFAULT', po.defextsize), decode(po.defminexts, NULL, 'DEFAULT', po.defminexts), decode(po.defmaxexts, NULL, 'DEFAULT', po.defmaxexts), decode(po.defmaxsize, NULL, 'DEFAULT', po.defmaxsize), decode(po.defextpct, NULL, 'DEFAULT', po.defextpct), po.deflists, po.defgroups, decode(po.deflogging, 0, 'NONE', 1, 'YES', 2, 'NO', 'UNKNOWN'), decode(bitand(mod(trunc(po.spare2/4294967296),256), 3), 0, 'NONE', 1, 'ENABLED', 2, 'DISABLED', 'UNKNOWN'), -- compression info is in byte 4 of spare2 case bitand(mod(trunc(po.spare2/4294967296),256), 63) -- 6 bits in use when 0 then NULL when 1 then 'BASIC' -- 00000001 when 2 then NULL when 5 then 'OLTP' -- 00000101 when 9 then 'QUERY LOW' -- 00001001 when 17 then 'QUERY HIGH' -- 00010001 when 25 then 'ARCHIVE LOW' -- 00011001 when 33 then 'ARCHIVE HIGH' -- 00100001 else 'UNKNOWN' end, -- internal decode(bitand(po.spare1, 3), 1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'), decode(bitand(po.spare1, 12)/4, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(po.spare1, 48)/16, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), (select c.name from sys.con$ c, sys.cdef$ cd where c.con# = cd.con# and cd.obj# = o.obj# and cd.type# = 4 and bitand(cd.defer, 512) != 0), po.interval_str, decode(bitand(t.property,8224), 8224, 'YES', 'NO'), decode(bitand(po.flags, 6144), 4096, 'YES', 2048, 'NO', 'NONE') 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 o.subname IS NULL and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL 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', 4, 'LIST', 5, 'REFERENCE', 'UNKNOWN'), decode(mod(po.spare2, 256), 0, 'NONE', 1, 'RANGE', 2, 'HASH', 3, 'SYSTEM', 4, 'LIST', 5, 'REFERENCE', 'UNKNOWN'), po.partcnt, mod(trunc(po.spare2/65536), 65536), po.partkeycols, mod(trunc(po.spare2/256), 256), decode(bitand(t.trigflag, 1073741824), 1073741824, 'UNUSABLE', 'VALID'), 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.defmaxsize, NULL, 'DEFAULT', po.defmaxsize), NULL,--decode(po.defextpct, NULL, 'DEFAULT', po.defextpct), TO_NUMBER(NULL),TO_NUMBER(NULL),--po.deflists, po.defgroups, decode(po.deflogging, 0, 'NONE', 1, 'YES', 2, 'NO', 'UNKNOWN'), 'N/A', null, decode(bitand(po.spare1, 3), 1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'), decode(bitand(po.spare1, 12)/4, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(po.spare1, 48)/16, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), NULL -- ref-partitioned IOT is not supported so skip the sub-query, ,po.interval_str -- Select interval_str anyways ,'N/A' , decode(bitand(po.flags, 6144), 4096, 'YES', 2048, 'NO', 'NONE') 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 o.subname IS NULL and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL and bitand(t.property, 64 + 128) != 0 / create or replace 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, STATUS, 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_MAX_SIZE, DEF_PCT_INCREASE, DEF_FREELISTS, DEF_FREELIST_GROUPS, DEF_LOGGING, DEF_COMPRESSION, DEF_COMPRESS_FOR, DEF_BUFFER_POOL, DEF_FLASH_CACHE, DEF_CELL_FLASH_CACHE, REF_PTN_CONSTRAINT_NAME, INTERVAL, IS_NESTED, DEF_SEGMENT_CREATION) as select u.name, o.name, decode(po.parttype, 1, 'RANGE', 2, 'HASH', 3, 'SYSTEM', 4, 'LIST', 5, 'REFERENCE', 'UNKNOWN'), decode(mod(po.spare2, 256), 0, 'NONE', 1, 'RANGE', 2, 'HASH', 3, 'SYSTEM', 4, 'LIST', 5, 'REFERENCE', 'UNKNOWN'), po.partcnt, mod(trunc(po.spare2/65536), 65536), po.partkeycols, mod(trunc(po.spare2/256), 256), decode(bitand(t.trigflag, 1073741824), 1073741824, 'UNUSABLE', 'VALID'), ts.name, po.defpctfree, decode(bitand(ts.flags, 32), 32, to_number(NULL), po.defpctused), po.definitrans, po.defmaxtrans, decode(po.deftiniexts, NULL, 'DEFAULT', po.deftiniexts), decode(po.defextsize, NULL, 'DEFAULT', po.defextsize), decode(po.defminexts, NULL, 'DEFAULT', po.defminexts), decode(po.defmaxexts, NULL, 'DEFAULT', po.defmaxexts), decode(po.defmaxsize, NULL, 'DEFAULT', po.defmaxsize), decode(po.defextpct, NULL, 'DEFAULT', po.defextpct), decode(bitand(ts.flags, 32), 32, to_number(NULL), po.deflists), decode(bitand(ts.flags, 32), 32, to_number(NULL),po.defgroups), decode(po.deflogging, 0, 'NONE', 1, 'YES', 2, 'NO', 'UNKNOWN'), decode(bitand(mod(trunc(po.spare2/4294967296),256), 3), 0, 'NONE', 1, 'ENABLED', 2, 'DISABLED', 'UNKNOWN'), -- compression info is in byte 4 of spare2 case bitand(mod(trunc(po.spare2/4294967296),256), 63) -- 6 bits in use when 0 then NULL when 1 then 'BASIC' -- 00000001 when 2 then NULL when 5 then 'OLTP' -- 00000101 when 9 then 'QUERY LOW' -- 00001001 when 17 then 'QUERY HIGH' -- 00010001 when 25 then 'ARCHIVE LOW' -- 00011001 when 33 then 'ARCHIVE HIGH' -- 00100001 else 'UNKNOWN' end, -- internal ilevels decode(bitand(po.spare1, 3), 1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'), decode(bitand(po.spare1, 12)/4, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(po.spare1, 48)/16, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), (select c.name from sys.con$ c, sys.cdef$ cd where c.con# = cd.con# and cd.obj# = o.obj# and cd.type# = 4 and bitand(cd.defer, 512) != 0), po.interval_str, decode(bitand(t.property,8224), 8224, 'YES', 'NO'), decode(bitand(po.flags, 6144), 4096, 'YES', 2048, 'NO', 'NONE') 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.subname IS NULL and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL 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', 4, 'LIST', 5, 'REFERENCE', 'UNKNOWN'), decode(mod(po.spare2, 256), 0, 'NONE', 1, 'RANGE', 2, 'HASH', 3, 'SYSTEM', 4, 'LIST', 5, 'REFERENCE', 'UNKNOWN'), po.partcnt, mod(trunc(po.spare2/65536), 65536), po.partkeycols, mod(trunc(po.spare2/256), 256), decode(bitand(t.trigflag, 1073741824), 1073741824, 'UNUSABLE', 'VALID'), 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.defmaxsize, NULL, 'DEFAULT', po.defmaxsize), NULL,--decode(po.defextpct, NULL, 'DEFAULT', po.defextpct), TO_NUMBER(NULL),TO_NUMBER(NULL),--po.deflists, po.defgroups, decode(po.deflogging, 0, 'NONE', 1, 'YES', 2, 'NO', 'UNKNOWN'), 'N/A', null, decode(bitand(po.spare1, 3), 1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'), decode(bitand(po.spare1, 12)/4, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(po.spare1, 48)/16, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), NULL -- ref-partitioned IOT is not supported so skip the sub-query, ,po.interval_str ,'N/A' , decode(bitand(po.flags, 6144), 4096, 'YES', 2048, 'NO', 'NONE') 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.subname IS NULL and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL 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 */) ) ) / create or replace 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, STATUS, 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_MAX_SIZE, DEF_PCT_INCREASE, DEF_FREELISTS, DEF_FREELIST_GROUPS, DEF_LOGGING, DEF_COMPRESSION, DEF_COMPRESS_FOR, DEF_BUFFER_POOL, DEF_FLASH_CACHE, DEF_CELL_FLASH_CACHE, REF_PTN_CONSTRAINT_NAME, INTERVAL, IS_NESTED, DEF_SEGMENT_CREATION) as select u.name, o.name, decode(po.parttype, 1, 'RANGE', 2, 'HASH', 3, 'SYSTEM', 4, 'LIST', 5, 'REFERENCE', 'UNKNOWN'), decode(mod(po.spare2, 256), 0, 'NONE', 1, 'RANGE', 2, 'HASH', 3, 'SYSTEM', 4, 'LIST', 5, 'REFERENCE', 'UNKNOWN'), po.partcnt, mod(trunc(po.spare2/65536), 65536), po.partkeycols, mod(trunc(po.spare2/256), 256), decode(bitand(t.trigflag, 1073741824), 1073741824, 'UNUSABLE', 'VALID'), ts.name, po.defpctfree, decode(bitand(ts.flags, 32), 32, to_number(NULL),po.defpctused), po.definitrans, po.defmaxtrans, decode(po.deftiniexts, NULL, 'DEFAULT', po.deftiniexts), decode(po.defextsize, NULL, 'DEFAULT', po.defextsize), decode(po.defminexts, NULL, 'DEFAULT', po.defminexts), decode(po.defmaxexts, NULL, 'DEFAULT', po.defmaxexts), decode(po.defmaxsize, NULL, 'DEFAULT', po.defmaxsize), decode(po.defextpct, NULL, 'DEFAULT', po.defextpct), decode(bitand(ts.flags, 32), 32, to_number(NULL),po.deflists), decode(bitand(ts.flags, 32), 32, to_number(NULL), po.defgroups), decode(po.deflogging, 0, 'NONE', 1, 'YES', 2, 'NO', 'UNKNOWN'), decode(bitand(mod(trunc(po.spare2/4294967296),256), 3), 0, 'NONE', 1, 'ENABLED', 2, 'DISABLED', 'UNKNOWN'), -- compression info is in byte 4 of spare2 case bitand(mod(trunc(po.spare2/4294967296),256), 63) -- 6 bits in use when 0 then NULL when 1 then 'BASIC' -- 00000001 when 2 then NULL when 5 then 'OLTP' -- 00000101 when 9 then 'QUERY LOW' -- 00001001 when 17 then 'QUERY HIGH' -- 00010001 when 25 then 'ARCHIVE LOW' -- 00011001 when 33 then 'ARCHIVE HIGH' -- 00100001 else 'UNKNOWN' end, -- internal decode(bitand(po.spare1, 3), 1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'), decode(bitand(po.spare1, 12)/4, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(po.spare1, 48)/16, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), (select c.name from sys.con$ c, sys.cdef$ cd where c.con# = cd.con# and cd.obj# = o.obj# and cd.type# = 4 and bitand(cd.defer, 512) != 0), po.interval_str, decode(bitand(t.property,8224), 8224, 'YES', 'NO'), decode(bitand(po.flags, 6144), 4096, 'YES', 2048, 'NO', 'NONE') 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 o.subname IS NULL and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL 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', 4, 'LIST', 5, 'REFERENCE', 'UNKNOWN'), decode(mod(po.spare2, 256), 0, 'NONE', 1, 'RANGE', 2, 'HASH', 3, 'SYSTEM', 4, 'LIST', 5, 'REFERENCE', 'UNKNOWN'), po.partcnt, mod(trunc(po.spare2/65536), 65536), po.partkeycols, mod(trunc(po.spare2/256), 256), decode(bitand(t.trigflag, 1073741824), 1073741824, 'UNUSABLE', 'VALID'), 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.defmaxsize, NULL, 'DEFAULT', po.defmaxsize), NULL,--decode(po.defextpct, NULL, 'DEFAULT', po.defextpct), TO_NUMBER(NULL),TO_NUMBER(NULL),--po.deflists, po.defgroups, decode(po.deflogging, 0, 'NONE', 1, 'YES', 2, 'NO', 'UNKNOWN'), 'N/A', null, decode(bitand(po.spare1, 3), 1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'), decode(bitand(po.spare1, 12)/4, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(po.spare1, 48)/16, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), NULL -- ref-partitioned IOT is not supported so skip the sub-query , po.interval_str ,'N/A' , decode(bitand(po.flags, 6144), 4096, 'YES', 2048, 'NO', 'NONE') 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 o.subname IS NULL and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL and bitand(t.property, 64 + 128) != 0 / create or replace 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_MAX_SIZE, DEF_PCT_INCREASE, DEF_FREELISTS, DEF_FREELIST_GROUPS, DEF_LOGGING, DEF_BUFFER_POOL, DEF_FLASH_CACHE, DEF_CELL_FLASH_CACHE, DEF_PARAMETERS, INTERVAL) as select io.name, o.name, decode(po.parttype, 1, 'RANGE', 2, 'HASH', 3, 'SYSTEM', 4, 'LIST', 5, 'REFERENCE', 'UNKNOWN'), decode(mod(po.spare2, 256), 0, 'NONE', 1, 'RANGE', 2, 'HASH', 3, 'SYSTEM', 4, 'LIST', 5, 'REFERENCE', '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.defmaxsize, NULL, 'DEFAULT', po.defmaxsize), decode(po.defextpct, NULL, 'DEFAULT', po.defextpct), po.deflists, po.defgroups, decode(po.deflogging, 0, 'NONE', 1, 'YES', 2, 'NO', 'UNKNOWN'), decode(bitand(po.spare1, 3), 1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'), decode(bitand(po.spare1, 12)/4, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(po.spare1, 48)/16, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), po.parameters, po.interval_str 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') and io.namespace = 4 and io.remoteowner IS NULL and io.linkname IS NULL and io.subname IS NULL / create or replace 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_MAX_SIZE, DEF_PCT_INCREASE, DEF_FREELISTS, DEF_FREELIST_GROUPS, DEF_LOGGING, DEF_BUFFER_POOL, DEF_FLASH_CACHE, DEF_CELL_FLASH_CACHE, DEF_PARAMETERS, INTERVAL) as select u.name, io.name, o.name, decode(po.parttype, 1, 'RANGE', 2, 'HASH', 3, 'SYSTEM', 4, 'LIST', 5, 'REFERENCE', 'UNKNOWN'), decode(mod(po.spare2, 256), 0, 'NONE', 1, 'RANGE', 2, 'HASH', 3, 'SYSTEM', 4, 'LIST', 5, 'REFERENCE', '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.defmaxsize, NULL, 'DEFAULT', po.defmaxsize), decode(po.defextpct, NULL, 'DEFAULT', po.defextpct), po.deflists, po.defgroups, decode(po.deflogging, 0, 'NONE', 1, 'YES', 2, 'NO', 'UNKNOWN'), decode(bitand(po.spare1, 3), 1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'), decode(bitand(po.spare1, 12)/4, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(po.spare1, 48)/16, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), po.parameters, po.interval_str 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 i.type# != 8 and /* not LOB index */ io.subname IS NULL and io.namespace = 4 and io.remoteowner IS NULL and io.linkname IS NULL 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 */) ) ) / create or replace 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_MAX_SIZE, DEF_PCT_INCREASE, DEF_FREELISTS, DEF_FREELIST_GROUPS, DEF_LOGGING, DEF_BUFFER_POOL, DEF_FLASH_CACHE, DEF_CELL_FLASH_CACHE, DEF_PARAMETERS, INTERVAL) as select u.name, io.name, o.name, decode(po.parttype, 1, 'RANGE', 2, 'HASH', 3, 'SYSTEM', 4, 'LIST', 5, 'REFERENCE', 'UNKNOWN'), decode(mod(po.spare2, 256), 0, 'NONE', 1, 'RANGE', 2, 'HASH', 3, 'SYSTEM', 4, 'LIST', 5, 'REFERENCE', '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.defmaxsize, NULL, 'DEFAULT', po.defmaxsize), decode(po.defextpct, NULL, 'DEFAULT', po.defextpct), po.deflists, po.defgroups, decode(po.deflogging, 0, 'NONE', 1, 'YES', 2, 'NO', 'UNKNOWN'), decode(bitand(po.spare1, 3), 1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'), decode(bitand(po.spare1, 12)/4, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(po.spare1, 48)/16, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), po.parameters, po.interval_str 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.namespace = 4 and io.remoteowner IS NULL and io.linkname IS NULL and io.subname IS NULL / create or replace 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, 'TABLE', 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 o.subname IS NULL and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL and c.obj# = a.obj#(+) and c.intcol# = a.intcol#(+) union select io.name, 'INDEX', 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 io.namespace = 4 and io.remoteowner IS NULL and io.linkname IS NULL and io.subname IS NULL and c.obj# = a.obj#(+) and c.intcol# = a.intcol#(+) / create or replace 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, 'TABLE', 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.subname IS NULL and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL 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, 'INDEX', 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.subname IS NULL and io.namespace = 4 and io.remoteowner IS NULL and io.linkname IS NULL 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 */) ) ) / create or replace 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, 'TABLE', 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#(+) and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL and o.subname IS NULL union select u.name, io.name, 'INDEX', 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.namespace = 4 and io.remoteowner IS NULL and io.linkname IS NULL and io.subname IS NULL / create or replace public synonym DBA_PART_KEY_COLUMNS for DBA_PART_KEY_COLUMNS / grant select on DBA_PART_KEY_COLUMNS to select_catalog_role / remark remark The following views are created because the fragment# in the remark dictionaries no longer go from 1->n. Instead they go from a->b remark Further we now allows holes in the number sequence. remark These views are used to replicate the old behaviour of fragment#s. remark The phypart# is exposed in these views. It can be used in place remark of partition position where holes are not an issue. remark eg: It is used in *_IND_STATISTICS views in join to find remark corresponding table partition of an index partition. remark Using part# will be slow because of row_number() function. create or replace view tabpartv$ (obj#, dataobj#, bo#, part#, hiboundlen, hiboundval, ts#, file#, block#, pctfree$, pctused$, initrans, maxtrans, flags, analyzetime, samplesize, rowcnt, blkcnt, empcnt, avgspc, chncnt, avgrln, phypart#) as select obj#, dataobj#, bo#, row_number() over (partition by bo# order by part#), hiboundlen, hiboundval, ts#, file#, block#, pctfree$, pctused$, initrans, maxtrans, flags, analyzetime, samplesize, rowcnt, blkcnt, empcnt, avgspc, chncnt, avgrln, part# from tabpart$ / grant select on tabpartv$ to select_catalog_role / create or replace view tabcompartv$ (obj#, dataobj#, bo#, part#, hiboundlen, hiboundval, subpartcnt, flags, defts#, defpctfree, defpctused, definitrans, defmaxtrans, definiexts, defextsize, defminexts, defmaxexts, defmaxsize, defextpct, deflists, defgroups, deflogging, defbufpool, analyzetime, samplesize, rowcnt, blkcnt, empcnt, avgspc, chncnt, avgrln, spare1, spare2, spare3, phypart#) as select obj#, dataobj#, bo#, row_number() over (partition by bo# order by part#), hiboundlen, hiboundval, subpartcnt, flags, defts#, defpctfree, defpctused, definitrans, defmaxtrans, definiexts, defextsize, defminexts, defmaxexts,defmaxsize, defextpct, deflists, defgroups, deflogging, defbufpool, analyzetime, samplesize, rowcnt, blkcnt, empcnt, avgspc, chncnt, avgrln, spare1, spare2, spare3, part# from tabcompart$; grant select on tabcompartv$ to select_catalog_role / create or replace view tabsubpartv$ (obj#, dataobj#, pobj#, subpart#, flags, ts#, file#, block#, pctfree$, pctused$, initrans, maxtrans, analyzetime, samplesize, rowcnt, blkcnt, empcnt, avgspc, chncnt, avgrln, spare1, spare2, spare3, hiboundlen, hiboundval, physubpart#) as select obj#, dataobj#, pobj#, row_number() over (partition by pobj# order by subpart#), flags, ts#, file#, block#, pctfree$, pctused$, initrans, maxtrans, analyzetime, samplesize, rowcnt, blkcnt, empcnt, avgspc, chncnt, avgrln, spare1, spare2, spare3, hiboundlen, hiboundval, subpart# from tabsubpart$ / grant select on tabsubpartv$ to select_catalog_role / create or replace view indpartv$ (obj#, dataobj#, bo#, part#, hiboundlen, hiboundval, flags, ts#, file#, block#, pctfree$, pctthres$, initrans, maxtrans, analyzetime, samplesize, rowcnt, blevel, leafcnt, distkey, lblkkey, dblkkey, clufac, spare1, spare2, spare3, inclcol, phypart#) as select obj#, dataobj#, bo#, row_number() over (partition by bo# order by part#), hiboundlen, hiboundval, flags, ts#, file#, block#, pctfree$, pctthres$, initrans, maxtrans, analyzetime, samplesize, rowcnt, blevel, leafcnt, distkey, lblkkey, dblkkey, clufac, spare1, spare2, spare3, inclcol, part# from indpart$ / grant select on indpartv$ to select_catalog_role / create or replace view indcompartv$ (obj#, dataobj#, bo#, part#, hiboundlen, hiboundval, subpartcnt, flags, defts#, defpctfree, definitrans, defmaxtrans, definiexts, defextsize, defminexts, defmaxexts, defmaxsize, defextpct, deflists, defgroups, deflogging, defbufpool, analyzetime, samplesize, rowcnt, blevel, leafcnt, distkey, lblkkey, dblkkey, clufac, spare1, spare2, spare3, phypart#) as select obj#, dataobj#, bo#, row_number() over (partition by bo# order by part#), hiboundlen, hiboundval, subpartcnt, flags, defts#, defpctfree, definitrans, defmaxtrans, definiexts, defextsize, defminexts, defmaxexts, defmaxsize, defextpct, deflists, defgroups, deflogging, defbufpool, analyzetime, samplesize, rowcnt, blevel, leafcnt, distkey, lblkkey, dblkkey, clufac, spare1, spare2, spare3, part# from indcompart$ / grant select on indcompartv$ to select_catalog_role / create or replace view indsubpartv$ (obj#, dataobj#, pobj#, subpart#, flags, ts#, file#, block#, pctfree$, initrans, maxtrans, analyzetime, samplesize, rowcnt, blevel, leafcnt, distkey, lblkkey, dblkkey, clufac, spare1, spare2, spare3, hiboundlen, hiboundval, physubpart#) as select obj#, dataobj#, pobj#, row_number() over (partition by pobj# order by subpart#), flags, ts#, file#, block#, pctfree$, initrans, maxtrans, analyzetime, samplesize, rowcnt, blevel, leafcnt, distkey, lblkkey, dblkkey, clufac, spare1, spare2, spare3, hiboundlen, hiboundval, subpart# from indsubpart$ / grant select on indsubpartv$ to select_catalog_role / create or replace view lobfragv$ (fragobj#, parentobj#, tabfragobj#, indfragobj#, frag#, fragtype$, ts#, file#, block#, chunk, pctversion$, fragflags, fragpro, spare1, spare2, spare3) as select fragobj#, parentobj#, tabfragobj#, indfragobj#, row_number() over (partition by parentobj# order by frag#), fragtype$, ts#, file#, block#, chunk, pctversion$, fragflags, fragpro, spare1, spare2, spare3 from lobfrag$ / grant select on lobfragv$ to select_catalog_role / create or replace view lobcomppartv$ (partobj#, lobj#, tabpartobj#, indpartobj#, part#, defts#, defchunk, defpctver$, defflags, defpro, definiexts, defextsize, defminexts, defmaxexts, defmaxsize, defretention, defmintime, defextpct, deflists, defgroups, defbufpool, spare1, spare2, spare3) as select partobj#, lobj#, tabpartobj#, indpartobj#, row_number() over (partition by lobj# order by part#), defts#, defchunk, defpctver$, defflags, defpro, definiexts, defextsize, defminexts, defmaxexts, defmaxsize, defretention, defmintime, defextpct, deflists, defgroups, defbufpool, spare1, spare2, spare3 from lobcomppart$ / grant select on lobcomppartv$ 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 pctused, freelists, freelist groups are null for bitmap segments 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, MAX_SIZE, PCT_INCREASE, FREELISTS, FREELIST_GROUPS, LOGGING, COMPRESSION, COMPRESS_FOR, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN,SAMPLE_SIZE, LAST_ANALYZED, BUFFER_POOL, FLASH_CACHE, CELL_FLASH_CACHE, GLOBAL_STATS, USER_STATS, IS_NESTED, PARENT_TABLE_PARTITION, INTERVAL, SEGMENT_CREATED) as select o.name, 'NO', o.subname, 0, tp.hiboundval, tp.hiboundlen, row_number() over (partition by o.name order by tp.part#), ts.name, tp.pctfree$, decode(bitand(ts.flags, 32), 32, to_number(NULL), tp.pctused$), tp.initrans, tp.maxtrans, decode(bitand(tp.flags, 65536), 65536, ds.initial_stg * ts.blocksize, s.iniexts * ts.blocksize), decode(bitand(tp.flags, 65536), 65536, ds.next_stg * ts.blocksize, s.extsize * ts.blocksize), decode(bitand(tp.flags, 65536), 65536, ds.minext_stg, s.minexts), decode(bitand(tp.flags, 65536), 65536, ds.maxext_stg, s.maxexts), decode(bitand(tp.flags, 65536), 65536, ds.maxsiz_stg * ts.blocksize, decode(bitand(s.spare1, 4194304), 4194304, bitmapranges, NULL)), decode(bitand(ts.flags, 3), 1, to_number(NULL), decode(bitand(tp.flags, 65536), 65536, ds.pctinc_stg, s.extpct)), decode(bitand(ts.flags, 32), 32, to_number(NULL), decode(bitand(tp.flags, 65536), 65536, decode(ds.frlins_stg, 0, 1, ds.frlins_stg), decode(s.lists, 0, 1, s.lists))), decode(bitand(ts.flags, 32), 32, to_number(NULL), decode(bitand(tp.flags, 65536), 65536, decode(ds.maxins_stg, 0, 1, ds.maxins_stg), decode(s.groups, 0, 1, s.groups))), decode(mod(trunc(tp.flags / 4), 2), 0, 'YES', 'NO'), case when (bitand(tp.flags, 65536) = 65536) then decode(bitand(ds.flags_stg, 4), 4, 'ENABLED', 'DISABLED') else decode(bitand(s.spare1, 2048), 2048, 'ENABLED', 'DISABLED') end, case when (bitand(tp.flags, 65536) = 65536) then decode(bitand(ds.flags_stg, 4), 4, case when bitand(ds.cmpflag_stg, 3) = 1 then 'BASIC' when bitand(ds.cmpflag_stg, 3) = 2 then 'OLTP' else decode(ds.cmplvl_stg, 1, 'QUERY LOW', 2, 'QUERY HIGH', 3, 'ARCHIVE LOW', 'ARCHIVE HIGH') end, null) else decode(bitand(s.spare1, 2048), 0, null, case when bitand(s.spare1, 16777216) = 16777216 then 'OLTP' when bitand(s.spare1, 100663296) = 33554432 -- 0x2000000 then 'QUERY LOW' when bitand(s.spare1, 100663296) = 67108864 -- 0x4000000 then 'QUERY HIGH' when bitand(s.spare1, 100663296) = 100663296 -- 0x2000000+0x4000000 then 'ARCHIVE LOW' when bitand(s.spare1, 134217728) = 134217728 -- 0x8000000 then 'ARCHIVE HIGH' else 'BASIC' end) end, tp.rowcnt, tp.blkcnt, tp.empcnt, tp.avgspc, tp.chncnt, tp.avgrln, tp.samplesize, tp.analyzetime, decode(bitand(decode(bitand(tp.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 3), 1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'), decode(bitand(decode(bitand(tp.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 12)/4, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(decode(bitand(tp.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 48)/16, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(tp.flags, 16), 0, 'NO', 'YES'), decode(bitand(tp.flags, 8), 0, 'NO', 'YES'), decode(bitand(o.flags, 16384), 0, 'NO', 'YES'), case bitand(o.flags, 16384) --is the object a nested table partition? when 16384 then (select o1.subname from obj$ o1 where o1.obj#= (select tp1.obj# from tabpartv$ tp1, tabpartv$ tp2, ntab$ nt where tp2.bo# = tp.bo# and tp2.obj# = tp.obj# and tp1.part# = tp2.part# and tp1.bo#=nt.obj# and nt.ntab#=tp.bo#)) else null end, decode(bitand(tp.flags, 32768), 32768, 'YES', 'NO'), decode(bitand(tp.flags, 65536), 65536, 'NO', 'YES') from obj$ o, tabpart$ tp, ts$ ts, sys.seg$ s, sys.tab$ t, sys.deferred_stg$ ds where o.obj# = tp.obj# and ts.ts# = tp.ts# and tp.obj# = ds.obj#(+) and tp.file#=s.file#(+) and tp.block#=s.block#(+) and tp.ts#=s.ts#(+) and bitand(t.property, 64) != 64 and tp.bo# = t.obj# and bitand(t.trigflag, 1073741824) != 1073741824 and o.owner# = userenv('SCHEMAID') and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL union all -- IOT PARTITIONS select o.name, 'NO', o.subname, 0, tp.hiboundval, tp.hiboundlen, row_number() over (partition by o.name order by 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),TO_NUMBER(NULL), NULL, 'N/A', 'N/A', tp.rowcnt, TO_NUMBER(NULL), TO_NUMBER(NULL), 0, tp.chncnt, tp.avgrln, tp.samplesize, tp.analyzetime, NULL, NULL, NULL, decode(bitand(tp.flags, 16), 0, 'NO', 'YES'), decode(bitand(tp.flags, 8), 0, 'NO', 'YES'), 'N/A', 'N/A', 'N/A', decode(bitand(tp.flags, 65536), 65536, 'NO', 'YES') from obj$ o, tabpart$ tp, tab$ t where o.obj# = tp.obj# and tp.bo# = t.obj# and bitand(t.trigflag, 1073741824) != 1073741824 and bitand(t.property, 64) = 64 and o.owner# = userenv('SCHEMAID') and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL union all -- COMPOSITE PARTITIONS select o.name, 'YES', o.subname, tcp.subpartcnt, tcp.hiboundval, tcp.hiboundlen, row_number() over (partition by o.name order by tcp.part#), ts.name, tcp.defpctfree, decode(bitand(ts.flags, 32), 32, to_number(NULL), tcp.defpctused), tcp.definitrans, tcp.defmaxtrans, tcp.definiexts, tcp.defextsize, tcp.defminexts, tcp.defmaxexts, tcp.defmaxsize, tcp.defextpct, decode(bitand(ts.flags, 32), 32, to_number(NULL), tcp.deflists), decode(bitand(ts.flags, 32), 32, to_number(NULL),tcp.defgroups), decode(tcp.deflogging, 0, 'NONE', 1, 'YES', 2, 'NO', 'UNKNOWN'), decode(bitand(tcp.spare2, 3), 1, 'ENABLED', 2, 'DISABLED', 'NONE'), decode(bitand(tcp.spare2, 1), 0, null, case bitand(tcp.spare2, 63) -- 1st 6 bits used when 1 then 'BASIC' -- 00000001 when 5 then 'OLTP' -- 00000101 when 9 then 'QUERY LOW' -- 00001001 when 17 then 'QUERY HIGH' -- 00010001 when 25 then 'ARCHIVE LOW' -- 00011001 when 33 then 'ARCHIVE HIGH' -- 00100001 else 'UNKNOWN' end), -- internal ilevels tcp.rowcnt, tcp.blkcnt, tcp.empcnt, tcp.avgspc, tcp.chncnt, tcp.avgrln, tcp.samplesize, tcp.analyzetime, decode(bitand(tcp.defbufpool, 3), 1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'), decode(bitand(tcp.defbufpool, 12)/4, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(tcp.defbufpool, 48)/16, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(tcp.flags, 16), 0, 'NO', 'YES'), decode(bitand(tcp.flags, 8), 0, 'NO', 'YES'), 'N/A', 'N/A', decode(bitand(tcp.flags, 32768), 32768, 'YES', 'NO'), decode(bitand(tcp.spare2, 768), 256, 'NO', 512, 'YES', 'NONE') from obj$ o, tabcompart$ tcp, ts$ ts, tab$ t where o.obj# = tcp.obj# and tcp.defts# = ts.ts# and o.owner# = userenv('SCHEMAID') and bitand(t.property, 64) != 64 and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL and tcp.bo# = t.obj# and bitand(t.trigflag, 1073741824) != 1073741824 / create or replace 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, MAX_SIZE, PCT_INCREASE, FREELISTS, FREELIST_GROUPS, LOGGING, COMPRESSION, COMPRESS_FOR, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN, SAMPLE_SIZE, LAST_ANALYZED, BUFFER_POOL, FLASH_CACHE, CELL_FLASH_CACHE, GLOBAL_STATS, USER_STATS, IS_NESTED, PARENT_TABLE_PARTITION, INTERVAL, SEGMENT_CREATED) as select u.name, o.name, 'NO', o.subname, 0, tp.hiboundval, tp.hiboundlen, row_number() over (partition by u.name, o.name order by tp.part#), ts.name, tp.pctfree$, decode(bitand(ts.flags, 32), 32, to_number(NULL), tp.pctused$), tp.initrans, tp.maxtrans, decode(bitand(tp.flags, 65536), 65536, ds.initial_stg * ts.blocksize, s.iniexts * ts.blocksize), decode(bitand(tp.flags, 65536), 65536, ds.next_stg * ts.blocksize, s.extsize * ts.blocksize), decode(bitand(tp.flags, 65536), 65536, ds.minext_stg, s.minexts), decode(bitand(tp.flags, 65536), 65536, ds.maxext_stg, s.maxexts), decode(bitand(tp.flags, 65536), 65536, ds.maxsiz_stg * ts.blocksize, decode(bitand(s.spare1, 4194304), 4194304, bitmapranges, NULL)), decode(bitand(ts.flags, 3), 1, to_number(NULL), decode(bitand(tp.flags, 65536), 65536, ds.pctinc_stg, s.extpct)), decode(bitand(ts.flags, 32), 32, to_number(NULL), decode(bitand(tp.flags, 65536), 65536, decode(ds.frlins_stg, 0, 1, ds.frlins_stg), decode(s.lists, 0, 1, s.lists))), decode(bitand(ts.flags, 32), 32, to_number(NULL), decode(bitand(tp.flags, 65536), 65536, decode(ds.maxins_stg, 0, 1, ds.maxins_stg), decode(s.groups, 0, 1, s.groups))), decode(mod(trunc(tp.flags / 4), 2), 0, 'YES', 'NO'), case when (bitand(tp.flags, 65536) = 65536) then decode(bitand(ds.flags_stg, 4), 4, 'ENABLED', 'DISABLED') else decode(bitand(s.spare1, 2048), 2048, 'ENABLED', 'DISABLED') end, case when (bitand(tp.flags, 65536) = 65536) then decode(bitand(ds.flags_stg, 4), 4, case when bitand(ds.cmpflag_stg, 3) = 1 then 'BASIC' when bitand(ds.cmpflag_stg, 3) = 2 then 'OLTP' else decode(ds.cmplvl_stg, 1, 'QUERY LOW', 2, 'QUERY HIGH', 3, 'ARCHIVE LOW', 'ARCHIVE HIGH') end, null) else decode(bitand(s.spare1, 2048), 0, null, case when bitand(s.spare1, 16777216) = 16777216 then 'OLTP' when bitand(s.spare1, 100663296) = 33554432 -- 0x2000000 then 'QUERY LOW' when bitand(s.spare1, 100663296) = 67108864 -- 0x4000000 then 'QUERY HIGH' when bitand(s.spare1, 100663296) = 100663296 -- 0x2000000+0x4000000 then 'ARCHIVE LOW' when bitand(s.spare1, 134217728) = 134217728 -- 0x8000000 then 'ARCHIVE HIGH' else 'BASIC' end) end, tp.rowcnt, tp.blkcnt, tp.empcnt, tp.avgspc, tp.chncnt, tp.avgrln, tp.samplesize, tp.analyzetime, decode(bitand(decode(bitand(tp.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 3), 1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'), decode(bitand(decode(bitand(tp.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 12)/4, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(decode(bitand(tp.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 48)/16, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(tp.flags, 16), 0, 'NO', 'YES'), decode(bitand(tp.flags, 8), 0, 'NO', 'YES'), decode(bitand(o.flags, 16384), 0, 'NO', 'YES'), case bitand(o.flags, 16384) --is the object a nested table partition? when 16384 then (select o1.subname from obj$ o1 where o1.obj#= (select tp1.obj# from tabpartv$ tp1, tabpartv$ tp2, ntab$ nt where tp2.bo# = tp.bo# and tp2.obj# = tp.obj# and tp1.part# = tp2.part# and tp1.bo#=nt.obj# and nt.ntab#=tp.bo#)) else null end, decode(bitand(tp.flags, 32768), 32768, 'YES', 'NO'), decode(bitand(tp.flags, 65536), 65536, 'NO', 'YES') from obj$ o, tabpart$ tp, ts$ ts, sys.seg$ s, user$ u, tab$ t, deferred_stg$ ds where o.obj# = tp.obj# and ts.ts# = tp.ts# and u.user# = o.owner# and tp.obj# = ds.obj#(+) and tp.file#=s.file#(+) and tp.block#=s.block#(+) and tp.ts#=s.ts#(+) and bitand(t.property, 64) != 64 and tp.bo# = t.obj# and bitand(t.trigflag, 1073741824) != 1073741824 and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL 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 -- IOT PARTITIONS select u.name, o.name, 'NO', o.subname, 0, tp.hiboundval, tp.hiboundlen, row_number() over (partition by u.name, o.name order by 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),TO_NUMBER(NULL), NULL, 'N/A', 'N/A', tp.rowcnt, TO_NUMBER(NULL), TO_NUMBER(NULL), 0, tp.chncnt, tp.avgrln, tp.samplesize, tp.analyzetime, NULL, NULL, NULL, decode(bitand(tp.flags, 16), 0, 'NO', 'YES'), decode(bitand(tp.flags, 8), 0, 'NO', 'YES'), 'N/A', 'N/A', 'N/A', decode(bitand(tp.flags, 65536), 65536, 'NO', 'YES') from obj$ o, tabpart$ tp, user$ u, tab$ t where o.obj# = tp.obj# and o.owner# = u.user# and tp.bo# = t.obj# and bitand(t.trigflag, 1073741824) != 1073741824 and bitand(t.property, 64) = 64 and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL 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 -- COMPOSITE PARTITIONS select u.name, o.name, 'YES', o.subname, tcp.subpartcnt, tcp.hiboundval, tcp.hiboundlen, row_number() over (partition by u.name, o.name order by tcp.part#), ts.name, tcp.defpctfree, decode(bitand(ts.flags, 32), 32, to_number(NULL), tcp.defpctused), tcp.definitrans, tcp.defmaxtrans, tcp.definiexts, tcp.defextsize, tcp.defminexts, tcp.defmaxexts, tcp.defmaxsize, tcp.defextpct, decode(bitand(ts.flags, 32), 32, to_number(NULL), tcp.deflists), decode(bitand(ts.flags, 32), 32, to_number(NULL), tcp.defgroups), decode(tcp.deflogging, 0, 'NONE', 1, 'YES', 2, 'NO', 'UNKNOWN'), decode(bitand(tcp.spare2, 3), 1, 'ENABLED', 2, 'DISABLED', 'NONE'), decode(bitand(tcp.spare2, 1), 0, null, case bitand(tcp.spare2, 63) -- 1st 6 bits used when 1 then 'BASIC' -- 00000001 when 5 then 'OLTP' -- 00000101 when 9 then 'QUERY LOW' -- 00001001 when 17 then 'QUERY HIGH' -- 00010001 when 25 then 'ARCHIVE LOW' -- 00011001 when 33 then 'ARCHIVE HIGH' -- 00100001 else 'UNKNOWN' end), -- internal tcp.rowcnt, tcp.blkcnt, tcp.empcnt, tcp.avgspc, tcp.chncnt, tcp.avgrln, tcp.samplesize, tcp.analyzetime, decode(bitand(tcp.defbufpool, 3), 1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'), decode(bitand(tcp.defbufpool, 12)/4, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(tcp.defbufpool, 48)/16, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(tcp.flags, 16), 0, 'NO', 'YES'), decode(bitand(tcp.flags, 8), 0, 'NO', 'YES'), 'N/A', 'N/A', decode(bitand(tcp.flags, 32768), 32768, 'YES', 'NO'), decode(bitand(tcp.spare2, 768), 256, 'NO', 512, 'YES', 'NONE') from obj$ o, tabcompart$ tcp, ts$ ts, user$ u, tab$ t where o.obj# = tcp.obj# and tcp.defts# = ts.ts# and u.user# = o.owner# and tcp.bo# = t.obj# and bitand(t.trigflag, 1073741824) != 1073741824 and bitand(t.property, 64) != 64 and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL 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 */) ) ) / create or replace 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, MAX_SIZE, PCT_INCREASE, FREELISTS, FREELIST_GROUPS, LOGGING, COMPRESSION, COMPRESS_FOR, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN, SAMPLE_SIZE, LAST_ANALYZED, BUFFER_POOL, FLASH_CACHE, CELL_FLASH_CACHE, GLOBAL_STATS, USER_STATS, IS_NESTED, PARENT_TABLE_PARTITION, INTERVAL, SEGMENT_CREATED) as select u.name, o.name, 'NO', o.subname, 0, tp.hiboundval, tp.hiboundlen, row_number() over (partition by u.name, o.name order by tp.part#), ts.name, tp.pctfree$, decode(bitand(ts.flags, 32), 32, to_number(NULL), tp.pctused$), tp.initrans, tp.maxtrans, decode(bitand(tp.flags, 65536), 65536, ds.initial_stg * ts.blocksize, s.iniexts * ts.blocksize), decode(bitand(tp.flags, 65536), 65536, ds.next_stg * ts.blocksize, s.extsize * ts.blocksize), decode(bitand(tp.flags, 65536), 65536, ds.minext_stg, s.minexts), decode(bitand(tp.flags, 65536), 65536, ds.maxext_stg, s.maxexts), decode(bitand(tp.flags, 65536), 65536, ds.maxsiz_stg * ts.blocksize, decode(bitand(s.spare1, 4194304), 4194304, bitmapranges, NULL)), decode(bitand(ts.flags, 3), 1, to_number(NULL), decode(bitand(tp.flags, 65536), 65536, ds.pctinc_stg, s.extpct)), decode(bitand(ts.flags, 32), 32, to_number(NULL), decode(bitand(tp.flags, 65536), 65536, decode(ds.frlins_stg, 0, 1, ds.frlins_stg), decode(s.lists, 0, 1, s.lists))), decode(bitand(ts.flags, 32), 32, to_number(NULL), decode(bitand(tp.flags, 65536), 65536, decode(ds.maxins_stg, 0, 1, ds.maxins_stg), decode(s.groups, 0, 1, s.groups))), decode(mod(trunc(tp.flags / 4), 2), 0, 'YES', 'NO'), case when (bitand(tp.flags, 65536) = 65536) then decode(bitand(ds.flags_stg, 4), 4, 'ENABLED', 'DISABLED') else decode(bitand(s.spare1, 2048), 2048, 'ENABLED', 'DISABLED') end, case when (bitand(tp.flags, 65536) = 65536) then decode(bitand(ds.flags_stg, 4), 4, case when bitand(ds.cmpflag_stg, 3) = 1 then 'BASIC' when bitand(ds.cmpflag_stg, 3) = 2 then 'OLTP' else decode(ds.cmplvl_stg, 1, 'QUERY LOW', 2, 'QUERY HIGH', 3, 'ARCHIVE LOW', 'ARCHIVE HIGH') end, null) else decode(bitand(s.spare1, 2048), 0, null, case when bitand(s.spare1, 16777216) = 16777216 then 'OLTP' when bitand(s.spare1, 100663296) = 33554432 -- 0x2000000 then 'QUERY LOW' when bitand(s.spare1, 100663296) = 67108864 -- 0x4000000 then 'QUERY HIGH' when bitand(s.spare1, 100663296) = 100663296 -- 0x2000000+0x4000000 then 'ARCHIVE LOW' when bitand(s.spare1, 134217728) = 134217728 -- 0x8000000 then 'ARCHIVE HIGH' else 'BASIC' end) end, tp.rowcnt, tp.blkcnt, tp.empcnt, tp.avgspc, tp.chncnt, tp.avgrln, tp.samplesize, tp.analyzetime, decode(bitand(decode(bitand(tp.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 3), 1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'), decode(bitand(decode(bitand(tp.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 12)/4, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(decode(bitand(tp.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 48)/16, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(tp.flags, 16), 0, 'NO', 'YES'), decode(bitand(tp.flags, 8), 0, 'NO', 'YES'), decode(bitand(o.flags, 16384), 0, 'NO', 'YES'), case bitand(o.flags, 16384) --is the object a nested table partition? when 16384 then (select o1.subname from obj$ o1 where o1.obj#= (select tp1.obj# from tabpartv$ tp1, tabpartv$ tp2, ntab$ nt where tp2.bo# = tp.bo# and tp2.obj# = tp.obj# and tp1.part# = tp2.part# and tp1.bo#=nt.obj# and nt.ntab#=tp.bo#)) else null end, decode(bitand(tp.flags, 32768), 32768, 'YES', 'NO'), decode(bitand(tp.flags, 65536), 65536, 'NO', 'YES') from obj$ o, tabpart$ tp, ts$ ts, sys.seg$ s, user$ u, tab$ t, sys.deferred_stg$ ds where o.obj# = tp.obj# and ts.ts# = tp.ts# and u.user# = o.owner# and tp.obj# = ds.obj#(+) and tp.file#=s.file#(+) and tp.block#=s.block#(+) and tp.ts#=s.ts#(+) and bitand(t.property, 64) != 64 and tp.bo# = t.obj# and bitand(t.trigflag, 1073741824) != 1073741824 and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL union all -- IOT PARTITIONS select u.name, o.name, 'NO', o.subname, 0, tp.hiboundval, tp.hiboundlen, row_number() over (partition by u.name, o.name order by 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),TO_NUMBER(NULL), NULL, 'N/A', 'N/A', tp.rowcnt, TO_NUMBER(NULL), TO_NUMBER(NULL), 0, tp.chncnt, tp.avgrln, tp.samplesize, tp.analyzetime, NULL, NULL, NULL, decode(bitand(tp.flags, 16), 0, 'NO', 'YES'), decode(bitand(tp.flags, 8), 0, 'NO', 'YES'), 'N/A', 'N/A', 'N/A', decode(bitand(tp.flags, 65536), 65536, 'NO', 'YES') from obj$ o, tabpart$ tp, user$ u, tab$ t where o.obj# = tp.obj# and o.owner# = u.user# and tp.bo# = t.obj# and bitand(t.property, 64) = 64 and bitand(t.trigflag, 1073741824) != 1073741824 and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL union all -- COMPOSITE PARTITIONS select u.name, o.name, 'YES', o.subname, tcp.subpartcnt, tcp.hiboundval, tcp.hiboundlen, row_number() over (partition by u.name, o.name order by tcp.part#), ts.name, tcp.defpctfree, decode(bitand(ts.flags, 32), 32, to_number(NULL), tcp.defpctused), tcp.definitrans, tcp.defmaxtrans, tcp.definiexts, tcp.defextsize, tcp.defminexts, tcp.defmaxexts, tcp.defmaxsize, tcp.defextpct, decode(bitand(ts.flags, 32), 32, to_number(NULL), tcp.deflists), decode(bitand(ts.flags, 32), 32, to_number(NULL), tcp.defgroups), decode(tcp.deflogging, 0, 'NONE', 1, 'YES', 2, 'NO', 'UNKNOWN'), decode(bitand(tcp.spare2, 3), 1, 'ENABLED', 2, 'DISABLED', 'NONE'), decode(bitand(tcp.spare2, 1), 0, null, case bitand(tcp.spare2, 63) -- 1st 6 bits used when 1 then 'BASIC' -- 00000001 when 5 then 'OLTP' -- 00000101 when 9 then 'QUERY LOW' -- 00001001 when 17 then 'QUERY HIGH' -- 00010001 when 25 then 'ARCHIVE LOW' -- 00011001 when 33 then 'ARCHIVE HIGH' -- 00100001 else 'UNKNOWN' end), -- internal ilevels tcp.rowcnt, tcp.blkcnt, tcp.empcnt, tcp.avgspc, tcp.chncnt, tcp.avgrln, tcp.samplesize, tcp.analyzetime, decode(bitand(tcp.defbufpool, 3), 1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'), decode(bitand(tcp.defbufpool, 12)/4, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(tcp.defbufpool, 48)/16, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(tcp.flags, 16), 0, 'NO', 'YES'), decode(bitand(tcp.flags, 8), 0, 'NO', 'YES'), 'N/A', 'N/A', decode(bitand(tcp.flags, 32768), 32768, 'YES', 'NO'), decode(bitand(tcp.spare2, 768), 256, 'NO', 512, 'YES', 'NONE') from obj$ o, tabcompart$ tcp, ts$ ts, user$ u, tab$ t where o.obj# = tcp.obj# and tcp.defts# = ts.ts# and u.user# = o.owner# and tcp.bo# = t.obj# and bitand(t.trigflag, 1073741824) != 1073741824 and bitand(t.property, 64) != 64 and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL / create or replace 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 pctused, freelists, freelist groups are null for bitmap segments 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, MAX_SIZE, 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, FLASH_CACHE, CELL_FLASH_CACHE, USER_STATS, PCT_DIRECT_ACCESS, GLOBAL_STATS, DOMIDX_OPSTATUS, PARAMETERS, INTERVAL, SEGMENT_CREATED) as select io.name, 'NO', io.subname, 0, ip.hiboundval, ip.hiboundlen, row_number() over (partition by io.name order by ip.part#), decode(bitand(ip.flags, 1), 1, 'UNUSABLE', 'USABLE'), ts.name, ip.pctfree$, ip.initrans, ip.maxtrans, decode(bitand(ip.flags, 65536), 65536, ds.initial_stg * ts.blocksize, s.iniexts * ts.blocksize), decode(bitand(ip.flags, 65536), 65536, ds.next_stg * ts.blocksize, s.extsize * ts.blocksize), decode(bitand(ip.flags, 65536), 65536, ds.minext_stg, s.minexts), decode(bitand(ip.flags, 65536), 65536, ds.maxext_stg, s.maxexts), decode(bitand(ip.flags, 65536), 65536, ds.maxsiz_stg * ts.blocksize, decode(bitand(s.spare1, 4194304), 4194304, bitmapranges, NULL)), decode(bitand(ts.flags, 3), 1, to_number(NULL), decode(bitand(ip.flags, 65536), 65536, ds.pctinc_stg, s.extpct)), decode(bitand(ts.flags, 32), 32, to_number(NULL), decode(bitand(ip.flags, 65536), 65536, decode(ds.frlins_stg, 0, 1, ds.frlins_stg), decode(s.lists, 0, 1, s.lists))), decode(bitand(ts.flags, 32), 32, to_number(NULL), decode(bitand(ip.flags, 65536), 65536, decode(ds.maxins_stg, 0, 1, ds.maxins_stg), 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(bitand(decode(bitand(ip.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 3), 1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'), decode(bitand(decode(bitand(ip.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 12)/4, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(decode(bitand(ip.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 48)/16, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(ip.flags, 8), 0, 'NO', 'YES'), ip.pctthres$, decode(bitand(ip.flags, 16), 0, 'NO', 'YES'), '','', decode(bitand(ip.flags, 32768), 32768, 'YES', 'NO'), decode(bitand(ip.flags, 65536), 65536, 'NO', 'YES') from obj$ io, indpart$ ip, ts$ ts, sys.seg$ s, ind$ i, tab$ t, sys.deferred_stg$ ds 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 ip.obj# = ds.obj#(+) and io.owner# = userenv('SCHEMAID') and ip.bo# = i.obj# and i.type# != 9 and i.bo# = t.obj# and bitand(t.trigflag, 1073741824) != 1073741824 and io.namespace = 4 and io.remoteowner IS NULL and io.linkname IS NULL union all select io.name, 'YES', io.subname,icp.subpartcnt, icp.hiboundval, icp.hiboundlen, row_number() over (partition by io.name order by icp.part#), 'N/A', ts.name, icp.defpctfree, icp.definitrans, icp.defmaxtrans, icp.definiexts, icp.defextsize, icp.defminexts, icp.defmaxexts, icp.defmaxsize, icp.defextpct, icp.deflists, icp.defgroups, decode(icp.deflogging, 0, 'NONE', 1, 'YES', 2, 'NO', 'UNKNOWN'), decode(bitand(icp.flags, 1024), 0, 'DISABLED', 1024, 'ENABLED', null), icp.blevel, icp.leafcnt, icp.distkey, icp.lblkkey, icp.dblkkey, icp.clufac, icp.rowcnt, icp.samplesize, icp.analyzetime, decode(bitand(icp.defbufpool, 3), 1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'), decode(bitand(icp.defbufpool, 12)/4, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(icp.defbufpool, 48)/16, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(icp.flags, 8), 0, 'NO', 'YES'), TO_NUMBER(NULL), decode(bitand(icp.flags, 16), 0, 'NO', 'YES'), '','', decode(bitand(icp.flags, 32768), 32768, 'YES', 'NO'), 'N/A' from obj$ io, indcompart$ icp, ts$ ts, ind$ i, tab$ t where io.obj# = icp.obj# and icp.defts# = ts.ts# (+) and io.owner# = userenv('SCHEMAID') and i.type# != 9 and icp.bo# = i.obj# and i.bo# = t.obj# and bitand(t.trigflag, 1073741824) != 1073741824 and io.namespace = 4 and io.remoteowner IS NULL and io.linkname IS NULL union all select io.name, 'NO', io.subname, 0, ip.hiboundval, ip.hiboundlen, row_number() over (partition by io.name order by ip.part#), decode(bitand(ip.flags, 1), 1, 'UNUSABLE', decode(bitand(ip.flags, 4096), 4096, 'INPROGRS', 'USABLE')), null, ip.pctfree$, ip.initrans, ip.maxtrans, 0, 0, 0, 0, 0, 0, 0,0, 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, 'DEFAULT', 'DEFAULT', 'DEFAULT', decode(bitand(ip.flags, 8), 0, 'NO', 'YES'), ip.pctthres$, decode(bitand(ip.flags, 16), 0, 'NO', 'YES'), decode(i.type#, 9, decode(bitand(ip.flags, 8192), 8192, 'FAILED', 'VALID'), ''), ipp.parameters, decode(bitand(ip.flags, 32768), 32768, 'YES', 'NO'), decode(bitand(ip.flags, 65536), 65536, 'NO', 'YES') from obj$ io, indpart$ ip, indpart_param$ ipp, ind$ i, tab$ t where io.obj# = ip.obj# and ip.obj# = ipp.obj# and ip.bo# = i.obj# and io.owner# = userenv('SCHEMAID') and io.namespace = 4 and io.remoteowner IS NULL and io.linkname IS NULL and i.bo# = t.obj# and bitand(t.trigflag, 1073741824) != 1073741824 / create or replace 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, MAX_SIZE, 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, FLASH_CACHE, CELL_FLASH_CACHE, USER_STATS, PCT_DIRECT_ACCESS, GLOBAL_STATS, DOMIDX_OPSTATUS, PARAMETERS, INTERVAL, SEGMENT_CREATED) as select u.name, io.name, 'NO', io.subname, 0, ip.hiboundval, ip.hiboundlen, row_number() over (partition by u.name, io.name order by ip.part#), decode(bitand(ip.flags, 1), 1, 'UNUSABLE', 'USABLE'),ts.name, ip.pctfree$, ip.initrans, ip.maxtrans, decode(bitand(ip.flags, 65536), 65536, ds.initial_stg * ts.blocksize, s.iniexts * ts.blocksize), decode(bitand(ip.flags, 65536), 65536, ds.next_stg * ts.blocksize, s.extsize * ts.blocksize), decode(bitand(ip.flags, 65536), 65536, ds.minext_stg, s.minexts), decode(bitand(ip.flags, 65536), 65536, ds.maxext_stg, s.maxexts), decode(bitand(ip.flags, 65536), 65536, ds.maxsiz_stg * ts.blocksize, decode(bitand(s.spare1, 4194304), 4194304, bitmapranges, NULL)), decode(bitand(ts.flags, 3), 1, to_number(NULL), decode(bitand(ip.flags, 65536), 65536, ds.pctinc_stg, s.extpct)), decode(bitand(ts.flags, 32), 32, to_number(NULL), decode(bitand(ip.flags, 65536), 65536, decode(ds.frlins_stg, 0, 1, ds.frlins_stg), decode(s.lists, 0, 1, s.lists))), decode(bitand(ts.flags, 32), 32, to_number(NULL), decode(bitand(ip.flags, 65536), 65536, decode(ds.maxins_stg, 0, 1, ds.maxins_stg), 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(bitand(decode(bitand(ip.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 3), 1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'), decode(bitand(decode(bitand(ip.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 12)/4, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(decode(bitand(ip.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 48)/16, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(ip.flags, 8), 0, 'NO', 'YES'), ip.pctthres$, decode(bitand(ip.flags, 16), 0, 'NO', 'YES'), '','', decode(bitand(ip.flags, 32768), 32768, 'YES', 'NO'), decode(bitand(ip.flags, 65536), 65536, 'NO', 'YES') from obj$ io, indpart$ ip, ts$ ts, sys.seg$ s, ind$ i, sys.user$ u, tab$ t, sys.deferred_stg$ ds 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 i.bo# = t.obj# and ip.obj# = ds.obj#(+) and bitand(t.trigflag, 1073741824) != 1073741824 and i.type# != 8 and /* not LOB index */ i.type# != 9 and /* not DOMAIN index */ io.namespace = 4 and io.remoteowner IS NULL and io.linkname IS NULL 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, 'YES', io.subname, icp.subpartcnt, icp.hiboundval, icp.hiboundlen, row_number() over (partition by u.name, io.name order by icp.part#) , 'N/A', ts.name, icp.defpctfree, icp.definitrans, icp.defmaxtrans, icp.definiexts, icp.defextsize, icp.defminexts, icp.defmaxexts, icp.defmaxsize, icp.defextpct, icp.deflists, icp.defgroups, decode(icp.deflogging, 0, 'NONE', 1, 'YES', 2, 'NO', 'UNKNOWN'), decode(bitand(icp.flags, 1024), 0, 'DISABLED', 1024, 'ENABLED', null), icp.blevel, icp.leafcnt, icp.distkey, icp.lblkkey, icp.dblkkey, icp.clufac, icp.rowcnt, icp.samplesize, icp.analyzetime, decode(bitand(icp.defbufpool, 3), 1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'), decode(bitand(icp.defbufpool, 12)/4, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(icp.defbufpool, 48)/16, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(icp.flags, 8), 0, 'NO', 'YES'), TO_NUMBER(NULL), decode(bitand(icp.flags, 16), 0, 'NO', 'YES'), '','', decode(bitand(icp.flags, 32768), 32768, 'YES', 'NO'), 'N/A' from obj$ io, indcompart$ icp, ts$ ts, ind$ i, user$ u, tab$ t where io.obj# = icp.obj# and icp.defts# = ts.ts# (+) and io.owner# = u.user# and i.obj# = icp.bo# and i.bo# = t.obj# and bitand(t.trigflag, 1073741824) != 1073741824 and i.type# != 8 and /* not LOB index */ i.type# != 9 and /* not DOMAIN index */ io.namespace = 4 and io.remoteowner IS NULL and io.linkname IS NULL 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 */) ) ) union all select u.name, io.name, 'NO', io.subname, 0, ip.hiboundval, ip.hiboundlen, row_number() over (partition by u.name, io.name order by ip.part#), decode(bitand(ip.flags, 1), 1, 'UNUSABLE', decode(bitand(ip.flags, 4096), 4096, 'INPROGRS', 'USABLE')), null, ip.pctfree$, ip.initrans, ip.maxtrans, 0, 0, 0, 0, 0, 0, 0, 0, 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, 'DEFAULT', 'DEFAULT', 'DEFAULT', decode(bitand(ip.flags, 8), 0, 'NO', 'YES'), ip.pctthres$, decode(bitand(ip.flags, 16), 0, 'NO', 'YES'), decode(i.type#, 9, decode(bitand(ip.flags, 8192), 8192, 'FAILED', 'VALID'), ''), ipp.parameters, decode(bitand(ip.flags, 32768), 32768, 'YES', 'NO'), decode(bitand(ip.flags, 65536), 65536, 'NO', 'YES') from obj$ io, indpart$ ip, ind$ i, sys.user$ u, indpart_param$ ipp, tab$ t where io.obj# = ip.obj# and io.owner# = u.user# and i.obj# = ip.bo# and ip.obj# = ipp.obj# and i.bo# = t.obj# and bitand(t.trigflag, 1073741824) != 1073741824 and io.namespace = 4 and io.remoteowner IS NULL and io.linkname IS NULL 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 */) ) ) / create or replace 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, MAX_SIZE, 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, FLASH_CACHE, CELL_FLASH_CACHE, USER_STATS, PCT_DIRECT_ACCESS, GLOBAL_STATS, DOMIDX_OPSTATUS, PARAMETERS, INTERVAL, SEGMENT_CREATED) as select u.name, io.name, 'NO', io.subname, 0, ip.hiboundval, ip.hiboundlen, row_number() over (partition by u.name, io.name order by ip.part#), decode(bitand(ip.flags, 1), 1, 'UNUSABLE', 'USABLE'), ts.name, ip.pctfree$,ip.initrans, ip.maxtrans, decode(bitand(ip.flags, 65536), 65536, ds.initial_stg * ts.blocksize, s.iniexts * ts.blocksize), decode(bitand(ip.flags, 65536), 65536, ds.next_stg * ts.blocksize, s.extsize * ts.blocksize), decode(bitand(ip.flags, 65536), 65536, ds.minext_stg, s.minexts), decode(bitand(ip.flags, 65536), 65536, ds.maxext_stg, s.maxexts), decode(bitand(ip.flags, 65536), 65536, ds.maxsiz_stg * ts.blocksize, decode(bitand(s.spare1, 4194304), 4194304, bitmapranges, NULL)), decode(bitand(ts.flags, 3), 1, to_number(NULL), decode(bitand(ip.flags, 65536), 65536, ds.pctinc_stg, s.extpct)), decode(bitand(ts.flags, 32), 32, to_number(NULL), decode(bitand(ip.flags, 65536), 65536, decode(ds.frlins_stg, 0, 1, ds.frlins_stg), decode(s.lists, 0, 1, s.lists))), decode(bitand(ts.flags, 32), 32, to_number(NULL), decode(bitand(ip.flags, 65536), 65536, decode(ds.maxins_stg, 0, 1, ds.maxins_stg), 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(bitand(decode(bitand(ip.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 3), 1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'), decode(bitand(decode(bitand(ip.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 12)/4, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(decode(bitand(ip.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 48)/16, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(ip.flags, 8), 0, 'NO', 'YES'), ip.pctthres$, decode(bitand(ip.flags, 16), 0, 'NO', 'YES'),'','', decode(bitand(ip.flags, 32768), 32768, 'YES', 'NO'), decode(bitand(ip.flags, 65536), 65536, 'NO', 'YES') from obj$ io, indpart$ ip, ts$ ts, sys.seg$ s, user$ u, ind$ i, tab$ t, sys.deferred_stg$ ds 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 i.bo# = t.obj# and ip.obj# = ds.obj#(+) and i.type# != 9 and bitand(t.trigflag, 1073741824) != 1073741824 and io.namespace = 4 and io.remoteowner IS NULL and io.linkname IS NULL union all select u.name, io.name, 'YES', io.subname, icp.subpartcnt, icp.hiboundval, icp.hiboundlen, row_number() over (partition by u.name, io.name order by icp.part#), 'N/A', ts.name, icp.defpctfree, icp.definitrans, icp.defmaxtrans, icp.definiexts, icp.defextsize, icp.defminexts, icp.defmaxexts, icp.defmaxsize, icp.defextpct, icp.deflists, icp.defgroups, decode(icp.deflogging, 0, 'NONE', 1, 'YES', 2, 'NO', 'UNKNOWN'), decode(bitand(icp.flags, 1024), 0, 'DISABLED', 1024, 'ENABLED', null), icp.blevel, icp.leafcnt, icp.distkey, icp.lblkkey, icp.dblkkey, icp.clufac, icp.rowcnt, icp.samplesize, icp.analyzetime, decode(bitand(icp.defbufpool, 3), 1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'), decode(bitand(icp.defbufpool, 12)/4, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(icp.defbufpool, 48)/16, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(icp.flags, 8), 0, 'NO', 'YES'), TO_NUMBER(NULL), decode(bitand(icp.flags, 16), 0, 'NO', 'YES'),'','', decode(bitand(icp.flags, 32768), 32768, 'YES', 'NO'), 'N/A' from obj$ io, indcompart$ icp, ts$ ts, user$ u, ind$ i, tab$ t where io.obj# = icp.obj# and icp.defts# = ts.ts# (+) and u.user# = io.owner# and i.obj# = icp.bo# and i.bo# = t.obj# and i.type# != 9 and bitand(t.trigflag, 1073741824) != 1073741824 and io.namespace = 4 and io.remoteowner IS NULL and io.linkname IS NULL union all select u.name, io.name, 'NO', io.subname, 0, ip.hiboundval, ip.hiboundlen, row_number() over (partition by u.name, io.name order by ip.part#), decode(bitand(ip.flags, 1), 1, 'UNUSABLE', decode(bitand(ip.flags, 4096), 4096, 'INPROGRS', 'USABLE')), null, ip.pctfree$, ip.initrans, ip.maxtrans, 0, 0, 0, 0, 0, 0, 0, 0, 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, 'DEFAULT', 'DEFAULT', 'DEFAULT', decode(bitand(ip.flags, 8), 0, 'NO', 'YES'), ip.pctthres$, decode(bitand(ip.flags, 16), 0, 'NO', 'YES'), decode(i.type#, 9, decode(bitand(ip.flags, 8192), 8192, 'FAILED', 'VALID'), ''), ipp.parameters, decode(bitand(ip.flags, 32768), 32768, 'YES', 'NO'), decode(bitand(ip.flags, 65536), 65536, 'NO', 'YES') from obj$ io, indpart$ ip, user$ u, ind$ i, indpart_param$ ipp, tab$ t where io.obj# = ip.obj# and io.owner# = u.user# and ip.bo# = i.obj# and ip.obj# = ipp.obj# and i.bo# = t.obj# and bitand(t.trigflag, 1073741824) != 1073741824 and io.namespace = 4 and io.remoteowner IS NULL and io.linkname IS NULL / create or replace 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. remark pctused, freelists, freelist groups are null for bitmap segments remark create or replace view USER_TAB_SUBPARTITIONS (TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, HIGH_VALUE, HIGH_VALUE_LENGTH, SUBPARTITION_POSITION, TABLESPACE_NAME, PCT_FREE, PCT_USED, INI_TRANS, MAX_TRANS, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENT, MAX_EXTENT, MAX_SIZE, PCT_INCREASE, FREELISTS, FREELIST_GROUPS, LOGGING, COMPRESSION, COMPRESS_FOR, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN, SAMPLE_SIZE, LAST_ANALYZED, BUFFER_POOL, FLASH_CACHE, CELL_FLASH_CACHE, GLOBAL_STATS, USER_STATS, INTERVAL, SEGMENT_CREATED) as select po.name, po.subname, so.subname, tsp.hiboundval, tsp.hiboundlen, row_number() over (partition by po.name,po.subname order by tsp.subpart#), ts.name, tsp.pctfree$, decode(bitand(ts.flags, 32), 32, to_number(NULL), tsp.pctused$), tsp.initrans, tsp.maxtrans, decode(bitand(tsp.flags, 65536), 65536, ds.initial_stg * ts.blocksize, s.iniexts * ts.blocksize), decode(bitand(tsp.flags, 65536), 65536, ds.next_stg * ts.blocksize, s.extsize * ts.blocksize), decode(bitand(tsp.flags, 65536), 65536, ds.minext_stg, s.minexts), decode(bitand(tsp.flags, 65536), 65536, ds.maxext_stg, s.maxexts), decode(bitand(tsp.flags, 65536), 65536, ds.maxsiz_stg * ts.blocksize, decode(bitand(s.spare1, 4194304), 4194304, bitmapranges, NULL)), decode(bitand(ts.flags, 3), 1, to_number(NULL), decode(bitand(tsp.flags, 65536), 65536, ds.pctinc_stg, s.extpct)), decode(bitand(ts.flags, 32), 32, to_number(NULL), decode(bitand(tsp.flags, 65536), 65536, decode(ds.frlins_stg, 0, 1, ds.frlins_stg), decode(s.lists, 0, 1, s.lists))), decode(bitand(ts.flags, 32), 32, to_number(NULL), decode(bitand(tsp.flags, 65536), 65536, decode(ds.maxins_stg, 0, 1, ds.maxins_stg), decode(s.groups, 0, 1, s.groups))), decode(mod(trunc(tsp.flags / 4), 2), 0, 'YES', 'NO'), case when (bitand(tsp.flags, 65536) = 65536) then decode(bitand(ds.flags_stg, 4), 4, 'ENABLED', 'DISABLED') else decode(bitand(s.spare1, 2048), 2048, 'ENABLED', 'DISABLED') end, case when (bitand(tsp.flags, 65536) = 65536) then decode(bitand(ds.flags_stg, 4), 4, case when bitand(ds.cmpflag_stg, 3) = 1 then 'BASIC' when bitand(ds.cmpflag_stg, 3) = 2 then 'OLTP' else decode(ds.cmplvl_stg, 1, 'QUERY LOW', 2, 'QUERY HIGH', 3, 'ARCHIVE LOW', 'ARCHIVE HIGH') end, null) else decode(bitand(s.spare1, 2048), 0, null, case when bitand(s.spare1, 16777216) = 16777216 then 'OLTP' when bitand(s.spare1, 100663296) = 33554432 -- 0x2000000 then 'QUERY LOW' when bitand(s.spare1, 100663296) = 67108864 -- 0x4000000 then 'QUERY HIGH' when bitand(s.spare1, 100663296) = 100663296 -- 0x2000000+0x4000000 then 'ARCHIVE LOW' when bitand(s.spare1, 134217728) = 134217728 -- 0x8000000 then 'ARCHIVE HIGH' else 'BASIC' end) end, tsp.rowcnt, tsp.blkcnt, tsp.empcnt, tsp.avgspc, tsp.chncnt, tsp.avgrln, tsp.samplesize, tsp.analyzetime, decode(bitand(decode(bitand(tsp.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 3), 1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'), decode(bitand(decode(bitand(tsp.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 12)/4, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(decode(bitand(tsp.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 48)/16, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(tsp.flags, 16), 0, 'NO', 'YES'), decode(bitand(tsp.flags, 8), 0, 'NO', 'YES'), decode(bitand(tsp.flags, 32768), 32768, 'YES', 'NO'), decode(bitand(tsp.flags, 65536), 65536, 'NO', 'YES') from sys.obj$ so, sys.obj$ po, sys.tabcompart$ tcp, sys.tabsubpart$ tsp, sys.tab$ t, sys.ts$ ts, sys.seg$ s, sys.deferred_stg$ ds where so.obj# = tsp.obj# and po.obj# = tsp.pobj# and tcp.obj# = tsp.pobj# and tcp.bo# = t.obj# and bitand(t.trigflag, 1073741824) != 1073741824 and tsp.ts# = ts.ts# and tsp.obj# = ds.obj#(+) and tsp.file# = s.file#(+) and tsp.block# = s.block#(+) and tsp.ts# = s.ts#(+) and po.owner# = userenv('SCHEMAID') and so.owner# = userenv('SCHEMAID') and po.namespace = 1 and po.remoteowner IS NULL and po.linkname IS NULL and so.namespace = 1 and so.remoteowner IS NULL and so.linkname IS NULL / create or replace 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, HIGH_VALUE, HIGH_VALUE_LENGTH, SUBPARTITION_POSITION, TABLESPACE_NAME, PCT_FREE, PCT_USED, INI_TRANS, MAX_TRANS, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENT, MAX_EXTENT, MAX_SIZE, PCT_INCREASE, FREELISTS, FREELIST_GROUPS, LOGGING, COMPRESSION, COMPRESS_FOR, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN, SAMPLE_SIZE, LAST_ANALYZED, BUFFER_POOL, FLASH_CACHE, CELL_FLASH_CACHE, GLOBAL_STATS, USER_STATS, INTERVAL, SEGMENT_CREATED) as select u.name, po.name, po.subname, so.subname, tsp.hiboundval, tsp.hiboundlen, row_number() over (partition by u.name, po.name, po.subname order by tsp.subpart#), ts.name, tsp.pctfree$, decode(bitand(ts.flags, 32), 32, to_number(NULL), tsp.pctused$), tsp.initrans, tsp.maxtrans, decode(bitand(tsp.flags, 65536), 65536, ds.initial_stg * ts.blocksize, s.iniexts * ts.blocksize), decode(bitand(tsp.flags, 65536), 65536, ds.next_stg * ts.blocksize, s.extsize * ts.blocksize), decode(bitand(tsp.flags, 65536), 65536, ds.minext_stg, s.minexts), decode(bitand(tsp.flags, 65536), 65536, ds.maxext_stg, s.maxexts), decode(bitand(tsp.flags, 65536), 65536, ds.maxsiz_stg * ts.blocksize, decode(bitand(s.spare1, 4194304), 4194304, bitmapranges, NULL)), decode(bitand(ts.flags, 3), 1, to_number(NULL), decode(bitand(tsp.flags, 65536), 65536, ds.pctinc_stg, s.extpct)), decode(bitand(ts.flags, 32), 32, to_number(NULL), decode(bitand(tsp.flags, 65536), 65536, decode(ds.frlins_stg, 0, 1, ds.frlins_stg), decode(s.lists, 0, 1, s.lists))), decode(bitand(ts.flags, 32), 32, to_number(NULL), decode(bitand(tsp.flags, 65536), 65536, decode(ds.maxins_stg, 0, 1, ds.maxins_stg), decode(s.groups, 0, 1, s.groups))), decode(mod(trunc(tsp.flags / 4), 2), 0, 'YES', 'NO'), case when (bitand(tsp.flags, 65536) = 65536) then decode(bitand(ds.flags_stg, 4), 4, 'ENABLED', 'DISABLED') else decode(bitand(s.spare1, 2048), 2048, 'ENABLED', 'DISABLED') end, case when (bitand(tsp.flags, 65536) = 65536) then decode(bitand(ds.flags_stg, 4), 4, case when bitand(ds.cmpflag_stg, 3) = 1 then 'BASIC' when bitand(ds.cmpflag_stg, 3) = 2 then 'OLTP' else decode(ds.cmplvl_stg, 1, 'QUERY LOW', 2, 'QUERY HIGH', 3, 'ARCHIVE LOW', 'ARCHIVE HIGH') end, null) else decode(bitand(s.spare1, 2048), 0, null, case when bitand(s.spare1, 16777216) = 16777216 then 'OLTP' when bitand(s.spare1, 100663296) = 33554432 -- 0x2000000 then 'QUERY LOW' when bitand(s.spare1, 100663296) = 67108864 -- 0x4000000 then 'QUERY HIGH' when bitand(s.spare1, 100663296) = 100663296 -- 0x2000000+0x4000000 then 'ARCHIVE LOW' when bitand(s.spare1, 134217728) = 134217728 -- 0x8000000 then 'ARCHIVE HIGH' else 'BASIC' end) end, tsp.rowcnt, tsp.blkcnt, tsp.empcnt, tsp.avgspc, tsp.chncnt, tsp.avgrln, tsp.samplesize, tsp.analyzetime, decode(bitand(decode(bitand(tsp.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 3), 1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'), decode(bitand(decode(bitand(tsp.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 12)/4, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(decode(bitand(tsp.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 48)/16, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(tsp.flags, 16), 0, 'NO', 'YES'), decode(bitand(tsp.flags, 8), 0, 'NO', 'YES'), decode(bitand(tsp.flags, 32768), 32768, 'YES', 'NO'), decode(bitand(tsp.flags, 65536), 65536, 'NO', 'YES') from obj$ po, obj$ so, tabcompart$ tcp, tabsubpart$ tsp, tab$ t, ts$ ts, sys.seg$ s, user$ u, sys.deferred_stg$ ds where so.obj# = tsp.obj# and po.obj# = tcp.obj# and tcp.obj# = tsp.pobj# and tcp.bo# = t.obj# and bitand(t.trigflag, 1073741824) != 1073741824 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 tsp.obj# = ds.obj#(+) and po.namespace = 1 and po.remoteowner IS NULL and po.linkname IS NULL and so.namespace = 1 and so.remoteowner IS NULL and so.linkname IS NULL and ((po.owner# = userenv('SCHEMAID') and so.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 */) ) ) / create or replace 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, HIGH_VALUE, HIGH_VALUE_LENGTH, SUBPARTITION_POSITION, TABLESPACE_NAME, PCT_FREE, PCT_USED, INI_TRANS, MAX_TRANS, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENT, MAX_EXTENT, MAX_SIZE, PCT_INCREASE, FREELISTS, FREELIST_GROUPS, LOGGING, COMPRESSION, COMPRESS_FOR, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN, SAMPLE_SIZE, LAST_ANALYZED, BUFFER_POOL, FLASH_CACHE, CELL_FLASH_CACHE, GLOBAL_STATS, USER_STATS, INTERVAL, SEGMENT_CREATED) as select u.name, po.name, po.subname, so.subname, tsp.hiboundval, tsp.hiboundlen, row_number() over (partition by u.name, po.name,po.subname order by tsp.subpart#), ts.name, tsp.pctfree$, decode(bitand(ts.flags, 32), 32, to_number(NULL), tsp.pctused$), tsp.initrans, tsp.maxtrans, decode(bitand(tsp.flags, 65536), 65536, ds.initial_stg * ts.blocksize, s.iniexts * ts.blocksize), decode(bitand(tsp.flags, 65536), 65536, ds.next_stg * ts.blocksize, s.extsize * ts.blocksize), decode(bitand(tsp.flags, 65536), 65536, ds.minext_stg, s.minexts), decode(bitand(tsp.flags, 65536), 65536, ds.maxext_stg, s.maxexts), decode(bitand(tsp.flags, 65536), 65536, ds.maxsiz_stg * ts.blocksize, decode(bitand(s.spare1, 4194304), 4194304, bitmapranges, NULL)), decode(bitand(ts.flags, 3), 1, to_number(NULL), decode(bitand(tsp.flags, 65536), 65536, ds.pctinc_stg, s.extpct)), decode(bitand(ts.flags, 32), 32, to_number(NULL), decode(bitand(tsp.flags, 65536), 65536, decode(ds.frlins_stg, 0, 1, ds.frlins_stg), decode(s.lists, 0, 1, s.lists))), decode(bitand(ts.flags, 32), 32, to_number(NULL), decode(bitand(tsp.flags, 65536), 65536, decode(ds.maxins_stg, 0, 1, ds.maxins_stg), decode(s.groups, 0, 1, s.groups))), decode(mod(trunc(tsp.flags / 4), 2), 0, 'YES', 'NO'), case when (bitand(tsp.flags, 65536) = 65536) then decode(bitand(ds.flags_stg, 4), 4, 'ENABLED', 'DISABLED') else decode(bitand(s.spare1, 2048), 2048, 'ENABLED', 'DISABLED') end, case when (bitand(tsp.flags, 65536) = 65536) then decode(bitand(ds.flags_stg, 4), 4, case when bitand(ds.cmpflag_stg, 3) = 1 then 'BASIC' when bitand(ds.cmpflag_stg, 3) = 2 then 'OLTP' else decode(ds.cmplvl_stg, 1, 'QUERY LOW', 2, 'QUERY HIGH', 3, 'ARCHIVE LOW', 'ARCHIVE HIGH') end, null) else decode(bitand(s.spare1, 2048), 0, null, case when bitand(s.spare1, 16777216) = 16777216 then 'OLTP' when bitand(s.spare1, 100663296) = 33554432 -- 0x2000000 then 'QUERY LOW' when bitand(s.spare1, 100663296) = 67108864 -- 0x4000000 then 'QUERY HIGH' when bitand(s.spare1, 100663296) = 100663296 -- 0x2000000+0x4000000 then 'ARCHIVE LOW' when bitand(s.spare1, 134217728) = 134217728 -- 0x8000000 then 'ARCHIVE HIGH' else 'BASIC' end) end, tsp.rowcnt, tsp.blkcnt, tsp.empcnt, tsp.avgspc, tsp.chncnt, tsp.avgrln, tsp.samplesize, tsp.analyzetime, decode(bitand(decode(bitand(tsp.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 3), 1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'), decode(bitand(decode(bitand(tsp.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 12)/4, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(decode(bitand(tsp.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 48)/16, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(tsp.flags, 16), 0, 'NO', 'YES'), decode(bitand(tsp.flags, 8), 0, 'NO', 'YES'), decode(bitand(tsp.flags, 32768), 32768, 'YES', 'NO'), decode(bitand(tsp.flags, 65536), 65536, 'NO', 'YES') from sys.obj$ so, sys.obj$ po, tabcompart$ tcp, sys.tabsubpart$ tsp, sys.tab$ t, sys.ts$ ts, sys.seg$ s, sys.user$ u, sys.deferred_stg$ ds where so.obj# = tsp.obj# and po.obj# = tsp.pobj# and tcp.obj# = tsp.pobj# and tcp.bo# = t.obj# and bitand(t.trigflag, 1073741824) != 1073741824 and tsp.ts# = ts.ts# and u.user# = po.owner# and tsp.obj# = ds.obj#(+) and tsp.file# = s.file#(+) and tsp.block# = s.block#(+) and tsp.ts# = s.ts#(+) and po.namespace = 1 and po.remoteowner IS NULL and po.linkname IS NULL and so.namespace = 1 and so.remoteowner IS NULL and so.linkname IS NULL / create or replace 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. remark pctused, freelists, freelist groups are null for bitmap segments remark create or replace view USER_IND_SUBPARTITIONS (INDEX_NAME, PARTITION_NAME, SUBPARTITION_NAME, HIGH_VALUE, HIGH_VALUE_LENGTH, SUBPARTITION_POSITION, STATUS, TABLESPACE_NAME, PCT_FREE, INI_TRANS, MAX_TRANS, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENT, MAX_EXTENT, MAX_SIZE, 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, FLASH_CACHE, CELL_FLASH_CACHE, USER_STATS, GLOBAL_STATS, INTERVAL, SEGMENT_CREATED) as select po.name, po.subname, so.subname, isp.hiboundval, isp.hiboundlen, row_number() over (partition by po.name,po.subname order by isp.subpart#), decode(bitand(isp.flags, 1), 1, 'UNUSABLE', 'USABLE'), ts.name, isp.pctfree$, isp.initrans, isp.maxtrans, decode(bitand(isp.flags, 65536), 65536, ds.initial_stg * ts.blocksize, s.iniexts * ts.blocksize), decode(bitand(isp.flags, 65536), 65536, ds.next_stg * ts.blocksize, s.extsize * ts.blocksize), decode(bitand(isp.flags, 65536), 65536, ds.minext_stg, s.minexts), decode(bitand(isp.flags, 65536), 65536, ds.maxext_stg, s.maxexts), decode(bitand(isp.flags, 65536), 65536, ds.maxsiz_stg * ts.blocksize, decode(bitand(s.spare1, 4194304), 4194304, bitmapranges, NULL)), decode(bitand(ts.flags, 3), 1, to_number(NULL), decode(bitand(isp.flags, 65536), 65536, ds.pctinc_stg, s.extpct)), decode(bitand(ts.flags, 32), 32, to_number(NULL), decode(bitand(isp.flags, 65536), 65536, decode(ds.frlins_stg, 0, 1, ds.frlins_stg), decode(s.lists, 0, 1, s.lists))), decode(bitand(ts.flags, 32), 32, to_number(NULL), decode(bitand(isp.flags, 65536), 65536, decode(ds.maxins_stg, 0, 1, ds.maxins_stg), decode(s.groups, 0, 1, s.groups))), decode(mod(trunc(isp.flags / 4), 2), 0, 'YES', 'NO'), decode(bitand(isp.flags, 1024), 0, 'DISABLED', 1024, 'ENABLED', null), isp.blevel, isp.leafcnt, isp.distkey, isp.lblkkey, isp.dblkkey, isp.clufac, isp.rowcnt, isp.samplesize, isp.analyzetime, decode(bitand(decode(bitand(isp.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 3), 1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'), decode(bitand(decode(bitand(isp.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 12)/4, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(decode(bitand(isp.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 48)/16, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(isp.flags, 8), 0, 'NO', 'YES'), decode(bitand(isp.flags, 16), 0, 'NO', 'YES'), decode(bitand(isp.flags, 32768), 32768, 'YES', 'NO'), decode(bitand(isp.flags, 65536), 65536, 'NO', 'YES') from sys.obj$ so, sys.obj$ po, sys.indsubpart$ isp, sys.indcompart$ icp, sys.ts$ ts, sys.seg$ s, sys.ind$ i, sys.tab$ t, sys.deferred_stg$ ds where so.obj# = isp.obj# and po.obj# = icp.obj# and icp.obj# = isp.pobj# and isp.ts# = ts.ts# and isp.file# = s.file#(+) and isp.block# = s.block#(+) and isp.ts# = s.ts#(+) and isp.obj# = ds.obj#(+) and po.owner# = userenv('SCHEMAID') and so.owner# = userenv('SCHEMAID') and i.obj# = icp.bo# and i.bo# = t.obj# and bitand(t.trigflag, 1073741824) != 1073741824 and po.namespace = 4 and po.remoteowner IS NULL and po.linkname IS NULL and so.namespace = 4 and so.remoteowner IS NULL and so.linkname IS NULL / create or replace 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, HIGH_VALUE, HIGH_VALUE_LENGTH, SUBPARTITION_POSITION, STATUS, TABLESPACE_NAME, PCT_FREE, INI_TRANS, MAX_TRANS, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENT, MAX_EXTENT, MAX_SIZE, 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, FLASH_CACHE, CELL_FLASH_CACHE, USER_STATS, GLOBAL_STATS, INTERVAL, SEGMENT_CREATED) as select u.name, po.name, po.subname, so.subname, isp.hiboundval, isp.hiboundlen, row_number() over (partition by u.name, po.name, po.subname order by isp.subpart#), decode(bitand(isp.flags, 1), 1, 'UNUSABLE', 'USABLE'), ts.name, isp.pctfree$, isp.initrans, isp.maxtrans, decode(bitand(isp.flags, 65536), 65536, ds.initial_stg * ts.blocksize, s.iniexts * ts.blocksize), decode(bitand(isp.flags, 65536), 65536, ds.next_stg * ts.blocksize, s.extsize * ts.blocksize), decode(bitand(isp.flags, 65536), 65536, ds.minext_stg, s.minexts), decode(bitand(isp.flags, 65536), 65536, ds.maxext_stg, s.maxexts), decode(bitand(isp.flags, 65536), 65536, ds.maxsiz_stg * ts.blocksize, decode(bitand(s.spare1, 4194304), 4194304, bitmapranges, NULL)), decode(bitand(ts.flags, 3), 1, to_number(NULL), decode(bitand(isp.flags, 65536), 65536, ds.pctinc_stg, s.extpct)), decode(bitand(ts.flags, 32), 32, to_number(NULL), decode(bitand(isp.flags, 65536), 65536, decode(ds.frlins_stg, 0, 1, ds.frlins_stg), decode(s.lists, 0, 1, s.lists))), decode(bitand(ts.flags, 32), 32, to_number(NULL), decode(bitand(isp.flags, 65536), 65536, decode(ds.maxins_stg, 0, 1, ds.maxins_stg), decode(s.groups, 0, 1, s.groups))), decode(mod(trunc(isp.flags / 4), 2), 0, 'YES', 'NO'), decode(bitand(isp.flags, 1024), 0, 'DISABLED', 1024, 'ENABLED', null), isp.blevel, isp.leafcnt, isp.distkey, isp.lblkkey, isp.dblkkey, isp.clufac, isp.rowcnt, isp.samplesize, isp.analyzetime, decode(bitand(decode(bitand(isp.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 3), 1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'), decode(bitand(decode(bitand(isp.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 12)/4, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(decode(bitand(isp.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 48)/16, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(isp.flags, 8), 0, 'NO', 'YES'), decode(bitand(isp.flags, 16), 0, 'NO', 'YES'), decode(bitand(isp.flags, 32768), 32768, 'YES', 'NO'), decode(bitand(isp.flags, 65536), 65536, 'NO', 'YES') from obj$ so, sys.obj$ po, ind$ i, indcompart$ icp, indsubpart$ isp, ts$ ts, seg$ s, user$ u, tab$ t, sys.deferred_stg$ ds 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 isp.obj# = ds.obj#(+) and u.user# = po.owner# and i.bo# = t.obj# and bitand(t.trigflag, 1073741824) != 1073741824 and i.type# != 8 and /* not LOB index */ po.namespace = 4 and po.remoteowner IS NULL and po.linkname IS NULL and so.namespace = 4 and so.remoteowner IS NULL and so.linkname IS NULL and ((po.owner# = userenv('SCHEMAID') and so.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 */) ) ) / create or replace 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, HIGH_VALUE, HIGH_VALUE_LENGTH, SUBPARTITION_POSITION, STATUS, TABLESPACE_NAME, PCT_FREE, INI_TRANS, MAX_TRANS, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENT, MAX_EXTENT, MAX_SIZE, 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, FLASH_CACHE, CELL_FLASH_CACHE, USER_STATS, GLOBAL_STATS, INTERVAL, SEGMENT_CREATED) as select u.name, po.name, po.subname, so.subname, isp.hiboundval, isp.hiboundlen, row_number() over (partition by u.name, po.name, po.subname order by isp.subpart#), decode(bitand(isp.flags, 1), 1, 'UNUSABLE', 'USABLE'), ts.name, isp.pctfree$, isp.initrans, isp.maxtrans, decode(bitand(isp.flags, 65536), 65536, ds.initial_stg * ts.blocksize, s.iniexts * ts.blocksize), decode(bitand(isp.flags, 65536), 65536, ds.next_stg * ts.blocksize, s.extsize * ts.blocksize), decode(bitand(isp.flags, 65536), 65536, ds.minext_stg, s.minexts), decode(bitand(isp.flags, 65536), 65536, ds.maxext_stg, s.maxexts), decode(bitand(isp.flags, 65536), 65536, ds.maxsiz_stg * ts.blocksize, decode(bitand(s.spare1, 4194304), 4194304, bitmapranges, NULL)), decode(bitand(ts.flags, 3), 1, to_number(NULL), decode(bitand(isp.flags, 65536), 65536, ds.pctinc_stg, s.extpct)), decode(bitand(ts.flags, 32), 32, to_number(NULL), decode(bitand(isp.flags, 65536), 65536, decode(ds.frlins_stg, 0, 1, ds.frlins_stg), decode(s.lists, 0, 1, s.lists))), decode(bitand(ts.flags, 32), 32, to_number(NULL), decode(bitand(isp.flags, 65536), 65536, decode(ds.maxins_stg, 0, 1, ds.maxins_stg), decode(s.groups, 0, 1, s.groups))), decode(mod(trunc(isp.flags / 4), 2), 0, 'YES', 'NO'), decode(bitand(isp.flags, 1024), 0, 'DISABLED', 1024, 'ENABLED', null), isp.blevel, isp.leafcnt, isp.distkey, isp.lblkkey, isp.dblkkey, isp.clufac, isp.rowcnt, isp.samplesize, isp.analyzetime, decode(bitand(decode(bitand(isp.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 3), 1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'), decode(bitand(decode(bitand(isp.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 12)/4, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(decode(bitand(isp.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 48)/16, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(isp.flags, 8), 0, 'NO', 'YES'), decode(bitand(isp.flags, 16), 0, 'NO', 'YES'), decode(bitand(isp.flags, 32768), 32768, 'YES', 'NO'), decode(bitand(isp.flags, 65536), 65536, 'NO', 'YES') from sys.obj$ so, sys.obj$ po, sys.indcompart$ icp, sys.indsubpart$ isp, sys.ts$ ts, sys.seg$ s, sys.user$ u, sys.ind$ i, sys.tab$ t, sys.deferred_stg$ ds where so.obj# = isp.obj# and po.obj# = icp.obj# and icp.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#(+) and isp.obj# = ds.obj#(+) and icp.bo# = i.obj# and i.bo# = t.obj# and bitand(t.trigflag, 1073741824) != 1073741824 and po.namespace = 4 and po.remoteowner IS NULL and po.linkname IS NULL and so.namespace = 4 and so.remoteowner IS NULL and so.linkname IS NULL / create or replace public synonym DBA_IND_SUBPARTITIONS for DBA_IND_SUBPARTITIONS / grant select on DBA_IND_SUBPARTITIONS 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, 'TABLE', 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#(+) and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL and o.subname IS NULL union select o.name, 'INDEX', 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#(+) and o.namespace = 4 and o.remoteowner IS NULL and o.linkname IS NULL and o.subname IS NULL / create or replace 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, 'TABLE', 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.subname IS NULL and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL 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, 'INDEX', 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.subname IS NULL and o.namespace = 4 and o.remoteowner IS NULL and o.linkname IS NULL 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 */) ) ) / create or replace 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, 'TABLE', 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.subname IS NULL and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL union select u.name, o.name, 'INDEX', 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.subname IS NULL and o.namespace = 4 and o.remoteowner IS NULL and o.linkname IS NULL / create or replace 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_MAX_SIZE, DEF_RETENTION, DEF_MINRET, DEF_PCT_INCREASE, DEF_FREELISTS, DEF_FREELIST_GROUPS, DEF_LOGGING, DEF_BUFFER_POOL, DEF_FLASH_CACHE, DEF_CELL_FLASH_CACHE, DEF_ENCRYPT, DEF_COMPRESS, DEF_DEDUPLICATE, DEF_SECUREFILE) 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, 795), 1, 'NO', 2, 'NO', 8, 'CACHEREADS', 16, 'CACHEREADS', 256, 'YES', 512, 'YES', 'YES'), decode(plob.defpro, 0, 'NO', 2048, '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.defmaxsize, NULL, 'DEFAULT', plob.defmaxsize), decode(bitand(plob.defpro, 2048), 2048, decode(plob.defretention, to_number(NULL), 'DEFAULT', 0, 'NONE', 1, 'AUTO', 2, 'MIN', 3, 'MAX', 4, 'DEFAULT', 'INVALID'), decode(bitand(plob.defflags, 32), 32, 'YES', 'NO')), decode(plob.defmintime, NULL, 'DEFAULT', plob.defmintime), 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, 790), 0,'NONE', 4,'YES', 2,'NO', 16, 'NO', 256, 'NO', 512, 'YES', 'UNKNOWN'), decode(bitand(plob.defbufpool, 3), 1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'), decode(bitand(plob.defbufpool, 12)/4, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(plob.defbufpool, 48)/16, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(plob.defflags,4096), 4096, 'YES', decode(bitand(plob.defpro,2048), 2048, 'NO', 'NONE')), decode(bitand(plob.defflags,57344), 8192, 'LOW', 16384, 'MEDIUM', 32768, 'HIGH', decode(bitand(plob.defpro,2048), 2048, 'NO', 'NONE')), decode(bitand(plob.defflags,458752), 65536, 'LOB', 131072, 'OBJECT', 327680, 'LOB VALIDATE', 393216, 'OBJECT VALIDATE', decode(bitand(plob.defpro,2048), 2048, 'NO', 'NONE')), decode(bitand(plob.defpro,2048), 2048, 'YES', 'NO') from sys.obj$ o, sys.col$ c, sys.lob$ l, sys.partlob$ plob, sys.obj$ lo, sys.obj$ io, sys.ts$ ts, sys.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#(+) and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL and o.subname IS NULL and lo.namespace = 8 and lo.remoteowner IS NULL and lo.linkname IS NULL and lo.subname IS NULL and lo.owner# = userenv('SCHEMAID') / create or replace 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_MAX_SIZE, DEF_RETENTION, DEF_MINRET, DEF_PCT_INCREASE, DEF_FREELISTS, DEF_FREELIST_GROUPS, DEF_LOGGING, DEF_BUFFER_POOL, DEF_FLASH_CACHE, DEF_CELL_FLASH_CACHE, DEF_ENCRYPT, DEF_COMPRESS, DEF_DEDUPLICATE, DEF_SECUREFILE) 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, 795), 1, 'NO', 2, 'NO', 8, 'CACHEREADS', 16, 'CACHEREADS', 256, 'YES', 512, 'YES', 'YES'), decode(plob.defpro, 0, 'NO', 2048, '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.defmaxsize, NULL, 'DEFAULT', plob.defmaxsize), decode(bitand(plob.defpro, 2048), 2048, decode(plob.defretention, to_number(NULL), 'DEFAULT', 0, 'NONE', 1, 'AUTO', 2, 'MIN', 3, 'MAX', 4, 'DEFAULT', 'INVALID'), decode(bitand(plob.defflags, 32), 32, 'YES', 'NO')), decode(plob.defmintime, NULL, 'DEFAULT', plob.defmintime), 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, 790), 0,'NONE', 4,'YES', 2,'NO', 16, 'NO', 256, 'NO', 512, 'YES', 'UNKNOWN'), decode(bitand(plob.defbufpool, 3), 1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'), decode(bitand(plob.defbufpool, 12)/4, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(plob.defbufpool, 48)/16, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(plob.defflags,4096), 4096, 'YES', decode(bitand(plob.defpro,2048), 2048, 'NO', 'NONE')), decode(bitand(plob.defflags,57344), 8192, 'LOW', 16384, 'MEDIUM', 32768, 'HIGH', decode(bitand(plob.defpro,2048), 2048, 'NO', 'NONE')), decode(bitand(plob.defflags,458752), 65536, 'LOB', 131072, 'OBJECT', 327680, 'LOB VALIDATE', 393216, 'OBJECT VALIDATE', decode(bitand(plob.defpro,2048), 2048, 'NO', 'NONE')), decode(bitand(plob.defpro,2048), 2048, 'YES', 'NO') 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, sys.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.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL and o.subname IS NULL and lo.subname IS NULL and lo.namespace = 8 and lo.remoteowner IS NULL and lo.linkname IS NULL and ((o.owner# = userenv('SCHEMAID') and lo.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 */) ) ) / create or replace 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_MAX_SIZE, DEF_RETENTION, DEF_MINRET, DEF_PCT_INCREASE, DEF_FREELISTS, DEF_FREELIST_GROUPS, DEF_LOGGING, DEF_BUFFER_POOL, DEF_FLASH_CACHE, DEF_CELL_FLASH_CACHE, DEF_ENCRYPT, DEF_COMPRESS, DEF_DEDUPLICATE, DEF_SECUREFILE) 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, 795), 1, 'NO', 2, 'NO', 8, 'CACHEREADS', 16, 'CACHEREADS', 256, 'YES', 512, 'YES', 'YES'), decode(plob.defpro, 0, 'NO', 2048, '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.defmaxsize, NULL, 'DEFAULT', plob.defmaxsize), decode(bitand(plob.defpro, 2048), 2048, decode(plob.defretention, to_number(NULL), 'DEFAULT', 0, 'NONE', 1, 'AUTO', 2, 'MIN', 3, 'MAX', 4, 'DEFAULT', 'INVALID'), decode(bitand(plob.defflags, 32), 32, 'YES', 'NO')), decode(plob.defmintime, NULL, 'DEFAULT', plob.defmintime), 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, 790), 0,'NONE', 4,'YES', 2,'NO', 16, 'NO', 256, 'NO', 512, 'YES', 'UNKNOWN'), decode(bitand(plob.defbufpool, 3), 1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'), decode(bitand(plob.defbufpool, 12)/4, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(plob.defbufpool, 48)/16, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(plob.defflags,4096), 4096, 'YES', decode(bitand(plob.defpro,2048), 2048, 'NO', 'NONE')), decode(bitand(plob.defflags,57344), 8192, 'LOW', 16384, 'MEDIUM', 32768, 'HIGH', decode(bitand(plob.defpro,2048), 2048, 'NO', 'NONE')), decode(bitand(plob.defflags,458752), 65536, 'LOB', 131072, 'OBJECT', 327680, 'LOB VALIDATE', 393216, 'OBJECT VALIDATE', decode(bitand(plob.defpro,2048), 2048, 'NO', 'NONE')), decode(bitand(plob.defpro,2048), 2048, 'YES', 'NO') 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, sys.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.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL and o.subname IS NULL and lo.subname IS NULL and lo.namespace = 8 and lo.remoteowner IS NULL and lo.linkname IS NULL / create or replace 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, MAX_SIZE, RETENTION, MINRETENTION, PCT_INCREASE, FREELISTS, FREELIST_GROUPS, LOGGING, BUFFER_POOL, FLASH_CACHE, CELL_FLASH_CACHE, ENCRYPT, COMPRESSION, DEDUPLICATION, SECUREFILE, SEGMENT_CREATED) as select o.name, decode(bitand(c.property, 1), 1, a.name, c.name), lo.name, po.subname, lpo.subname, lipo.subname, row_number() over (partition by o.name, lo.name order by lf.frag#), 'NO', lf.chunk * ts.blocksize, decode(bitand(lf.fragflags, 32), 0, lf.pctversion$, to_number(NULL)), decode(bitand(lf.fragflags, 795), 1, 'NO', 2, 'NO', 8, 'CACHEREADS', 16, 'CACHEREADS', 256, 'YES', 512, 'YES', 'YES'), decode(lf.fragpro, 0, 'NO', 2048, 'NO', 'YES'), ts.name, to_char(decode(bitand(lf.fragflags, 33554432), 33554432, ds.initial_stg, s.iniexts) * ts.blocksize), to_char(decode(bitand(lf.fragflags, 33554432), 33554432, ds.next_stg, s.extsize) * ts.blocksize), to_char(decode(bitand(lf.fragflags, 33554432), 33554432, ds.minext_stg, s.minexts)), to_char(decode(bitand(lf.fragflags, 33554432), 33554432, ds.maxext_stg, s.maxexts)), to_char(decode(bitand(lf.fragflags, 33554432), 33554432, ds.maxsiz_stg * ts.blocksize, decode(bitand(s.spare1, 4194304), 4194304, bitmapranges, NULL))), to_char(decode(bitand(lf.fragflags, 33554432), 33554432, decode(bitand(lf.fragpro, 2048), 2048, decode(ds.lobret_stg, to_number(NULL), 'DEFAULT', 0, 'NONE', 1, 'AUTO', 2, 'MIN', 3, 'MAX', 4, 'DEFAULT', 'INVALID'), decode(bitand(lf.fragflags, 32), 32, 'YES', 'NO')), decode(bitand(s.spare1, 2097152), 2097152, decode(s.lists, 0, 'NONE', 1, 'AUTO', 2, 'MIN', 3, 'MAX', 4, 'DEFAULT', 'INVALID'), decode(bitand(lf.fragflags, 32), 32, 'YES', 'NO')))), to_char(decode(bitand(lf.fragflags, 33554432), 33554432, decode(bitand(lf.fragpro, 2048), 2048, ds.mintim_stg, NULL), decode(bitand(s.spare1, 2097152), 2097152, s.groups, NULL))), to_char(decode(bitand(ts.flags, 3), 1, to_number(NULL), decode(bitand(lf.fragflags, 33554432), 33554432, ds.pctinc_stg, s.extpct))), to_char(decode(bitand(lf.fragflags, 33554432), 33554432, decode(ds.frlins_stg, 0, 1, ds.frlins_stg), decode(s.lists, 0, 1, s.lists))), to_char(decode(bitand(lf.fragflags, 33554432), 33554432, decode(ds.maxins_stg, 0, 1, ds.maxins_stg), decode(s.groups, 0, 1, s.groups))), decode(bitand(lf.fragflags, 786), 2, 'NO', 16, 'NO', 256, 'NO', 512, 'YES', 'YES'), decode(bitand(decode(bitand(lf.fragflags, 33554432), 33554432, ds.bfp_stg, s.cachehint), 3), 1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'), decode(bitand(decode(bitand(lf.fragflags, 33554432), 33554432, ds.bfp_stg, s.cachehint), 12)/4, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(decode(bitand(lf.fragflags, 33554432), 33554432, ds.bfp_stg, s.cachehint), 48)/16, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(lf.fragflags,4096), 4096, 'YES', decode(bitand(lf.fragpro,2048), 2048, 'NO', 'NONE')), decode(bitand(lf.fragflags,57344), 8192, 'LOW', 16384, 'MEDIUM', 32768, 'HIGH', decode(bitand(lf.fragpro,2048), 2048, 'NO', 'NONE')), decode(bitand(lf.fragflags,458752), 65536, 'LOB', 131072, 'OBJECT', 327680, 'LOB VALIDATE', 393216, 'OBJECT VALIDATE', decode(bitand(lf.fragpro,2048), 2048, 'NO', 'NONE')), decode(bitand(lf.fragpro, 2048), 2048, 'YES', 'NO'), decode(bitand(lf.fragflags, 33554432), 33554432, 'NO', 'YES') 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.tab$ t, sys.ts$ ts, sys.seg$ s, sys.attrcol$ a, sys.deferred_stg$ ds where o.owner# = userenv('SCHEMAID') and pobj.obj# = o.obj# and o.obj# = t.obj# and bitand(t.trigflag, 1073741824) != 1073741824 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.fragobj# = ds.obj#(+) and lf.ts# = s.ts#(+) 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.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL and lo.namespace = 8 and lo.remoteowner IS NULL and lo.linkname IS NULL and lo.owner# = userenv('SCHEMAID') union all select o.name, decode(bitand(c.property, 1), 1, a.name, c.name), lo.name, po.subname, lpo.subname, lipo.subname, row_number() over (partition by o.name, lo.name order by lcp.part#), 'YES', lcp.defchunk, decode(bitand(lcp.defflags, 32), 0, lcp.defpctver$, to_number(NULL)), decode(bitand(lcp.defflags, 795), 1, 'NO', 2, 'NO', 8, 'CACHEREADS', 16, 'CACHEREADS', 256, 'YES', 512, 'YES', 'YES'), decode(lcp.defpro, 0, 'NO', 2048, '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.defmaxsize, NULL, 'DEFAULT', lcp.defmaxsize), decode(bitand(lcp.defpro, 2048), 2048, decode(lcp.defretention, to_number(NULL), 'DEFAULT', 0, 'NONE', 1, 'AUTO', 2, 'MIN', 3, 'MAX', 4, 'DEFAULT', 'INVALID'), decode(bitand(lcp.defflags, 32), 32, 'YES', 'NO')), decode(lcp.defmintime, NULL, 'DEFAULT', lcp.defmintime), 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, 790), 0,'NONE', 4,'YES', 2,'NO', 16, 'NO', 256, 'NO', 512, 'YES', 'UNKNOWN'), decode(bitand(lcp.defbufpool, 3), 1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'), decode(bitand(lcp.defbufpool, 12)/4, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(lcp.defbufpool, 48)/16, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(lcp.defflags,4096), 4096, 'YES', decode(bitand(lcp.defpro,2048), 2048, 'NO', 'NONE')), decode(bitand(lcp.defflags,57344), 8192, 'LOW', 16384, 'MEDIUM', 32768, 'HIGH', decode(bitand(lcp.defpro,2048), 2048, 'NO', 'NONE')), decode(bitand(lcp.defflags,458752), 65536, 'LOB', 131072, 'OBJECT', 327680, 'LOB VALIDATE', 393216, 'OBJECT VALIDATE', decode(bitand(lcp.defpro,2048), 2048, 'NO', 'NONE')), decode(bitand(lcp.defpro,2048), 2048, 'YES', 'NO'), 'N/A' 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, sys.partobj$ pobj, sys.tab$ t, sys.attrcol$ a where o.owner# = userenv('SCHEMAID') and pobj.obj# = o.obj# and o.obj# = t.obj# and bitand(t.trigflag, 1073741824) != 1073741824 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.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL and lo.namespace = 8 and lo.remoteowner IS NULL and lo.linkname IS NULL and lo.owner# = userenv('SCHEMAID') / create or replace 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, MAX_SIZE, RETENTION, MINRETENTION, PCT_INCREASE, FREELISTS, FREELIST_GROUPS, LOGGING, BUFFER_POOL, FLASH_CACHE, CELL_FLASH_CACHE, ENCRYPT, COMPRESSION, DEDUPLICATION, SECUREFILE, SEGMENT_CREATED) as select u.name, o.name, decode(bitand(c.property, 1), 1, a.name, c.name), lo.name, po.subname, lpo.subname, lipo.subname, row_number() over (partition by u.name, o.name,lo.name order by lf.frag#), 'NO', lf.chunk * ts.blocksize, decode(bitand(lf.fragflags, 32), 0, lf.pctversion$, to_number(NULL)), decode(bitand(lf.fragflags, 795), 1, 'NO', 2, 'NO', 8, 'CACHEREADS', 16, 'CACHEREADS', 256, 'YES', 512, 'YES', 'YES'), decode(lf.fragpro, 0, 'NO', 2048, 'NO', 'YES'), ts.name, to_char(decode(bitand(lf.fragflags, 33554432), 33554432, ds.initial_stg, s.iniexts) * ts.blocksize), to_char(decode(bitand(lf.fragflags, 33554432), 33554432, ds.next_stg, s.extsize) * ts.blocksize), to_char(decode(bitand(lf.fragflags, 33554432), 33554432, ds.minext_stg, s.minexts)), to_char(decode(bitand(lf.fragflags, 33554432), 33554432, ds.maxext_stg, s.maxexts)), to_char(decode(bitand(lf.fragflags, 33554432), 33554432, ds.maxsiz_stg * ts.blocksize, decode(bitand(s.spare1, 4194304), 4194304, bitmapranges, NULL))), to_char(decode(bitand(lf.fragflags, 33554432), 33554432, decode(bitand(lf.fragpro, 2048), 2048, decode(ds.lobret_stg, to_number(NULL), 'DEFAULT', 0, 'NONE', 1, 'AUTO', 2, 'MIN', 3, 'MAX', 4, 'DEFAULT', 'INVALID'), decode(bitand(lf.fragflags, 32), 32, 'YES', 'NO')), decode(bitand(s.spare1, 2097152), 2097152, decode(s.lists, 0, 'NONE', 1, 'AUTO', 2, 'MIN', 3, 'MAX', 4, 'DEFAULT', 'INVALID'), decode(bitand(lf.fragflags, 32), 32, 'YES', 'NO')))), to_char(decode(bitand(lf.fragflags, 33554432), 33554432, decode(bitand(lf.fragpro, 2048), 2048, ds.mintim_stg, NULL), decode(bitand(s.spare1, 2097152), 2097152, s.groups, NULL))), to_char(decode(bitand(ts.flags, 3), 1, to_number(NULL), decode(bitand(lf.fragflags, 33554432), 33554432, ds.pctinc_stg, s.extpct))), to_char(decode(bitand(lf.fragflags, 33554432), 33554432, decode(ds.frlins_stg, 0, 1, ds.frlins_stg), decode(s.lists, 0, 1, s.lists))), to_char(decode(bitand(lf.fragflags, 33554432), 33554432, decode(ds.maxins_stg, 0, 1, ds.maxins_stg), decode(s.groups, 0, 1, s.groups))), decode(bitand(lf.fragflags, 786), 2,'NO', 16, 'NO', 256, 'NO', 512, 'YES', 'YES'), decode(bitand(decode(bitand(lf.fragflags, 33554432), 33554432, ds.bfp_stg, s.cachehint), 3), 1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'), decode(bitand(decode(bitand(lf.fragflags, 33554432), 33554432, ds.bfp_stg, s.cachehint), 12)/4, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(decode(bitand(lf.fragflags, 33554432), 33554432, ds.bfp_stg, s.cachehint), 48)/16, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(lf.fragflags,4096), 4096, 'YES', decode(bitand(lf.fragpro,2048), 2048, 'NO', 'NONE')), decode(bitand(lf.fragflags,57344), 8192, 'LOW', 16384, 'MEDIUM', 32768, 'HIGH', decode(bitand(lf.fragpro,2048), 2048, 'NO', 'NONE')), decode(bitand(lf.fragflags,458752), 65536, 'LOB', 131072, 'OBJECT', 327680, 'LOB VALIDATE', 393216, 'OBJECT VALIDATE', decode(bitand(lf.fragpro,2048), 2048, 'NO', 'NONE')), decode(bitand(lf.fragpro,2048), 2048, 'YES', 'NO'), decode(bitand(lf.fragflags, 33554432), 33554432, 'NO', 'YES') 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.tab$ t, sys.ts$ ts, sys.seg$ s, sys.user$ u, sys.attrcol$ a, sys.deferred_stg$ ds where o.owner# = u.user# and pobj.obj# = o.obj# and o.obj# = t.obj# and bitand(t.trigflag, 1073741824) != 1073741824 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.fragobj# = ds.obj#(+) and lf.ts# = s.ts#(+) 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.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL and lo.namespace = 8 and lo.remoteowner IS NULL and lo.linkname IS NULL and ((o.owner# = userenv('SCHEMAID') and lo.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, row_number() over (partition by u.name, o.name,lo.name order by lcp.part#), 'YES', lcp.defchunk, decode(bitand(lcp.defflags, 32), 0, lcp.defpctver$, to_number(NULL)), decode(bitand(lcp.defflags, 795), 1, 'NO', 2, 'NO', 8, 'CACHEREADS', 16, 'CACHEREADS', 256, 'YES', 512, 'YES', 'YES'), decode(lcp.defpro, 0, 'NO', 2048, '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.defmaxsize, NULL, 'DEFAULT', lcp.defmaxsize), decode(bitand(lcp.defpro, 2048), 2048, decode(lcp.defretention, to_number(NULL), 'DEFAULT', 0, 'NONE', 1, 'AUTO', 2, 'MIN', 3, 'MAX', 4, 'DEFAULT', 'INVALID'), decode(bitand(lcp.defflags, 32), 32, 'YES', 'NO')), decode(lcp.defmintime, NULL, 'DEFAULT', lcp.defmintime), 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, 790), 0,'NONE', 4,'YES', 2,'NO', 16, 'NO', 256, 'NO', 512, 'YES', 'UNKNOWN'), decode(bitand(lcp.defbufpool, 3), 1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'), decode(bitand(lcp.defbufpool, 12)/4, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(lcp.defbufpool, 48)/16, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(lcp.defflags,4096), 4096, 'YES', decode(bitand(lcp.defpro,2048), 2048, 'NO', 'NONE')), decode(bitand(lcp.defflags,57344), 8192, 'LOW', 16384, 'MEDIUM', 32768, 'HIGH', decode(bitand(lcp.defpro,2048), 2048, 'NO', 'NONE')), decode(bitand(lcp.defflags,458752), 65536, 'LOB', 131072, 'OBJECT', 327680, 'LOB VALIDATE', 393216, 'OBJECT VALIDATE', decode(bitand(lcp.defpro,2048), 2048, 'NO', 'NONE')), decode(bitand(lcp.defpro,2048), 2048, 'YES', 'NO'), 'N/A' 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.tab$ t, sys.user$ u, sys.attrcol$ a where o.owner# = u.user# and pobj.obj# = o.obj# and o.obj# = t.obj# and bitand(t.trigflag, 1073741824) != 1073741824 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.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL and lo.namespace = 8 and lo.remoteowner IS NULL and lo.linkname IS NULL and ((o.owner# = userenv('SCHEMAID') and lo.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 */) ) ) / create or replace 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, MAX_SIZE, RETENTION, MINRETENTION, PCT_INCREASE, FREELISTS, FREELIST_GROUPS, LOGGING, BUFFER_POOL, FLASH_CACHE, CELL_FLASH_CACHE, ENCRYPT, COMPRESSION, DEDUPLICATION, SECUREFILE, SEGMENT_CREATED) as select u.name, o.name, decode(bitand(c.property, 1), 1, a.name, c.name), lo.name, po.subname, lpo.subname, lipo.subname, row_number() over (partition by u.name,o.name, lo.name order by lf.frag#), 'NO', lf.chunk * ts.blocksize, decode(bitand(lf.fragflags, 32), 0, lf.pctversion$, to_number(NULL)), decode(bitand(lf.fragflags, 795), 1, 'NO', 2, 'NO', 8, 'CACHEREADS', 16, 'CACHEREADS', 256, 'YES', 512, 'YES', 'YES'), decode(lf.fragpro, 0, 'NO', 2048, 'NO', 'YES'), ts.name, to_char(decode(bitand(lf.fragflags, 33554432), 33554432, ds.initial_stg, s.iniexts) * ts.blocksize), to_char(decode(bitand(lf.fragflags, 33554432), 33554432, ds.next_stg, s.extsize) * ts.blocksize), to_char(decode(bitand(lf.fragflags, 33554432), 33554432, ds.minext_stg, s.minexts)), to_char(decode(bitand(lf.fragflags, 33554432), 33554432, ds.maxext_stg, s.maxexts)), to_char(decode(bitand(lf.fragflags, 33554432), 33554432, ds.maxsiz_stg * ts.blocksize, decode(bitand(s.spare1, 4194304), 4194304, bitmapranges, NULL))), to_char(decode(bitand(lf.fragflags, 33554432), 33554432, decode(bitand(lf.fragpro, 2048), 2048, decode(ds.lobret_stg, to_number(NULL), 'DEFAULT', 0, 'NONE', 1, 'AUTO', 2, 'MIN', 3, 'MAX', 4, 'DEFAULT', 'INVALID'), decode(bitand(lf.fragflags, 32), 32, 'YES', 'NO')), decode(bitand(s.spare1, 2097152), 2097152, decode(s.lists, 0, 'NONE', 1, 'AUTO', 2, 'MIN', 3, 'MAX', 4, 'DEFAULT', 'INVALID'), decode(bitand(lf.fragflags, 32), 32, 'YES', 'NO')))), to_char(decode(bitand(lf.fragflags, 33554432), 33554432, decode(bitand(lf.fragpro, 2048), 2048, ds.mintim_stg, NULL), decode(bitand(s.spare1, 2097152), 2097152, s.groups, NULL))), to_char(decode(bitand(ts.flags, 3), 1, to_number(NULL), decode(bitand(lf.fragflags, 33554432), 33554432, ds.pctinc_stg, s.extpct))), to_char(decode(bitand(lf.fragflags, 33554432), 33554432, decode(ds.frlins_stg, 0, 1, ds.frlins_stg), decode(s.lists, 0, 1, s.lists))), to_char(decode(bitand(lf.fragflags, 33554432), 33554432, decode(ds.maxins_stg, 0, 1, ds.maxins_stg), decode(s.groups, 0, 1, s.groups))), decode(bitand(lf.fragflags, 786), 2, 'NO', 16, 'NO', 256, 'NO', 512, 'YES', 'YES'), decode(bitand(decode(bitand(lf.fragflags, 33554432), 33554432, ds.bfp_stg, s.cachehint), 3), 1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'), decode(bitand(decode(bitand(lf.fragflags, 33554432), 33554432, ds.bfp_stg, s.cachehint), 12)/4, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(decode(bitand(lf.fragflags, 33554432), 33554432, ds.bfp_stg, s.cachehint), 48)/16, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(lf.fragflags,4096), 4096, 'YES', decode(bitand(lf.fragpro,2048), 2048, 'NO', 'NONE')), decode(bitand(lf.fragflags,57344), 8192, 'LOW', 16384, 'MEDIUM', 32768, 'HIGH', decode(bitand(lf.fragpro,2048), 2048, 'NO', 'NONE')), decode(bitand(lf.fragflags,458752), 65536, 'LOB', 131072, 'OBJECT', 327680, 'LOB VALIDATE', 393216, 'OBJECT VALIDATE', decode(bitand(lf.fragpro,2048), 2048, 'NO', 'NONE')), decode(bitand(lf.fragpro,2048), 2048, 'YES', 'NO'), decode(bitand(lf.fragflags, 33554432), 33554432, 'NO', 'YES') 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.tab$ t, sys.ts$ ts, sys.seg$ s, sys.user$ u, sys.attrcol$ a, sys.deferred_stg$ ds where o.owner# = u.user# and pobj.obj# = o.obj# and o.obj# = t.obj# and bitand(t.trigflag, 1073741824) != 1073741824 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.fragobj# = ds.obj#(+) and lf.ts# = s.ts#(+) 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.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL and lo.namespace = 8 and lo.remoteowner IS NULL and lo.linkname IS NULL 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, row_number() over (partition by u.name, o.name,lo.name order by lcp.part#), 'YES', lcp.defchunk, decode(bitand(lcp.defflags, 32), 0, lcp.defpctver$, to_number(NULL)), decode(bitand(lcp.defflags, 795), 1, 'NO', 2, 'NO', 8, 'CACHEREADS', 16, 'CACHEREADS', 256, 'YES', 512, 'YES', 'YES'), decode(lcp.defpro, 0, 'NO', 2048, '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.defmaxsize, NULL, 'DEFAULT', lcp.defmaxsize), decode(bitand(lcp.defpro, 2048), 2048, decode(lcp.defretention, to_number(NULL), 'DEFAULT', 0, 'NONE', 1, 'AUTO', 2, 'MIN', 3, 'MAX', 4, 'DEFAULT', 'INVALID'), decode(bitand(lcp.defflags, 32), 32, 'YES', 'NO')), decode(lcp.defmintime, NULL, 'DEFAULT', lcp.defmintime), 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, 790), 0,'NONE', 4,'YES', 2,'NO', 16, 'NO', 256, 'NO', 512, 'YES', 'UNKNOWN'), decode(bitand(lcp.defbufpool, 3), 1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'), decode(bitand(lcp.defbufpool, 12)/4, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(lcp.defbufpool, 48)/16, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(lcp.defflags,4096), 4096, 'YES', decode(bitand(lcp.defpro,2048), 2048, 'NO', 'NONE')), decode(bitand(lcp.defflags,57344), 8192, 'LOW', 16384, 'MEDIUM', 32768, 'HIGH', decode(bitand(lcp.defpro,2048), 2048, 'NO', 'NONE')), decode(bitand(lcp.defflags,458752), 65536, 'LOB', 131072, 'OBJECT', 327680, 'LOB VALIDATE', 393216, 'OBJECT VALIDATE', decode(bitand(lcp.defpro,2048), 2048, 'NO', 'NONE')), decode(bitand(lcp.defpro,2048), 2048, 'YES', 'NO'), 'N/A' 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, sys.partobj$ pobj, sys.tab$ t, sys.user$ u, attrcol$ a where o.owner# = u.user# and pobj.obj# = o.obj# and o.obj# = t.obj# and bitand(t.trigflag, 1073741824) != 1073741824 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.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL and lo.namespace = 8 and lo.remoteowner IS NULL and lo.linkname IS NULL / create or replace 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, MAX_SIZE, RETENTION, MINRETENTION, PCT_INCREASE, FREELISTS, FREELIST_GROUPS, LOGGING, BUFFER_POOL, FLASH_CACHE, CELL_FLASH_CACHE, ENCRYPT, COMPRESSION, DEDUPLICATION, SECUREFILE, SEGMENT_CREATED) as select o.name, decode(bitand(c.property, 1), 1, a.name, c.name), lo.name, lpo.subname, spo.subname, lspo.subname, lispo.subname, row_number() over (partition by o.name, lo.name, lpo.subname order by lf.frag#), lf.chunk * ts.blocksize, decode(bitand(lf.fragflags, 32), 0, lf.pctversion$, to_number(NULL)), decode(bitand(lf.fragflags, 795), 1, 'NO', 2, 'NO', 8, 'CACHEREADS', 16, 'CACHEREADS', 256, 'YES', 512, 'YES', 'YES'), decode(lf.fragpro, 0, 'NO', 2048, 'NO', 'YES'), ts.name, to_char(decode(bitand(lf.fragflags, 33554432), 33554432, ds.initial_stg, s.iniexts) * ts.blocksize), to_char(decode(bitand(lf.fragflags, 33554432), 33554432, ds.next_stg, s.extsize) * ts.blocksize), to_char(decode(bitand(lf.fragflags, 33554432), 33554432, ds.minext_stg, s.minexts)), to_char(decode(bitand(lf.fragflags, 33554432), 33554432, ds.maxext_stg, s.maxexts)), to_char(decode(bitand(lf.fragflags, 33554432), 33554432, ds.maxsiz_stg * ts.blocksize, decode(bitand(s.spare1, 4194304), 4194304, bitmapranges, NULL))), to_char(decode(bitand(lf.fragflags, 33554432), 33554432, decode(bitand(lf.fragpro, 2048), 2048, decode(ds.lobret_stg, to_number(NULL), 'DEFAULT', 0, 'NONE', 1, 'AUTO', 2, 'MIN', 3, 'MAX', 4, 'DEFAULT', 'INVALID'), decode(bitand(lf.fragflags, 32), 32, 'YES', 'NO')), decode(bitand(s.spare1, 2097152), 2097152, decode(s.lists, 0, 'NONE', 1, 'AUTO', 2, 'MIN', 3, 'MAX', 4, 'DEFAULT', 'INVALID'), decode(bitand(lf.fragflags, 32), 32, 'YES','NO')))), to_char(decode(bitand(lf.fragflags, 33554432), 33554432, decode(bitand(lf.fragpro, 2048), 2048, ds.mintim_stg, NULL), decode(bitand(s.spare1, 2097152), 2097152, s.groups, NULL))), to_char(decode(bitand(ts.flags, 3), 1, to_number(NULL), decode(bitand(lf.fragflags, 33554432), 33554432, ds.pctinc_stg, s.extpct))), to_char(decode(bitand(lf.fragflags, 33554432), 33554432, decode(ds.frlins_stg, 0, 1, ds.frlins_stg), decode(s.lists, 0, 1, s.lists))), to_char(decode(bitand(lf.fragflags, 33554432), 33554432, decode(ds.maxins_stg, 0, 1, ds.maxins_stg), decode(s.groups, 0, 1, s.groups))), decode(bitand(lf.fragflags, 786), 2, 'NO', 16, 'NO', 256, 'NO', 512, 'YES', 'YES'), decode(bitand(decode(bitand(lf.fragflags, 33554432), 33554432, ds.bfp_stg, s.cachehint), 3), 1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'), decode(bitand(decode(bitand(lf.fragflags, 33554432), 33554432, ds.bfp_stg, s.cachehint), 12)/4, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(decode(bitand(lf.fragflags, 33554432), 33554432, ds.bfp_stg, s.cachehint), 48)/16, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(lf.fragflags,4096), 4096, 'YES', decode(bitand(lf.fragpro,2048), 2048, 'NO', 'NONE')), decode(bitand(lf.fragflags,57344), 8192, 'LOW', 16384, 'MEDIUM', 32768, 'HIGH', decode(bitand(lf.fragpro,2048), 2048, 'NO', 'NONE')), decode(bitand(lf.fragflags,458752), 65536, 'LOB', 131072, 'OBJECT', 327680, 'LOB VALIDATE', 393216, 'OBJECT VALIDATE', decode(bitand(lf.fragpro,2048), 2048, 'NO', 'NONE')), decode(bitand(lf.fragpro,2048), 2048, 'YES', 'NO'), decode(bitand(lf.fragflags, 33554432), 33554432, 'NO', 'YES') 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.tab$ t, sys.ts$ ts, sys.seg$ s, sys.attrcol$ a, sys.deferred_stg$ ds where o.owner# = userenv('SCHEMAID') and pobj.obj# = o.obj# and o.obj# = t.obj# and bitand(t.trigflag, 1073741824) != 1073741824 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.fragobj# = ds.obj#(+) and lf.ts# = s.ts#(+) 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.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL and lo.namespace = 8 and lo.remoteowner IS NULL and lo.linkname IS NULL / create or replace 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, MAX_SIZE, RETENTION, MINRETENTION, PCT_INCREASE, FREELISTS, FREELIST_GROUPS, LOGGING, BUFFER_POOL, FLASH_CACHE, CELL_FLASH_CACHE, ENCRYPT, COMPRESSION, DEDUPLICATION, SECUREFILE, SEGMENT_CREATED) 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, row_number() over (partition by u.name, o.name, lo.name, lpo.subname order by lf.frag#), lf.chunk * ts.blocksize, decode(bitand(lf.fragflags, 32), 0, lf.pctversion$, to_number(NULL)), decode(bitand(lf.fragflags, 795), 1, 'NO', 2, 'NO', 8, 'CACHEREADS', 16, 'CACHEREADS', 256, 'YES', 512, 'YES', 'YES'), decode(lf.fragpro, 0, 'NO', 2048, 'NO', 'YES'), ts.name, to_char(decode(bitand(lf.fragflags, 33554432), 33554432, ds.initial_stg, s.iniexts) * ts.blocksize), to_char(decode(bitand(lf.fragflags, 33554432), 33554432, ds.next_stg, s.extsize) * ts.blocksize), to_char(decode(bitand(lf.fragflags, 33554432), 33554432, ds.minext_stg, s.minexts)), to_char(decode(bitand(lf.fragflags, 33554432), 33554432, ds.maxext_stg, s.maxexts)), to_char(decode(bitand(lf.fragflags, 33554432), 33554432, ds.maxsiz_stg * ts.blocksize, decode(bitand(s.spare1, 4194304), 4194304, bitmapranges, NULL))), to_char(decode(bitand(lf.fragflags, 33554432), 33554432, decode(bitand(lf.fragpro, 2048), 2048, decode(ds.lobret_stg, to_number(NULL), 'DEFAULT', 0, 'NONE', 1, 'AUTO', 2, 'MIN', 3, 'MAX', 4, 'DEFAULT', 'INVALID'), decode(bitand(lf.fragflags, 32), 32,'YES', 'NO')), decode(bitand(s.spare1, 2097152), 2097152, decode(s.lists, 0, 'NONE', 1, 'AUTO', 2, 'MIN', 3, 'MAX', 4, 'DEFAULT', 'INVALID'), decode(bitand(lf.fragflags, 32), 32,'YES', 'NO')))), to_char(decode(bitand(lf.fragflags, 33554432), 33554432, decode(bitand(lf.fragpro, 2048), 2048, ds.mintim_stg, NULL), decode(bitand(s.spare1, 2097152), 2097152, s.groups, NULL))), to_char(decode(bitand(ts.flags, 3), 1, to_number(NULL), decode(bitand(lf.fragflags, 33554432), 33554432, ds.pctinc_stg, s.extpct))), to_char(decode(bitand(lf.fragflags, 33554432), 33554432, decode(ds.frlins_stg, 0, 1, ds.frlins_stg), decode(s.lists, 0, 1, s.lists))), to_char(decode(bitand(lf.fragflags, 33554432), 33554432, decode(ds.maxins_stg, 0, 1, ds.maxins_stg), decode(s.groups, 0, 1, s.groups))), decode(bitand(lf.fragflags, 786), 2, 'NO', 16, 'NO', 256, 'NO', 512, 'YES', 'YES'), decode(bitand(decode(bitand(lf.fragflags, 33554432), 33554432, ds.bfp_stg, s.cachehint), 3), 1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'), decode(bitand(decode(bitand(lf.fragflags, 33554432), 33554432, ds.bfp_stg, s.cachehint), 12)/4, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(decode(bitand(lf.fragflags, 33554432), 33554432, ds.bfp_stg, s.cachehint), 48)/16, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(lf.fragflags,4096), 4096, 'YES', decode(bitand(lf.fragpro,2048), 2048, 'NO', 'NONE')), decode(bitand(lf.fragflags,57344), 8192, 'LOW', 16384, 'MEDIUM', 32768, 'HIGH', decode(bitand(lf.fragpro,2048), 2048, 'NO', 'NONE')), decode(bitand(lf.fragflags,458752), 65536, 'LOB', 131072, 'OBJECT', 327680, 'LOB VALIDATE', 393216, 'OBJECT VALIDATE', decode(bitand(lf.fragpro,2048), 2048, 'NO', 'NONE')), decode(bitand(lf.fragpro,2048), 2048, 'YES', 'NO'), decode(bitand(lf.fragflags, 33554432), 33554432, 'NO', 'YES') 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.tab$ t, sys.ts$ ts, sys.seg$ s, sys.user$ u, sys.attrcol$ a, sys.deferred_stg$ ds where o.owner# = u.user# and pobj.obj# = o.obj# and o.obj# = t.obj# and bitand(t.trigflag, 1073741824) != 1073741824 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.fragobj# = ds.obj#(+) and lf.ts# = s.ts#(+) 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.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL and lo.namespace = 8 and lo.remoteowner IS NULL and lo.linkname IS NULL and ((o.owner# = userenv('SCHEMAID') and lo.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 */) ) ) / create or replace 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, MAX_SIZE, RETENTION, MINRETENTION, PCT_INCREASE, FREELISTS, FREELIST_GROUPS, LOGGING, BUFFER_POOL, FLASH_CACHE, CELL_FLASH_CACHE, ENCRYPT, COMPRESSION, DEDUPLICATION, SECUREFILE, SEGMENT_CREATED) 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, row_number() over (partition by u.name, o.name, lo.name, lpo.subname order by lf.frag#), lf.chunk * ts.blocksize, decode(bitand(lf.fragflags, 32), 0, lf.pctversion$, to_number(NULL)), decode(bitand(lf.fragflags, 795), 1, 'NO', 2, 'NO', 8, 'CACHEREADS', 16, 'CACHEREADS', 256, 'YES', 512, 'YES', 'YES'), decode(lf.fragpro, 0, 'NO', 2048, 'NO', 'YES'), ts.name, to_char(decode(bitand(lf.fragflags, 33554432), 33554432, ds.initial_stg, s.iniexts) * ts.blocksize), to_char(decode(bitand(lf.fragflags, 33554432), 33554432, ds.next_stg, s.extsize) * ts.blocksize), to_char(decode(bitand(lf.fragflags, 33554432), 33554432, ds.minext_stg, s.minexts)), to_char(decode(bitand(lf.fragflags, 33554432), 33554432, ds.maxext_stg, s.maxexts)), to_char(decode(bitand(lf.fragflags, 33554432), 33554432, ds.maxsiz_stg * ts.blocksize, decode(bitand(s.spare1, 4194304), 4194304, bitmapranges, NULL))), to_char(decode(bitand(lf.fragflags, 33554432), 33554432, decode(bitand(lf.fragpro, 2048), 2048, decode(ds.lobret_stg, to_number(NULL), 'DEFAULT', 0, 'NONE', 1, 'AUTO', 2, 'MIN', 3, 'MAX', 4, 'DEFAULT', 'INVALID'), decode(bitand(lf.fragflags, 32), 32,'YES', 'NO')), decode(bitand(s.spare1, 2097152), 2097152, decode(s.lists, 0, 'NONE', 1, 'AUTO', 2, 'MIN', 3, 'MAX', 4, 'DEFAULT', 'INVALID'), decode(bitand(lf.fragflags, 32), 32,'YES', 'NO')))), to_char(decode(bitand(lf.fragflags, 33554432), 33554432, decode(bitand(lf.fragpro, 2048), 2048, ds.mintim_stg, NULL), decode(bitand(s.spare1, 2097152), 2097152, s.groups, NULL))), to_char(decode(bitand(ts.flags, 3), 1, to_number(NULL), decode(bitand(lf.fragflags, 33554432), 33554432, ds.pctinc_stg, s.extpct))), to_char(decode(bitand(lf.fragflags, 33554432), 33554432, decode(ds.frlins_stg, 0, 1, ds.frlins_stg), decode(s.lists, 0, 1, s.lists))), to_char(decode(bitand(lf.fragflags, 33554432), 33554432, decode(ds.maxins_stg, 0, 1, ds.maxins_stg), decode(s.groups, 0, 1, s.groups))), decode(bitand(lf.fragflags, 786), 2, 'NO', 16, 'NO', 256, 'NO', 512, 'YES', 'YES'), decode(bitand(decode(bitand(lf.fragflags, 33554432), 33554432, ds.bfp_stg, s.cachehint), 3), 1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'), decode(bitand(decode(bitand(lf.fragflags, 33554432), 33554432, ds.bfp_stg, s.cachehint), 12)/4, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(decode(bitand(lf.fragflags, 33554432), 33554432, ds.bfp_stg, s.cachehint), 48)/16, 1, 'KEEP', 2, 'NONE', 'DEFAULT'), decode(bitand(lf.fragflags,4096), 4096, 'YES', decode(bitand(lf.fragpro,2048), 2048, 'NO', 'NONE')), decode(bitand(lf.fragflags,57344), 8192, 'LOW', 16384, 'MEDIUM', 32768, 'HIGH', decode(bitand(lf.fragpro,2048), 2048, 'NO', 'NONE')), decode(bitand(lf.fragflags,458752), 65536, 'LOB', 131072, 'OBJECT', 327680, 'LOB VALIDATE', 393216, 'OBJECT VALIDATE', decode(bitand(lf.fragpro,2048), 2048, 'NO', 'NONE')), decode(bitand(lf.fragpro,2048), 2048, 'YES', 'NO'), decode(bitand(lf.fragflags, 33554432), 33554432, 'NO', 'YES') 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.tab$ t, sys.ts$ ts, sys.seg$ s, sys.user$ u, sys.attrcol$ a, sys.deferred_stg$ ds where o.owner# = u.user# and pobj.obj# = o.obj# and o.obj# = t.obj# and bitand(t.trigflag, 1073741824) != 1073741824 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.fragobj# = ds.obj#(+) and lf.ts# = s.ts#(+) 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.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL and lo.namespace = 8 and lo.remoteowner IS NULL and lo.linkname IS NULL / create or replace public synonym DBA_LOB_SUBPARTITIONS for DBA_LOB_SUBPARTITIONS / grant select on DBA_LOB_SUBPARTITIONS to select_catalog_role / remark Views for template descriptions create or replace view USER_SUBPARTITION_TEMPLATES (TABLE_NAME, SUBPARTITION_NAME, SUBPARTITION_POSITION, TABLESPACE_NAME, HIGH_BOUND) as select o.name, st.spart_name, st.spart_position + 1, ts.name, st.hiboundval from sys.obj$ o, sys.defsubpart$ st, sys.ts$ ts where st.bo# = o.obj# and st.ts# = ts.ts#(+) and o.owner# = userenv('SCHEMAID') and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL and o.subname IS NULL / create or replace public synonym USER_SUBPARTITION_TEMPLATES for USER_SUBPARTITION_TEMPLATES / grant select on USER_SUBPARTITION_TEMPLATES to PUBLIC with grant option / create or replace view DBA_SUBPARTITION_TEMPLATES (USER_NAME, TABLE_NAME, SUBPARTITION_NAME, SUBPARTITION_POSITION, TABLESPACE_NAME, HIGH_BOUND) as select u.name, o.name, st.spart_name, st.spart_position + 1, ts.name, st.hiboundval from sys.obj$ o, sys.defsubpart$ st, sys.ts$ ts, sys.user$ u where st.bo# = o.obj# and st.ts# = ts.ts#(+) and o.owner# = u.user# and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL and o.subname IS NULL / create or replace public synonym DBA_SUBPARTITION_TEMPLATES for DBA_SUBPARTITION_TEMPLATES / grant select on DBA_SUBPARTITION_TEMPLATES to select_catalog_role / create or replace view ALL_SUBPARTITION_TEMPLATES (USER_NAME, TABLE_NAME, SUBPARTITION_NAME, SUBPARTITION_POSITION, TABLESPACE_NAME, HIGH_BOUND) as select u.name, o.name, st.spart_name, st.spart_position + 1, ts.name, st.hiboundval from sys.obj$ o, sys.defsubpart$ st, sys.ts$ ts, sys.user$ u where st.bo# = o.obj# and st.ts# = ts.ts#(+) and o.owner# = u.user# and o.subname IS NULL and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL 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 */))) / create or replace public synonym ALL_SUBPARTITION_TEMPLATES for ALL_SUBPARTITION_TEMPLATES / grant select on ALL_SUBPARTITION_TEMPLATES to PUBLIC with grant option / create or replace view USER_LOB_TEMPLATES (TABLE_NAME, LOB_COL_NAME, SUBPARTITION_NAME, LOB_SEGMENT_NAME, TABLESPACE_NAME) as select o.name, decode(bitand(c.property, 1), 1, ac.name, c.name), st.spart_name, lst.lob_spart_name, ts.name from sys.obj$ o, sys.defsubpart$ st, sys.defsubpartlob$ lst, sys.ts$ ts, sys.col$ c, sys.attrcol$ ac where o.obj# = lst.bo# and st.bo# = lst.bo# and st.spart_position = lst.spart_position and lst.lob_spart_ts# = ts.ts#(+) and c.obj# = lst.bo# and c.intcol# = lst.intcol# and o.owner# = userenv('SCHEMAID') and o.subname IS NULL and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL and lst.intcol# = ac.intcol#(+) and lst.bo# = ac.obj#(+) / create or replace public synonym USER_LOB_TEMPLATES for USER_LOB_TEMPLATES / grant select on USER_LOB_TEMPLATES to PUBLIC with grant option / create or replace view DBA_LOB_TEMPLATES (USER_NAME, TABLE_NAME, LOB_COL_NAME, SUBPARTITION_NAME, LOB_SEGMENT_NAME, TABLESPACE_NAME) as select u.name, o.name, decode(bitand(c.property, 1), 1, ac.name, c.name), st.spart_name, lst.lob_spart_name, ts.name from sys.obj$ o, sys.defsubpart$ st, sys.defsubpartlob$ lst, sys.ts$ ts, sys.col$ c, sys.attrcol$ ac, sys.user$ u where o.obj# = lst.bo# and st.bo# = lst.bo# and st.spart_position = lst.spart_position and lst.lob_spart_ts# = ts.ts#(+) and c.obj# = lst.bo# and c.intcol# = lst.intcol# and lst.intcol# = ac.intcol#(+) and lst.bo# = ac.obj#(+) and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL and o.owner# = u.user# and o.subname IS NULL / create or replace public synonym DBA_LOB_TEMPLATES for DBA_LOB_TEMPLATES / grant select on DBA_LOB_TEMPLATES to select_catalog_role / create or replace view ALL_LOB_TEMPLATES (USER_NAME, TABLE_NAME, LOB_COL_NAME, SUBPARTITION_NAME, LOB_SEGMENT_NAME, TABLESPACE_NAME) as select u.name, o.name, decode(bitand(c.property, 1), 1, ac.name, c.name), st.spart_name, lst.lob_spart_name, ts.name from sys.obj$ o, sys.defsubpart$ st, sys.defsubpartlob$ lst, sys.ts$ ts, sys.col$ c, sys.attrcol$ ac, sys.user$ u where o.obj# = lst.bo# and st.bo# = lst.bo# and st.spart_position = lst.spart_position and lst.lob_spart_ts# = ts.ts#(+) and c.obj# = lst.bo# and c.intcol# = lst.intcol# and lst.intcol# = ac.intcol#(+) and lst.bo# = ac.obj#(+) and o.owner# = u.user# and o.subname IS NULL and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL 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 */))) / create or replace public synonym ALL_LOB_TEMPLATES for ALL_LOB_TEMPLATES / grant select on ALL_LOB_TEMPLATES to PUBLIC with grant option /