After a dynamic partitioned table is created, you can execute the SHOW CREATE TABLE statement to query the table definition, or check the information about the dynamic partitioned table in the database by using the views.
Query the definition of a table
After a table is created, you can execute the SHOW CREATE TABLE table_name; statement to query the table definition.
For more information about the SHOW statement, see SHOW.
Here is an example:
Query 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" NUMBER(*,0),
"COL2" TIMESTAMP(6)
) COMPRESS FOR ARCHIVE 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(col2)
(partition "P0" values less than (Timestamp '2024-11-11 13:30:00.000000')) |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
Use views to query information about a dynamic partitioned table
| View | Description |
|---|---|
| sys.DBA_OB_DYNAMIC_PARTITION_TABLES | Displays the information about dynamic partitioned tables in the current tenant.
NoteThis view is introduced in V4.3.5 BP2. |
| sys.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.
NoteThis view is introduced in V4.3.5 BP2. |
Here is an example:
SELECT * FROM sys.DBA_OB_DYNAMIC_PARTITION_TABLES
WHERE TABLE_NAME = 'TEST_TBL1';
The return 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 |
+---------------+------------+----------+----------------------------------------+--------+-----------+----------------+-------------+-----------+------------------+
| SYS | TEST_TBL1 | 507940 | Timestamp '2024-11-11 13:30:00.000000' | TRUE | HOUR | 3HOUR | 1DAY | +8:00 | NONE |
+---------------+------------+----------+----------------------------------------+--------+-----------+----------------+-------------+-----------+------------------+
1 row in set
