After a dynamic partitioned table is created, you can execute the SHOW CREATE TABLE statement to view the table definition, or view the information about dynamic partitioned tables in the database by using views.
View the table definition
After a table is created, you can execute the SHOW CREATE TABLE table_name; statement to view the table definition.
For more information about the SHOW statement, see SHOW.
Here is an example:
View the definition of the test_tbl1 table.
SHOW CREATE TABLE test_tbl1;
The query result is as follows:
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_tbl1 | CREATE TABLE `test_tbl1` (
`col1` int(11) DEFAULT NULL,
`col2` datetime DEFAULT NULL
) ORGANIZATION INDEX DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 DYNAMIC_PARTITION_POLICY = (ENABLE = TRUE, TIME_UNIT = 'HOUR', PRECREATE_TIME = '3HOUR', EXPIRE_TIME = '1DAY', TIME_ZONE = '+8:00', BIGINT_PRECISION = 'NONE')
partition by range columns(col2)
(partition `P0` values less than ('2025-04-15 13:30:00')) |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
View dynamic partitioned table information by using views
View name |
Feature description |
|---|---|
| oceanbase.DBA_OB_DYNAMIC_PARTITION_TABLES | Displays the information about dynamic partitioned tables in the current tenant. |
| V$OB_DYNAMIC_PARTITION_TABLES | Displays the information about dynamic partitioned tables in the current tenant. This view reads the attributes of dynamic partitioned tables from the memory, providing better read performance. |
Here is an example:
SELECT * FROM oceanbase.DBA_OB_DYNAMIC_PARTITION_TABLES
WHERE TABLE_NAME = 'test_tbl1';
The query result is as follows:
+---------------+------------+----------+-----------------------+--------+-----------+----------------+-------------+-----------+------------------+
| DATABASE_NAME | TABLE_NAME | TABLE_ID | MAX_HIGH_BOUND_VAL | ENABLE | TIME_UNIT | PRECREATE_TIME | EXPIRE_TIME | TIME_ZONE | BIGINT_PRECISION |
+---------------+------------+----------+-----------------------+--------+-----------+----------------+-------------+-----------+------------------+
| db_test | test_tbl1 | 511391 | '2025-04-15 13:30:00' | TRUE | HOUR | 3HOUR | 1DAY | +8:00 | NONE |
+---------------+------------+----------+-----------------------+--------+-----------+----------------+-------------+-----------+------------------+
1 row in set