I have a table that is automatically partitioned by a column value list. Initially, I create the table with two values for this list so there are two partitions. Later someone adds a new value which causes Oracle to generate a new list partition.
The problem is that when I look at that SCRIPT for the table it only shows the two original partitions. It doesn't show the automatically generated partition.
If I use DBMS_METADATA.GET_DDL() function then all the partitions show up as expected.
Is there some reason why it is doing it this way?
And out of curiosity - How does Toad generate DDL? Some other way then the DBMS_METADATA package?
thanks,
Alan
Toad just runs SQL to select information from the data dictionary to generate DDL.
We don't use DBMS_METADATA.GET_DDL because it has too many limitations. There is no way we could have as many DDL options with DBMS_METADATA as we do on our own. Also, DBMS_METADATA doesn't exist on older versions of Oracle so we have to use our own code to extract DDL anyway.
You can control which partitions to extract (default, none, or all) here:
Personally, I think it's best to just create the default partitions. Oracle will create the other partitions as needed when data is inserted
Hi John,
What took you so long? I asked this question almost 3 minutes ago?
Just kidding, of course. Thanks for the lightening fast response !!!!
I should have mentioned that I am using Toad for Oracle 17.1. I check the script options and I do seem to have PARTITIONS ALL set. See attachment.
Hi Alan,
Lucky timing. 
I actually stopped working around 5:00 my time, but I just peeked into my office for something else and saw the message.
Can you send the script for your original table, and let me know what version of Oracle you are running?
You can email it to me if you'd rather not post it here. john.dorlon@quest.com
Thanks
John
Hi,
Here is a mini version of the table. Two manually created parititions and one created automatically by the 3rd insert statement:
CREATE TABLE alan(id NUMBER,
stat_code VARCHAR2(10))
PARTITION BY LIST(stat_code) AUTOMATIC
(PARTITION p_active VALUES ('active'),
PARTITION p_cancel VALUES ('cancel'))
ENABLE ROW MOVEMENT;
INSERT INTO alan VALUES (1, 'active');
INSERT INTO alan VALUES (2, 'cancel');
INSERT INTO alan VALUES (3, 'other');
COMMIT;
Here is the SCRIPT for the table from toad showing just 2 partitions without the automatically created partition:
CREATE TABLE ALAN
(
ID NUMBER,
STAT_CODE VARCHAR2(10 BYTE)
)
PARTITION BY LIST (STAT_CODE) AUTOMATIC
(
PARTITION P_ACTIVE VALUES ('active'),
PARTITION P_CANCEL VALUES ('cancel')
)
ENABLE ROW MOVEMENT;
And here is the script from the DBMS_METADATA.GET_DDL('TABLE', 'ALAN') function (ad nauseam):
CREATE TABLE "AROSENTHAL"."ALAN"("ID" NUMBER,
"STAT_CODE" VARCHAR2(10))
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
STORAGE(BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
PARTITION BY LIST("STAT_CODE")
AUTOMATIC
(PARTITION "P_ACTIVE"
VALUES ('active')
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",
PARTITION "P_CANCEL"
VALUES ('cancel')
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",
PARTITION "SYS_P24056"
VALUES ('other')
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")
ENABLE ROW MOVEMENT
Thanks for the sample table. I can reproduce this and will fix.
What DBMS_METADATA is doing, specifying the name of the auto-created partition, is a bug. That ends up creating a manually created partition that has the same naming convention as an automatically created partition.
It should look like this:
CREATE TABLE LIST_PART_AUTO
(
ID NUMBER,
STAT_CODE VARCHAR2(10 BYTE)
)
NOCOMPRESS
PARTITION BY LIST (STAT_CODE) AUTOMATIC
(
PARTITION P_ACTIVE VALUES ('active')
LOGGING
NOCOMPRESS ,
PARTITION P_CANCEL VALUES ('cancel')
LOGGING
NOCOMPRESS,
PARTITION VALUES ('other')
LOGGING
NOCOMPRESS
)
NOCACHE
ENABLE ROW MOVEMENT;
Actually, the more I think about it - I don't think this is really a bug in Toad.
I think a lot of users don't want the auto-created partitions in their DDL. I mean, the whole point is that they are created as needed, right?
I'll add a new option in that dropdown for "All, including auto-created"
And I'm going to make that apply to range partitions too.
Hi John,
Yes, I can see your point about not wanting auto created partitions in the DDL. The problem I had was that I was given a question about what partitions were in the table and I looked at the DDL Script from Toad as a quick way to see. It was "missing" the partition that should have been automatically created and our query wasn't returning the specific row so I was thinking that it didn't get inserted which made no sense. When I looked at the partitions table I saw it and realized what had happened.
Only couple minutes of confusion which is typical in my work day 
Thanks !!!
Alan
Ah, ok.
Yeah the "Partitions" tab is the best/quickest way to get that info.
