Can we name an automatic list partition in Oracle as per our choice

Question

Can we name an automatic list partition as per user's naming critria?

I have a table A for which I have created a partition which is automatic. Now when a new row with partitioning key will be inserted a new partition will be created. But I want that partition to be named according to my choice and not an oracle defined name.

Answer 1

With help I created a procedure that renames system GENERATED PARTITION names to the high value.

In the example below I created a list PARTITION for states.

CREATE TABLE T21
(
      seq_num NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
      num NUMBER(*,0),
      state   VARCHAR2(20)
)
  SEGMENT CREATION DEFERRED
  PARTITION BY LIST (state) AUTOMATIC
 (PARTITION P_CALIFORNIA VALUES ('CALIFORNIA'));

insert into t21 (num, state)
select
    level * round(dbms_random.value(1,50)),
    case round(dbms_random.value(1,50))
        when   1 then 'Alabama'
        when   2 then 'Alaska'
        when   3 then 'Arizona'
        when   4 then 'Arkansas'
        when   5 then 'California'
        when   6 then 'Colorado'
        when   7 then 'Connecticut'
        when   8 then 'Delaware'
        when   9 then 'Florida'
        when  10 then 'Georgia'
        when  11 then 'Hawaii'
        when  12 then 'Idaho'
        when  13 then 'Illinois'
        when  14 then 'Indiana'
        when  15 then 'Iowa'
        when  16 then 'Kansas'
        when  17 then 'Kentucky'
        when  18 then 'Louisiana'
        when  19 then 'Maine'
        when  20 then 'Maryland'
        when  21 then 'Massachusetts'
        when  22 then 'Michigan'
        when  23 then 'Minnesota'
        when  24 then 'Mississippi'
        when  25 then 'Missouri'
        when  26 then 'Montana'
        when  27 then 'Nebraska'
        when  28 then 'Nevada'
        when  29 then 'New Hampshire'
        when  30 then 'New Jersey'
        when  31 then 'New Mexico'
        when  32 then 'New York'
        when  33 then 'North Carolina'
        when  34 then 'North Dakota'
        when  35 then 'Ohio'
        when  36 then 'Oklahoma'
        when  37 then 'Oregon'
        when  38 then 'Pennsylvania'
        when  39 then 'Rhode Island'
        when  40 then 'South Carolina'
        when  41 then 'South Dakota'
        when  42 then 'Tennessee'
        when  43 then 'Texas'
        when  44 then 'Utah'
        when  45 then 'Vermont'
        when  46 then 'Virginia'
        when  47 then 'Washington'
        when  48 then 'West Virginia'
        when  49 then 'Wisconsin'
        when  50 then 'Wyoming'
    end
from dual
connect by level <= 100;


SELECT TABLE_NAME, PARTITION_NAME, High_value FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'T21';

PARTITION_NAME  HIGH_VALUE
T21 SYS_P3740   'Montana'
T21 SYS_P3739   'Indiana'
T21 SYS_P3741   'Massachusetts'
T21 SYS_P3742   'New York'
T21 SYS_P3743   'Connecticut'
T21 SYS_P3744   'Florida'
T21 SYS_P3745   'Wisconsin'
T21 SYS_P3746   'Virginia'
T21 SYS_P3747   'Nebraska'
T21 SYS_P3748   'Colorado'
T21 SYS_P3749   'Nevada'
T21 SYS_P3751   'Kentucky'
T21 SYS_P3750   'New Mexico'
T21 SYS_P3752   'Ohio'
T21 SYS_P3753   'Arkansas'
T21 SYS_P3754   'Maryland'
T21 SYS_P3755   'New Hampshire'
T21 SYS_P3756   'Pennsylvania'
T21 SYS_P3757   'Illinois'
T21 SYS_P3758   'Vermont'
T21 SYS_P3759   'Kansas'
T21 SYS_P3760   'Utah'
T21 SYS_P3761   'New Jersey'
T21 SYS_P3762   'Michigan'
T21 SYS_P3763   'Washington'
T21 P_CALIFORNIA    'CALIFORNIA'


CREATE OR REPLACE PROCEDURE RenameListPartitions(p_TableName varchar2)
IS

    CURSOR PartTables IS
    SELECT TABLE_NAME
    FROM USER_PART_TABLES
    WHERE PARTITIONING_TYPE = 'LIST'
      AND TABLE_NAME = p_TableName
    ORDER BY TABLE_NAME;

    CURSOR TabParts(aTableName VARCHAR2) IS
    SELECT PARTITION_NAME, HIGH_VALUE
    FROM USER_TAB_PARTITIONS
    WHERE regexp_like(partition_name,'^SYS_P[[:digit:]]{1,10}')
      AND TABLE_NAME = aTableName
      AND table_name not like 'BIN$%'
    ORDER BY PARTITION_POSITION;

  newPartName VARCHAR2(128);
 
BEGIN

    FOR aTab IN PartTables LOOP
        FOR aPart IN TabParts(aTab.TABLE_NAME) LOOP
            execute immediate 'select ' || aPart.HIGH_VALUE || ' from dual' into newPartName;

            IF newPartName <> aPart.PARTITION_NAME THEN
                EXECUTE IMMEDIATE 'ALTER TABLE '||aTab.TABLE_NAME||' RENAME PARTITION '||aPart.PARTITION_NAME||' TO "'||newPartName||'"';
            END IF;
        END LOOP;
    END LOOP;

END RenameListPartitions;
/

BEGIN
    RenameListPartitions('T21');
END;
/

SELECT TABLE_NAME, PARTITION_NAME, High_value FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'T21';

TABLE_NAME  PARTITION_NAME  HIGH_VALUE
T21 Alaska  'Alaska'
T21 California  'California'
T21 Colorado    'Colorado'
T21 Connecticut 'Connecticut'
T21 Delaware    'Delaware'
T21 Georgia 'Georgia'
T21 Hawaii  'Hawaii'
T21 Idaho   'Idaho'
T21 Illinois    'Illinois'
T21 Indiana 'Indiana'
T21 Iowa    'Iowa'
T21 Kansas  'Kansas'
T21 Kentucky    'Kentucky'
T21 Louisiana   'Louisiana'
T21 Maine   'Maine'
T21 Maryland    'Maryland'
T21 Massachusetts   'Massachusetts'
T21 Minnesota   'Minnesota'
T21 Missouri    'Missouri'
T21 Montana 'Montana'
T21 Nebraska    'Nebraska'
T21 Nevada  'Nevada'
T21 New Hampshire   'New Hampshire'
T21 New Jersey  'New Jersey'
T21 New Mexico  'New Mexico'
T21 New York    'New York'
T21 North Carolina  'North Carolina'
T21 North Dakota    'North Dakota'
T21 Ohio    'Ohio'
T21 P_CALIFORNIA    'CALIFORNIA'
T21 Pennsylvania    'Pennsylvania'
T21 South Carolina  'South Carolina'
T21 South Dakota    'South Dakota'
T21 Tennessee   'Tennessee'
T21 Texas   'Texas'
T21 Utah    'Utah'
T21 Vermont 'Vermont'
T21 Virginia    'Virginia'
T21 Washington  'Washington'
T21 West Virginia   'West Virginia'
T21 Wisconsin   'Wisconsin'