A new extended partition syntax can be used to designate a partition without knowing its name. The syntax must refer to a possible value for the partition. This syntax works for all cases when you have to reference a partition, whether it be range, list, interval, or hash. It supports all operations such as drop, merge, split, and so on.
Some samples are shown below -
create table SALES (
id number,
order_date date
)
partition by range (order_date)
(
partition p1 values less than
(to_date('01/01/2008','mm/dd/yyyy')),
partition p2 values less than
(to_date('02/01/2008','mm/dd/yyyy')),
partition p3 values less than
(to_date('03/01/2008','mm/dd/yyyy'))
);
Generally when you want to merge partitions, the syntax will have to refer to the partition names.
alter table SALES merge partitions p2, p3 into partition p2_3;
However, in 11g, the same can be accomplished by referring to a possible value for the partition with use of “for” syntax.
alter table SALES merge partitions
for(to_date('01/12/2008','mm/dd/yyyy')),
for(to_date('02/15/2008','mm/dd/yyyy'))
into partition p2_3;
SQL> select table_name, partition_name, high_value
from user_tab_partitions
where table_name = 'SALES';
TABLE_NAME PARTITION_ HIGH_VALUE
---------- ---------- ------------------------------
SALES P1 TO_DATE(' 2008-01-01 00:00:00'
, 'SYYYY-MM-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIA
SALES P2_3 TO_DATE(' 2008-03-01 00:00:00'
, 'SYYYY-MM-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIA
Or with dropping a partition -
SQL> alter table SALES drop partition
for(to_date('02/15/2008','mm/dd/yyyy'));
Table altered.
SQL> select table_name, partition_name, high_value
from user_tab_partitions
where table_name = 'SALES';
TABLE_NAME PARTITION_ HIGH_VALUE
---------- ---------- ------------------------------
SALES P1 TO_DATE(' 2008-01-01 00:00:00'
, 'SYYYY-MM-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIA
