英文:
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'
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论