DBMS_METADATA_DDL and Interval partitions

I am writing this post after a OTN question on DBMS_METADATA not showing the DDL after interval partitions are created and Jonathan lewis reason for this behavior.

create table test3
 (
 id number,
 created_dt date
 )
 partition by range (created_dt)
 interval (numtoyminterval(1, 'month'))
 (
 partition p_feb2017 values less than (to_date('2017-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian')) ,
 partition p_mar2017 values less than (to_date('2017-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian'))
 ) ;
 SQL> select partition_name,partition_position,high_value from user_tab_partitions where table_name ='TEST3' order by partition_position;

PARTITION_NAME PARTITION_POSITION HIGH_VALUE
 -------------------- ------------------ --------------------------------------------------------------------------------
 P_FEB2017 1 TO_DATE(' 2017-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
 P_MAR2017 2 TO_DATE(' 2017-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

Check the DBMS_METADATA output for the table.

SQL> select dbms_metadata.get_ddl('TABLE','TEST3','RAKESH') from dual;
 DBMS_METADATA.GET_DDL('TABLE','TEST3','RAKESH')
 --------------------------------------------------------------------------

CREATE TABLE "RAKESH"."TEST3"
 ( "ID" NUMBER,
 "CREATED_DT" DATE
 ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 STORAGE(
 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 TABLESPACE "USERS"
 PARTITION BY RANGE ("CREATED_DT") INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
  (PARTITION "P_FEB2017" VALUES LESS THAN (TO_DATE(' 2017-03-01 00:00:00', 'SYYY
 Y-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED
 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
 STORAGE(
 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 TABLESPACE "USERS" ,
 PARTITION "P_MAR2017" VALUES LESS THAN (TO_DATE(' 2017-04-01 00:00:00', 'SYYYY
 -MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED
 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
 STORAGE(
 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 TABLESPACE "USERS" )

Insert new data into the table.

SQL> insert into test3 values (1,to_date('8-MAY-2017', 'DD-MON-YYYY'));

1 row created.

SQL> commit;

Commit complete.

Interval partition created.

SQL> select partition_name,partition_position,high_value from user_tab_partitions where table_name ='TEST3' order by partition_position;

PARTITION_NAME PARTITION_POSITION HIGH_VALUE
 -------------------- ------------------ --------------------------------------------------------------------------------
 P_FEB2017 1 TO_DATE(' 2017-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
 N')

P_MAR2017 2 TO_DATE(' 2017-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
 N')

SYS_P911 3 TO_DATE(' 2017-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
  N')

But the DDL from DBMS_METADATA still shows the partitions that were created when the table first created, doesn’t include the new partition created for MAY.

CREATE TABLE "RAKESH"."TEST3"
 ( "ID" NUMBER,
 "CREATED_DT" DATE
 ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 STORAGE(
 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 TABLESPACE "USERS"
 PARTITION BY RANGE ("CREATED_DT") INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
 (PARTITION "P_FEB2017" VALUES LESS THAN (TO_DATE(' 2017-03-01 00:00:00', 'SYYY
 Y-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED
 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
 STORAGE(
 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 TABLESPACE "USERS" ,
 PARTITION "P_MAR2017" VALUES LESS THAN (TO_DATE(' 2017-04-01 00:00:00', 'SYYYY
 -MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED
 PCT FREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
 STORAGE(
 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 TABLESPACE "USERS" )

Thanks to Jonathan Lewis , who put a comment on rationale behind DBMS_METADATA not showing the interval partition. I am quoting his words.

“There is a simple rationale for this behaviour.

Imagine the only out-of-range data you’ve added is for December – Oracle will add the december partition but will not add the partitions for april through to november.
If dbms_metadata.get_ddl now created the DDL to create partitions with the three boundaries that existed the code would create three RANGE partitions, which means the december partition would cover april to december and act as the lower bound for the interval partitions, and you wouldn’t get (e.g.) a dedicated june partition created if you then inserted some data for June”

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s