I have several environments where automatic list partitioning is implemented.

Some product managers don't like the system GENERATED partition name, which is being GENERATED and want to reference the partition by a more meaningful name.

In an effort to address those concerns I'm trying to create a procedure, which will rename the partition based on the high_value. In the example below I am partitioning by state so the names I want to generate are P_OHIO, P_IOWA..

In the procedure below I am getting the following error:

ORA-14048: a partition maintenance operation may not be combined with other operations ORA-06512:

Can someone please advise me on why I am getting this error and how to rectify it.

Thanks in advance for your help and expertise.

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';

CREATE OR REPLACE PROCEDURE RenameListPartitions
 IS  

    CURSOR PartTables IS
    SELECT TABLE_NAME
    FROM USER_PART_TABLES 
    WHERE PARTITIONING_TYPE = 'LIST' 
    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(30);
 
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;
/

EXEC RenameListPartitions;

CodePudding user response：

Some of your partition values have spaces - like 'New York' - so you need to quote the identifiers for the partitions:

EXECUTE IMMEDIATE 'ALTER TABLE '||aTab.TABLE_NAME
  ||' RENAME PARTITION '||aPart.PARTITION_NAME
  ||' TO "'||newPartName||'"';

db<>fiddle

Of course, that means you also need to quote them when you refer to them explicitly in the future; it might be better to, say, replace spaces with underscores and change to uppercase, so you get NEW_YORK:

EXECUTE IMMEDIATE 'ALTER TABLE '||aTab.TABLE_NAME
  ||' RENAME PARTITION '||aPart.PARTITION_NAME
  ||' TO '||upper(replace(newPartName,' ','_'));

db<>fiddle