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 applicable only to OceanBase Database in Oracle 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 partition belongs. |
| HIGH_BOUND | VARCHAR2(32767) | NO | The partition template expression. |
| COMPRESSION | VARCHAR2(4) | NO | The default value is NULL. Indicates whether the data in each new composite partition added to the subpartition template is stored in a compressed format:
This value can be specified in the subpartition template. |
| INDEXING | VARCHAR2(4) | NO | The default value is NULL. Indicates whether the data in each new composite partition added to the subpartition template is considered to be indexed:
This value can be specified in the subpartition template. |
| READ_ONLY | VARCHAR2(4) | NO | The default value is NULL. Indicates whether the data in each new composite partition added to the subpartition template is read-only:
This value can be specified 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