-- 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.
-- The default behavior of "DBMS_METADATA.GET_DDL" is that it 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.
Demo: (Following was tested on the Oracle 12c, and it should be valid for Oracle 11g too.)
-- Create table with interval partition.
CREATE TABLE mytabwithInterval
(mydate DATE,
mynum NUMBER)
PARTITION BY RANGE (mydate) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(PARTITION P_20150301 VALUES LESS THAN (TO_DATE(' 2015-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')));
-- Insert some data to generate interval partitions.
INSERT INTO mytabwithInterval VALUES (TO_DATE('2015-01-01', 'YYYY-MM-DD'),1);
INSERT INTO mytabwithInterval VALUES (TO_DATE('2015-02-01', 'YYYY-MM-DD'),2);
INSERT INTO mytabwithInterval VALUES (TO_DATE('2015-03-01', 'YYYY-MM-DD'),3);
INSERT INTO mytabwithInterval VALUES (TO_DATE('2015-04-01', 'YYYY-MM-DD'),3);
COMMIT;
-- check partition information in dictionary table
col partition_name format a20
select partition_name from user_tab_partitions where table_name='MYTABWITHINTERVAL';
-- To see default behavior of dbms_metadata:
set long 100000
set pagesize 50
col DDL format a120
SELECT DBMS_METADATA.GET_DDL('TABLE' ,'MYTABWITHINTERVAL','SYS') FROM DUAL;
-- To see it with export option:
exec dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'EXPORT',true);
SELECT DBMS_METADATA.GET_DDL('TABLE' ,'MYTABWITHINTERVAL','SYS') FROM DUAL;
OUTPUT:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> set lines 181
SQL> set pages 100
SQL> CREATE TABLE mytabwithInterval
(mydate DATE,
mynum NUMBER)
PARTITION BY RANGE (mydate) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(PARTITION P_20150301 VALUES LESS THAN (TO_DATE(' 2015-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')));
2 3 4 5
Table created.
SQL> INSERT INTO mytabwithInterval VALUES (TO_DATE('2015-01-01', 'YYYY-MM-DD'),1);
INSERT INTO mytabwithInterval VALUES (TO_DATE('2015-02-01', 'YYYY-MM-DD'),2);
INSERT INTO mytabwithInterval VALUES (TO_DATE('2015-03-01', 'YYYY-MM-DD'),3);
INSERT INTO mytabwithInterval VALUES (TO_DATE('2015-04-01', 'YYYY-MM-DD'),3);
COMMIT;
1 row created.
SQL> SQL>
1 row created.
SQL> SQL>
1 row created.
SQL> SQL>
1 row created.
SQL>
Commit complete.
SQL> col partition_name format a20
select partition_name from user_tab_partitions where table_name='MYTABWITHINTERVAL';SQL>
PARTITION_NAME
--------------------
P_20150301
SYS_P561
SYS_P562
SQL>
SQL>
SQL>
SQL> set long 100000
set pagesize 50
col DDL format a120
SELECT DBMS_METADATA.GET_DDL('TABLE' ,'MYTABWITHINTERVAL','SYS') FROM DUAL;
SQL> SQL> SQL> SQL>
DBMS_METADATA.GET_DDL('TABLE','MYTABWITHINTERVAL','SYS')
--------------------------------------------------------------------------------
CREATE TABLE "SYS"."MYTABWITHINTERVAL"
( "MYDATE" DATE,
"MYNUM" NUMBER
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM"
PARTITION BY RANGE ("MYDATE") INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(PARTITION "P_20150301" VALUES LESS THAN (TO_DATE(' 2015-03-01 00:00:00', 'SYY
YY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" )
SQL> SQL> exec dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'EXPORT',true);
SELECT DBMS_METADATA.GET_DDL('TABLE' ,'MYTABWITHINTERVAL','SYS') FROM DUAL;
PL/SQL procedure successfully completed.
SQL>
DBMS_METADATA.GET_DDL('TABLE','MYTABWITHINTERVAL','SYS')
--------------------------------------------------------------------------------
CREATE TABLE "SYS"."MYTABWITHINTERVAL"
( "MYDATE" DATE,
"MYNUM" NUMBER
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM"
PARTITION BY RANGE ("MYDATE") INTERVAL (NUMTOYMINTERVAL(1,'MONTH')) TRANSITION
("P_20150301")
(PARTITION "P_20150301" VALUES LESS THAN (TO_DATE(' 2015-03-01 00:00:00', 'SYY
YY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ,
PARTITION "SYS_P561" VALUES LESS THAN (TO_DATE(' 2015-04-01 00:00:00', 'SYYYY-
MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ,
PARTITION "SYS_P562" VALUES LESS THAN (TO_DATE(' 2015-05-01 00:00:00', 'SYYYY-
MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" )
Enjoy!!!
No comments:
Post a Comment