split partitions

I have a question in split partition. The table name is sales_by_region.

CREATE TABLE sales_by_region (item# INTEGER, qty INTEGER,
store_name VARCHAR(30), state_code VARCHAR(2),
sale_date DATE)
STORAGE(INITIAL 10K NEXT 20K) TABLESPACE sap_stage_data
PARTITION BY LIST (state_code)
(
PARTITION region_east
VALUES ('MA','NY','CT','NH','ME','MD','VA','PA','NJ')
STORAGE (INITIAL 20K NEXT 40K PCTINCREASE 50)
TABLESPACE sap_stage_data,
PARTITION region_west
VALUES ('CA','AZ','NM','OR','WA','UT','NV','CO')
PCTFREE 25 NOLOGGING,
PARTITION region_south
VALUES ('TX','KY','TN','LA','MS','AR','AL','GA'),
PARTITION region_central
VALUES ('OH','ND','SD','MO','IL','MI','IA'),
PARTITION region_null
VALUES (NULL),
PARTITION region_unknown
VALUES (DEFAULT)
);

I am splitting the region_east partition into region_east_1 & region_east_2 as below. My question is, which values('CT', 'VA', 'MD') goes to region_east_1 and which values goes to region_east_2 partition. How oracle maps these values('CT', 'VA', 'MD') to two partitions(region_east_1 & region_east_2). Can any one please clarify?

ALTER TABLE sales_by_region
SPLIT PARTITION region_east VALUES ('CT', 'VA', 'MD')
INTO
( PARTITION region_east_1
PCTFREE 25 TABLESPACE sap_stage_data,
PARTITION region_east_2
STORAGE (NEXT 2M PCTINCREASE 25))
PARALLEL 5;

Are you sure this is a valid syntax? That for sure will work for hash partitioning but here I think you should specify at which value the partition should be split.

Regards

Listed values go to first partition then CT, VA and MD go to region_east_1
All non listed values existing in original partition go to region_east_2

By the way, your case study rings a bell... I'm almost sure I've seen than example in some place.

I was also looking a ask a question about partition split. I have this example that i saw somewhere out there...

ALTER TABLE my_table SPLIT PARTITION my_table_part AT (3)
INTO (PARTITION my_table_part_1,
PARTITION my_table_part_2);

then follows this select/verification
COLUMN high_value FORMAT A20
SELECT table_name,
partition_name,
high_value,
num_rows
FROM user_tab_partitions
ORDER BY table_name, partition_name;

TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
----------- ------------ --------- ---------- ------------- ----------
MY_TABLE MY_TABLE_PART_1 3 2
MY_TABLE MY_TABLE_PART_2 MAXVALUE 2

My questions is...where does the (3) as in At (3) above come from?
I would have thought it comes from the HIGH_VALUE column of the table to be split?
but my does not return anything..
SQL> select high_value from user_tab_partitions where table_name ='AAS_AON_FCT_2004_Q4';

no rows selected
Thanks

ndisang -- Your example looks like related to range-partitioning where you are creating partitions depending on some value range of the key.

The number (3) in the example is the higher-value you admit in that specific partition

PAVB, thank you for the answer.

You are right PAVB...my table is range partition on date field.
Thanks...

I have one quick question about split partition.

My table is list partitioned table. I have default partition name called REST. Now i wanted to add new partitions, but due to the default partition, i am not able to add it. But default partition REST does not have any values. I mean, default partition is empty now. At this circumstances, i want to add a partition. As per my knowlege, the only option is, drop the default partition and add new partitions. Is there any other options to add new partition at this situation? If so, can any one provide a syntax to add new partition?

Thanks

You can't add partitions in the middle of your partition set... you have to split partitions.

Dear PAVB, I understand that i can not add a partition now. Can you please tell me the syntax for spliting the default partition. Again, default partition is empty. Now how can i split it? If possible, i need commands for this?

Assuming the list of values for your new partition are not included in any one of your current partitions...
Assuming the values you want to include in your new partition are 'XX', 'YY' and 'ZZ"...

ALTER TABLE table_name
SPLIT PARTITION rest VALUES ('XX', 'YY', 'ZZ')
INTO
( PARTITION new_partition_name
TABLESPACE tablespace_name,
PARTITION rest
STORAGE (PCTINCREASE 0)
)
;

Thanks much. It helped.