Example - Setting an AUTOMATIC List Partition v13
The example that follows modifies a table sales to use automatic list partition instead of regular list partitioning. Use the following command to create a sales table:
CREATE TABLE sales
(
dept_no number,
part_no varchar2,
sales_state varchar2(20),
date date,
amount number
)
PARTITION BY LIST(sales_state)
(
PARTITION P_KAN VALUES('KANSAS'),
PARTITION P_TEX VALUES('TEXAS')
);To implement automatic list partitioning on the sales table, invoke the following command:
ALTER TABLE sales SET AUTOMATIC;
Query the ALL_TAB_PARTITIONS view to see that an existing partition is successfully created.
edb=# SELECT table_name, partition_name, high_value FROM ALL_TAB_PARTITIONS; table_name | partition_name | high_value ------------+----------------+------------ SALES | P_KAN | 'KANSAS' SALES | P_TEX | 'TEXAS' (2 rows)
Now, insert data into the sales table to create a new partition and add the new value.
edb=# INSERT INTO sales VALUES (1, 'VIR', 'VIRGINIA'); INSERT 0 1
Then, query the ALL_TAB_PARTITIONS view again after the insert. The automatic list partition is successfully created and data is inserted. A system-generated name of the partition is created that varies for each session.
edb=# SELECT table_name, partition_name, high_value FROM ALL_TAB_PARTITIONS; table_name | partition_name | high_value ------------+----------------+------------ SALES | P_KAN | 'KANSAS' SALES | P_TEX | 'TEXAS' SALES | SYS106900103 | 'VIRGINIA' (3 rows)