在Oracle中,我们可以根据自己的选择命名自动列表分区吗?

huangapple go评论106阅读模式
英文:

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

问题

我们可以根据用户的命名标准来命名自动列表分区吗?

我有一个表A,为它创建了一个自动分区。现在,当插入一个具有分区键的新行时,将创建一个新分区。但我希望可以根据我的选择来命名该分区,而不是使用Oracle定义的名称。

英文:

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.

答案1

得分: 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'));"
(创建了一个名为T21的表,其中包含了关于州的列表分区。)

"SELECT TABLE_NAME, PARTITION_NAME, High_value FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'T21';"
(查询表名为'T21'的表的分区名称和高值。)

"BEGIN
RenameListPartitions('T21');
END;"
(调用了名为RenameListPartitions的存储过程来重命名分区。)

"SELECT TABLE_NAME, PARTITION_NAME, High_value FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'T21';"
(再次查询表名为'T21'的表的分区名称和高值,以查看是否已重命名分区。)

英文:

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'

huangapple
  • 本文由 发表于 2023年6月6日 14:24:12
  • 转载请务必保留本文链接:https://go.coder-hub.com/76411933.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定