1. Database Reference
  2. Static Data Dictionary Views
  3. Static Data Dictionary Views: ALL_PART_COL_STATISTICS to DATABASE_PROPERTIES
  4. ALL_TAB_SUBPARTITIONS

3.119 ALL_TAB_SUBPARTITIONS

ALL_TAB_SUBPARTITIONS displays, for each table subpartition accessible to the current user, the subpartition name, name of the table and partition to which it belongs, its storage attributes, and statistics generated by the DBMS_STATS package.

Related Views

  • DBA_TAB_SUBPARTITIONS displays such information for all subpartitions in the database.

  • USER_TAB_SUBPARTITIONS displays such information for subpartitions of all partitioned objects owned by the current user. This view does not display the TABLE_OWNER column.

Column Datatype NULL Description

TABLE_OWNER

VARCHAR2(128)

NOT NULL

Owner of the table

TABLE_NAME

VARCHAR2(128)

NOT NULL

Name of the table

PARTITION_NAME

VARCHAR2(128)

Name of the partition

SUBPARTITION_NAME

VARCHAR2(128)

Name of the subpartition

HIGH_VALUE

LONG

Subpartition bound value expression

HIGH_VALUE_LENGTH

NUMBER

NOT NULL

Length of the subpartition bound value expression

PARTITION_POSITION

NUMBER

Position of the partition within the table

SUBPARTITION_POSITION

NUMBER

Position of the subpartition within the partition

TABLESPACE_NAME

VARCHAR2(30)

NOT NULL

Name of the tablespace containing the subpartition

PCT_FREE

NUMBER

NOT NULL

Minimum percentage of free space in a block

PCT_USED

NUMBER

Minimum percentage of used space in a block

INI_TRANS

NUMBER

NOT NULL

Initial number of transactions

MAX_TRANS

NUMBER

NOT NULL

Maximum number of transactions

INITIAL_EXTENT

NUMBER

Size of the initial extent in bytes (for a range partition); size of the initial extent in blocks (for a composite partition)

NEXT_EXTENT

NUMBER

Size of secondary extents in bytes (for a range partition); size of secondary extents in blocks (for a composite partition)

MIN_EXTENT

NUMBER

Minimum number of extents allowed in the segment

MAX_EXTENT

NUMBER

Maximum number of extents allowed in the segment

MAX_SIZE

NUMBER

Maximum number of blocks allowed in the segment

PCT_INCREASE

NUMBER

Percentage increase in extent size

FREELISTS

NUMBER

Number of freelist groups allocated in this segment

FREELIST_GROUPS

NUMBER

Number of freelist groups allocated in this segment

LOGGING

VARCHAR2(3)

Indicates whether or not changes to the table are logged:

  • YES

  • NO

COMPRESSION

VARCHAR2(8)

Indicates whether this subpartition is compressed (ENABLED) or not (DISABLED)

COMPRESS_FOR

VARCHAR2(30)

Default compression for what kind of operations:

  • BASIC

  • ADVANCED

  • QUERY LOW

  • QUERY HIGH

  • ARCHIVE LOW

  • ARCHIVE HIGH

  • NULL

The QUERY_LOW, QUERY_HIGH, ARCHIVE_LOW, and ARCHIVE_HIGH values are associated with Hybrid Columnar Compression, a feature of the Enterprise Edition of Oracle Database that is dependent on the underlying storage system. See Oracle Database Concepts for more information.

NUM_ROWS

NUMBER

Number of rows in the subpartition

BLOCKS

NUMBER

Number of blocks in the subpartition

EMPTY_BLOCKS

NUMBER

Number of empty blocks in the subpartition

AVG_SPACE

NUMBER

Average space in the subpartition

CHAIN_CNT

NUMBER

Chain count

AVG_ROW_LEN

NUMBER

Average row length

SAMPLE_SIZE

NUMBER

Sample size

LAST_ANALYZED

DATE

Date on which this table was most recently analyzed

BUFFER_POOL

VARCHAR2(7)

Buffer pool for this subpartition:

  • DEFAULT

  • KEEP

  • RECYCLE

  • NULL

FLASH_CACHE

VARCHAR2(7)

Database Smart Flash Cache hint to be used for subpartition blocks:

  • DEFAULT

  • KEEP

  • NONE

Solaris and Oracle Linux functionality only.

CELL_FLASH_CACHE

VARCHAR2(7)

