Note
This view is available starting with V2.2.77.
Purpose
This view displays the information about subpartition templates of partitioned tables owned by the current user.
Applicability
This view is available only in Oracle-compatible mode.
Columns
| Column | Type | Nullable | Description |
|---|---|---|---|
| TABLE_NAME | VARCHAR2(256) | NO | The name of the partitioned table. |
| SUBPARTITION_NAME | VARCHAR2(132) | NO | The name of the subpartition. |
| SUBPARTITION_POSITION | NUMBER | YES | The position of the subpartition. |
| TABLESPACE_NAME | VARCHAR2(30) | NO | The name of the tablespace to which the subpartition belongs. |
| HIGH_BOUND | VARCHAR2(32767) | NO | The partitioning expression. |
| COMPRESSION | VARCHAR2(4) | NO | The default value is NULL. This column indicates whether the data of each new composite partition added to the subpartition template is stored in a compressed format. Valid values:
You can specify this column in the subpartition template. |
| INDEXING | VARCHAR2(4) | NO | The default value is NULL. This column indicates whether the data of each new composite partition added to the subpartition template is considered to be indexed. Valid values:
You can specify this column in the subpartition template. |
| READ_ONLY | VARCHAR2(4) | NO | The default value is NULL. This column indicates whether the data of each new composite partition added to the subpartition template is read-only. Valid values:
You can specify this column in the subpartition template. |
Sample query
Query the information about subpartition templates of partitioned tables owned by the current user.
obclient [SYS]> SELECT * FROM SYS.USER_SUBPARTITION_TEMPLATES;
The query result is as follows:
+------------+-------------------+-----------------------+-----------------+------------+-------------+----------+-----------+
| TABLE_NAME | SUBPARTITION_NAME | SUBPARTITION_POSITION | TABLESPACE_NAME | HIGH_BOUND | COMPRESSION | INDEXING | READ_ONLY |
+------------+-------------------+-----------------------+-----------------+------------+-------------+----------+-----------+
| T2_M_LR | MP0 | 1 | NULL | 100 | NULL | NULL | NULL |
| T2_M_LR | MP1 | 2 | NULL | 200 | NULL | NULL | NULL |
| T2_M_LR | MP2 | 3 | NULL | 300 | NULL | NULL | NULL |
+------------+-------------------+-----------------------+-----------------+------------+-------------+----------+-----------+
3 rows in set
References
Query the information about subpartition templates of all partitioned tables in the current tenant: DBA_SUBPARTITION_TEMPLATES
Query the information about subpartition templates of all partitioned tables accessible to the current user: ALL_SUBPARTITION_TEMPLATES
For more information about how to create a partitioned table: Create a partitioned table