Note
This view is available starting with V2.2.77.
Purpose
This view displays the information about subpartition templates of the partitioned tables accessible to the current user.
Applicability
This view is applicable only to OceanBase Database in Oracle mode.
Columns
| Column | Type | Nullable | Description |
|---|---|---|---|
| TABLE_OWNER | VARCHAR2(128) | NO | The owner of the partitioned table. |
| TABLE_NAME | VARCHAR2(128) | 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 expression of the partition template. |
| 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:
This value can be specified 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 partially indexed. Valid values:
This value can be specified 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:
This value can be specified in the subpartition template. |
Sample query
Query the information about subpartition templates of the partitioned tables accessible to the current user.
obclient [SYS]> SELECT * FROM SYS.ALL_SUBPARTITION_TEMPLATES;
The query result is as follows:
+-----------+------------+-------------------+-----------------------+-----------------+------------+-------------+----------+-----------+
| USER_NAME | TABLE_NAME | SUBPARTITION_NAME | SUBPARTITION_POSITION | TABLESPACE_NAME | HIGH_BOUND | COMPRESSION | INDEXING | READ_ONLY |
+-----------+------------+-------------------+-----------------------+-----------------+------------+-------------+----------+-----------+
| SYS | T2_M_LR | MP0 | 1 | NULL | 100 | NULL | NULL | NULL |
| SYS | T2_M_LR | MP1 | 2 | NULL | 200 | NULL | NULL | NULL |
| SYS | 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 partitioned tables owned by the current user: USER_SUBPARTITION_TEMPLATES
For information about how to create a partitioned table, see Create a partitioned table.