GET_DDLoracle间隔分区表，无法得到新分区的信息

问题

GET_DDL oracle间隔分区表，无法得到插入新数据后，产生的新的分区信息。

参考

“ DBMS_METADATA.GET_DDL”不显示间隔分区表和索引的新创建的间隔分区（文档ID 1491820.1）

详细信息

SYMPTOMS
“DBMS_METADATA.GET_DDL” Does Not Show Interval Partitions created by the system for interval partitioned tables and indexes.
In the case of Interval Partitioning, New Partitions are created automatically when corresponding row is inserted. This newly created partition information will be displayed in “DBA_TAB_PARTITIONS” dictionary view. However when the DDL is queried using function “DBMS_METADATA.GET_DDL”, then this information is not shown.
CREATE TABLE (COL1 DATE,COL2 NUMBER)
PARTITION BY RANGE (COL1) INTERVAL (NUMTOYMINTERVAL(1,”MONTH”))
(PARTITION P1 VALUES LESS THAN (TO_DATE(” 2006-02-01 00:00:00″, “SYYYY-MM-DD HH24:MI:SS”, “NLS_CALENDAR=GREGORIAN”))
);
— this will go to the predefined range partition P1
INSERT INTO VALUES (TO_DATE(“2006-01-01”, “YYYY-MM-DD”),1);
— Oracle will create a new interval partition for this row
INSERT INTO VALUES (TO_DATE(“2006-02-01”, “YYYY-MM-DD”),2);
— Oracle will create another interval partition for this row
INSERT INTO VALUES (TO_DATE(“2006-03-01”, “YYYY-MM-DD”),3);
COMMIT;
— Now, check partition information in dictionary table
select partition_name from user_tab_partitions where table_name=”“;
For example output of the last query is the following, where it is shown that there are 2 new partitions (SYS_P7607, SYS_P7608) created automatically by Oracle.
PARTITION_NAME
————–
P_20060201
SYS_P7607
SYS_P7608
But when the DDL is queried using “DBMS_METADATA.GET_DDL” package then this newly created partition information is not shown.
set long 100000
set pagesize 50
col DDL format a120
SELECT DBMS_METADATA.GET_DDL(“TABLE” ,”“,”“) FROM DUAL;
Output:
CREATE TABLE ““.””
( “COL1” DATE,
“COL2” NUMBER
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “USERS”
PARTITION BY RANGE (“COL1″) INTERVAL (NUMTOYMINTERVAL(1,”MONTH”))
(PARTITION “P1″ VALUES LESS THAN (TO_DATE(” 2006-02-01 00:00:00″, “SYYYY-MM-DD HH24:MI:SS”, “NLS_CALENDAR=GREGORIAN”)) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “USERS” )
“DBMS_METADATA.GET_DDL” package behaves the same way for local indexes of Interval Partitioned tables as well.
SOLUTION
As Per Bug 10005550 this is expected behavior, because “DBMS_METADATA.GET_DDL” will provide the output as like how the object was created (manual/user DDL) initially. It does not include new partitions created by the system during data manipulation.
If you want to display the system generated partitions as part of the CREATE TABLE DDL, then set the EXPORT parameter of the dbms_metadata to true
e.g.
exec dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,”EXPORT”,true);
SELECT DBMS_METADATA.GET_DDL(“TABLE” ,”“,”“) FROM DUAL;