Skip Headers
Oracle® Database Reference
11g Release 2 (11.2)

Part Number E17110-04
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

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, and its storage attributes.

Related Views

Note:

Statistics are not collected on a per-subpartition basis.
Column Datatype NULL Description
TABLE_OWNER VARCHAR2(30) NOT NULL Owner of the table
TABLE_NAME VARCHAR2(30) NOT NULL Name of the table
PARTITION_NAME VARCHAR2(30)   Name of the partition
SUBPARTITION_NAME VARCHAR2(30)   Name of the subpartition
HIGH_VALUE LONG   Subpartition bound value expression
HIGH_VALUE_LENGTH NUMBER NOT NULL Length of the subpartition bound value expression
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 NOT NULL Minimum number of extents allowed in the segment
MAX_EXTENT NUMBER NOT NULL 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(12)   Default compression for what kind of operations:
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 Enterprise 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)   Indicates whether column statistics for the subpartition were collected by analyzing the table as a whole (YES) or estimated from statistics collected for partitions and subpartitions (NO)
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

Footnote 1 Hybrid columnar compression is a feature of Oracle Exadata. See Oracle Exadata Storage Server Software documentation for more information.