Dropping interval partitions – Revisited

In a previous posting about dropping interval partitions I explained how the transition point in an interval partitioned table can be moved up to be able to drop partitions. I explained that by temporarily converting the table from interval to range partitioning, all interval partitions are converted into range partitions.

Last week I discovered, suggested by Heijne Makkreel, that this conversion is unnecessary and that just setting the interval granularity to what it is already set to, all interval partitions are converted to range partitions.

The query below shows the partitions of an interval partitioned table (the same table as used in my original posting) with one range partition and four adjacent interval partitions. The partitions are 1 month in size.

SQL> select partition_name,interval,high_value
  2  from dba_tab_partitions where table_name = 'IPART'
  3  order by partition_position;

PARTITION_NAME INT HIGH_VALUE
-------------- --- ------------------------------------------------------------
SYS_P359       NO  TO_DATE(' 2010-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
SYS_P360       YES TO_DATE(' 2010-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
SYS_P361       YES TO_DATE(' 2010-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
SYS_P362       YES TO_DATE(' 2010-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
SYS_P363       YES TO_DATE(' 2010-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N

SQL> select interval from dba_part_tables where table_name = 'IPART';

INTERVAL
--------------------------------------------------------------------------------
NUMTOYMINTERVAL(1,'MONTH')

By issuing an ALTER TABLE command that sets the interval to what it is already set to, all interval partitions are converted into range partitions as demonstrated below:

SQL> alter table ipart set interval (NUMTOYMINTERVAL(1,'month'));

Table altered.

SQL> select partition_name,interval,high_value
  2  from dba_tab_partitions where table_name = 'IPART'
  3  order by partition_position;

PARTITION_NAME INT HIGH_VALUE
-------------- --- ------------------------------------------------------------
SYS_P359       NO  TO_DATE(' 2010-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
SYS_P360       NO  TO_DATE(' 2010-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
SYS_P361       NO  TO_DATE(' 2010-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
SYS_P362       NO  TO_DATE(' 2010-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
SYS_P363       NO  TO_DATE(' 2010-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N

The query output above shows that all partitions are now of type range, and we can simply drop them if we wish to do so.
-Harald

6th Planboard DBA Symposium: Call for Papers

After the successful 5th edition of the DBA Symposium, which featured Jonathan Lewis, Planboard just begun planning the 6th edition of this well-known Dutch DBA Symposium and the Call for Papers is now open.

If you are a Dutch DBA who wants to share his or her experiences with other Dutch DBA’s you definitely want to be a speaker at this wonderful symposium to be held on November 23th 2010! This is your chance to share that fantastic idea or solution you invented but didn’t know where to share it.

Please visit the Planboard website and submit your abstract before September 1st2010.
-Harald

5th Planboard DBA Symposium: Registration now open

On June 8 Planboard will run her 5th Dutch DBA Symposium and the registration is now open. This “for Dutch DBA’s, by Dutch DBA’s” symposium has become the place to be for the serious DBA who wants to share his or her knowledge with other DBA’s in an open environment with plenty of networking time between the 5 sessions.

Because this is the jubilee edition we, the program committee, wanted to make this event really special and I am glad that we found Jonathan Lewis willing to come over and give an interactive presentation about Statspack. Please enroll yourself on the Planboard website. As a program committee member I recommend attending this wonderful event and I hope to see you all on June 8.
-Harald