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
15 thoughts on “A new extended partition syntax in 11g”
A new cool feature is the interval partitioning as well. With the interval partitioning you don’t have other options but the new ‘for’ clause.
Nice article !
Yes I agree.
Thanks for stopping by.
Além disso, não é possível para os indivíduos para passar o tempo em academias e clubes de saúde por causa de seus compromissos profissionais. Logo, é a sua preferência em primeiro lugar para conseguir uma solução aberto e conveniente que é benévolo o suficiente para cumprir o seu finalidade sem afetar suas programações. Em tais condições, zero poderia ser melhor do que Fort Max Diet.
I really like what you guys are up too. This type of clever work and reporting! Keep up the superb works guys I’ve incorporated you guys to our blogroll.
Nice post. I used to be checking continuously this weblog and I’m inspired! Very helpful information specially the closing part 🙂 I maintain such information much. I was looking for this particular info for a very long time. Thanks and best of luck.
Good post. I learn something totally new and challenging on blogs I stumbleupon on a daily basis. It will always be helpful to read through articles from other writers and use something from other web sites.
Nice post. I learn something new and challenging on sites I stumbleupon every day. It’s always interesting to read through articles from other writers and practice something from their websites.
Thanks for some other wonderful post. Where else could anybody get that kind of information in such a perfect approach of writing? I’ve a presentation next week, and I’m at the look for such info.
Very nice article. I definitely love this site. Thanks!
I just like the helpful information you provide on your articles. I will bookmark your blog and take a look at once more right here regularly. I am rather sure I will be told a lot of new stuff right here! Best of luck for the next!
Good article. I absolutely appreciate this website. Stick with it!
I would like this site significantly a lot great info.
After I originally left a comment I seem to have clicked the -Notify me when new comments are added- checkbox and now whenever a comment is added I recieve four emails with the same comment. Perhaps there is an easy method you are able to remove me from that service? Thanks!