Hi All,
We have a requirement that we need to partition all existing tables (10); all tables will have a primary column e.g. (sales_state)
- Requirement is partition has to be dynamically created on all tables
- We should be able to perform all CRUD operations on a partition
- lock a particular portion
- delete a partition
trying below example
CREATE TABLE sales_auto_list
(
salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
sales_state VARCHAR2(20),
sales_amount NUMBER(10),
sales_date DATE
)
PARTITION BY LIST (sales_state)
automatic (PARTITION sales_state VALUES ('CALIFORNIA')
);
Whenever a new record is inserted into a new state for the first time, a partition is supposed to be created, and state-specific data should be part of that partition.
INSERT INTO SALES_AUTO_LIST VALUES(021, 'helloh', 'NY', 41000, TO_DATE ('21-DEC-2018','DD-MON-YYYY'));
Here, when I query the USER_TAB_PARTITIONS
SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME ='SALES_AUTO_LIST';
SALES_AUTO_LIST P_CAL 'CALIFORNIA'
SALES_AUTO_LIST SYS_P241 'FLORIDA'
SALES_AUTO_LIST SYS_P242 'TEXAS'
SALES_AUTO_LIST SYS_P243 'NY'
The partition name seems like a system defined.(SYS_P241)
My requirement is partition should be the value of the 'sales_state' e.g, TEXAS
,so that I can query data from a particular partition.
select * from sales_auto_list PARTITION (TEXAS); because I know the column value.
Any pointers would be helpful.