Note
This view is available starting with V2.2.77.
Purpose
This view displays the information about subpartition templates of partitioned tables accessible to the current user.
Applicability
This view applies 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 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 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 a partial index: 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 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
View the information about subpartition templates of all partitioned tables in the current tenant: DBA_SUBPARTITION_TEMPLATES
View 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.