Cell flash cache hint to be used for subpartition blocks:

  • DEFAULT

  • KEEP

  • NONE

See Also: Oracle Exadata Storage Server Software documentation for more information

GLOBAL_STATS

VARCHAR2(3)

GLOBAL_STATS will be YES if statistics have been gathered or NO if statistics have not been gathered

USER_STATS

VARCHAR2(3)

Indicates whether statistics were entered directly by the user (YES) or not (NO)

INTERVAL

VARCHAR2(3)

Indicates whether the partition is in the interval section of an interval partitioned table (YES) or whether the partition is in the range section (NO)

SEGMENT_CREATED

VARCHAR2(3)

Indicates whether the table subpartition segment has been created (YES) or not (NO); N/A indicates that this table is not subpartitioned

INDEXING

VARCHAR2(3)

Indicates the indexing property.

Possible values:

  • ON - Indexing is on for this subpartition

  • OFF - Indexing is off for this subpartition

READ_ONLY

VARCHAR2(3)

Indicates whether a subpartition is read-only or read/write:

  • YES: The default setting for the subpartition is read-only.

  • NO: The default setting for the subpartition is read/write.

INMEMORY

VARCHAR2(8)

Indicates whether the In-Memory Column Store (IM column store) is enabled (ENABLED) or disabled (DISABLED) for this subpartition

INMEMORY_PRIORITY

VARCHAR2(8)

Indicates the priority for In-Memory Column Store (IM column store) population. Possible values:

  • LOW

  • MEDIUM

  • HIGH

  • CRITICAL

  • NONE

  • NULL

INMEMORY_DISTRIBUTE

VARCHAR2(15)

Indicates how the IM column store is distributed in an Oracle Real Application Clusters (Oracle RAC) environment:

  • AUTO

  • BY ROWID RANGE

  • DBY PARTITION

  • BY SUBPARTITION

INMEMORY_COMPRESSION

VARCHAR2(17)

Indicates the compression level for the IM column store in an Oracle RAC environment:

  • NO MEMCOMPRESS

  • FOR DML

  • FOR QUERY [ LOW | HIGH ]

  • FOR CAPACITY [ LOW | HIGH ]

  • NULL

This column has a value based on where the segments lie for a table. For example, if the table is partitioned and is enabled for IM column store, the value is NULL for ALL_TABLES but non-NULL for ALL_TAB_SUBPARTITIONS.

INMEMORY_DUPLICATE

Indicates the duplicate setting for the IM column store in an Oracle RAC environment:

  • DUPLICATE

  • NO DUPLICATE

  • DUPLICATE ALL

INMEMORY_SERVICE

VARCHAR2(12)

Indicates how the IM column store is populated on various instances. The possible values are:

  • DEFAULT: Data is populated on all instances specified with the PARALLEL_INSTANCE_GROUP initialization parameter. If that parameter is not set, then the data is populated on all instances. This is the default.

  • NONE: Data is not populated on any instance.

  • ALL: Data is populated on all instances, regardless of the value of the PARALLEL_INSTANCE_GROUP initialization parameter.

  • USER_DEFINED: Data is populated only on the instances on which the user-specified service is active. The service name corresponding to this is stored in the INMEMORY_SERVICE_NAME column.

INMEMORY_SERVICE_NAME

VARCHAR2(1000)

Indicates the service name for the service on which the IM column store should be populated. This column has a value only when the corresponding INMEMORY_SERVICE is USER_DEFINED. In all other cases, this column is null.

CELLMEMORY

VARCHAR2(24)

The value for columnar compression in the storage cell flash cache. Possible values:

  • ENABLED: Oracle Exadata Storage will decide automatically whether to cache in columnar form

  • DISABLED: Oracle Exadata Storage is prevented from caching in columnar form

  • NO CACHECOMPRESS: Oracle Exadata Storage will cache in HCC format (no recompression)

  • FOR QUERY: Oracle Exadata Storage will recompress and cache in INMEMORY query high format

  • FOR CAPACITY: Oracle Exadata Storage will recompress and cache in INMEMORY capacity low format

This column is intended for use with Oracle Exadata.

MEMOPTIMIZE_READ

VARCHAR2(8)

Indicates whether the table is enabled for Fast Key Based Access (ENABLED) or not (DISABLED)

MEMOPTIMIZE_WRITE

VARCHAR2(8)

For internal use only

See Also: