MAX \ud30c\ud2f0\uc158\uc744 \uc0ac\uc6a9\ud558\uba74 \uc778\ub371\uc2a4\ub3c4 \ub9ac\ube4c\ub4dc\ud574\uc57c\ud558\uace0, SPLIT\ud574\uc57c \ud558\uace0.. \uace8\uce58\uc544\ud515\ub2c8\ub2e4.<\/p>\n
\uc0c8\ub85c\uc6b4 partition\uc744 add\ub9cc \ud558\ub294 \ubc29\uc2dd\uc73c\ub85c\uc758 \uc804\ud658\uc774 \ud544\uc694\ud569\ub2c8\ub2e4.<\/p>\n
\uc77c\ubcc4\ub85c \uc544\ub798 \ud504\ub85c\uc2dc\uc838\ub97c crontab\uc5d0\uc11c \ud638\ucd9c\ub9cc \ud558\uba74 \uc544~\uc8fc \uac04\ub2e8\ud558\uac8c \uc77c\ubcc4 \ud30c\ud2f0\uc158\uc744 \uad00\ub9ac\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n
\uac00\ub054 \uc0dd\uc131\uc774 \uc2e4\ud328\ub420 \ub54c\uac00 \uc788\ub294\ub370 \uc774\uac83\uae4c\uc9c0 \uace0\ub824\ud574\uc11c. \uc2dc\uac04\ub2e8\uc704\ub85c \uc544\ub798 \ud504\ub85c\uc2dc\uc838\ub97c \ub3cc\ub9ac\uba74 \uc54c\uc544\uc11c \uc624\ub298\ub85c\ubd80\ud130 10\uc77c\uc774\ud6c4\uae4c\uc9c0, <\/p>\n
\uc624\ub298\ub85c\ubd80\ud130 15\uc77c \uc774\uc804\uae4c\uc9c0 \ucd1d 25\uc77c\uc758 \ud30c\ud2f0\uc158\uc744 \ud56d\uc0c1 \uc720\uc9c0\uc2dc\ucf1c\uc90d\ub2c8\ub2e4.<\/p>\n
<\/p>\n
CREATE OR REPLACE procedure proc_partitions<\/p>\n
(<\/p>\n
out_resultcode out varchar2, — \uc791\uc5c5\uacb0\uacfc \ucf54\ub4dc.<\/p>\n
out_sqlerrmsg out varchar2 — \uc624\ub958\uc2dc oracle \uc5d0\ub7ec\ucf54\ub4dc\/\uba54\uc2dc\uc9c0.<\/p>\n
)<\/p>\n
is<\/p>\n
\/*************************************************************************************\/<\/p>\n
\/* \ud30c\ud2f0\uc154\ub2dd\ub41c \ud14c\uc774\ube14\ub4e4\uc744 \ubd88\ub7ec\uc640\uc11c SPLIT SCRIPT\ub97c \ub9cc\ub4e4\uace0 \uc774\ub97c \ucee4\uc11c\uc5d0 \uc800\uc7a5\ud55c\ub2e4 *\/<\/p>\n
\/*************************************************************************************\/<\/p>\n
cursor c_part_tables<\/p>\n
is<\/p>\n
select * from (<\/p>\n
select table_name, <\/p>\n
decode(ordval, 1, ‘alter table ‘||table_name||’ add partition P’||npart||’ VALUES LESS THAN (”’||ndt||”’)’) addscript, <\/p>\n
‘alter table ‘||table_name||’ drop partition P’||bdt||decode(table_name,’WORKLIST’,’ update global indexes’) dropscript, ndt, bdt, npart<\/p>\n
from( <\/p>\n
select table_name, len, decode(len,8,TO_CHAR(to_date(replace(max(partition_name),’P’,”),’yyyymmdd’)+1,’yyyymmdd’)) npart, <\/p>\n
decode(len,8,TO_CHAR(to_date(replace(max(partition_name),’P’,”),’yyyymmdd’)+2,’yyyymmdd’)) ndt,<\/p>\n
decode(len,8,TO_CHAR(to_date(replace(max(partition_name),’P’,”),’yyyymmdd’)-23,’yyyymmdd’)) bdt,<\/p>\n
row_number() over (partition by table_name, len order by decode(len,8,TO_CHAR(to_date(replace(partition_name,’P’,”),’yyyymmdd’)-10,’yyyymmdd’)) desc) ordval <\/p>\n
from ( <\/p>\n
select table_name, length(replace(partition_name,’P’,”)) len,<\/p>\n
partition_name <\/p>\n
from user_tab_partitions <\/p>\n
where length(replace(partition_name,’P’,”)) is not null<\/p>\n
and partition_name like ‘P2%’<\/p>\n
)<\/p>\n
where len = 8<\/p>\n
group by table_name, len, decode(len,8,TO_CHAR(to_date(replace(partition_name,’P’,”),’yyyymmdd’)-10,’yyyymmdd’)) <\/p>\n
) <\/p>\n
where ndt < to_char(sysdate+10,’yyyymmdd’) <\/p>\n
) ;<\/p>\n
<\/p>\n
v_sql varchar2(4000); <\/p>\n
<\/p>\n
begin<\/p>\n
for c_part in c_part_tables()<\/p>\n
loop<\/p>\n
<\/p>\n
begin<\/p>\n
v_sql := c_part.addscript;<\/p>\n
execute immediate v_sql;<\/p>\n
exception<\/p>\n
when others then<\/p>\n
v_sql := ”; <\/p>\n
end; <\/p>\n
begin <\/p>\n
v_sql := c_part.dropscript;<\/p>\n
execute immediate v_sql;<\/p>\n
exception<\/p>\n
when others then<\/p>\n
v_sql := ”; <\/p>\n
end; <\/p>\n
<\/p>\n
end loop; <\/p>\n
<\/p>\n
exception<\/p>\n
when others then<\/p>\n
out_resultcode := -1;<\/p>\n
out_sqlerrmsg := sqlerrm||’ (‘||sqlcode||’)’;<\/p>\n
end proc_partitions;<\/p>\n
\/<\/p>\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
MAX \ud30c\ud2f0\uc158\uc744 \uc0ac\uc6a9\ud558\uba74 \uc778\ub371\uc2a4\ub3c4 \ub9ac\ube4c\ub4dc\ud574\uc57c\ud558\uace0, SPLIT\ud574\uc57c \ud558\uace0.. \uace8\uce58\uc544\ud515\ub2c8\ub2e4. \uc0c8\ub85c\uc6b4 partition\uc744 add\ub9cc \ud558\ub294 \ubc29\uc2dd\uc73c\ub85c\uc758 \uc804\ud658\uc774 \ud544\uc694\ud569\ub2c8\ub2e4. \uc77c\ubcc4\ub85c \uc544\ub798 \ud504\ub85c\uc2dc\uc838\ub97c crontab\uc5d0\uc11c \ud638\ucd9c\ub9cc \ud558\uba74 \uc544~\uc8fc \uac04\ub2e8\ud558\uac8c \uc77c\ubcc4 \ud30c\ud2f0\uc158\uc744 \uad00\ub9ac\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4. \uac00\ub054 \uc0dd\uc131\uc774 \uc2e4\ud328\ub420 \ub54c\uac00 \uc788\ub294\ub370 \uc774\uac83\uae4c\uc9c0 \uace0\ub824\ud574\uc11c. \uc2dc\uac04\ub2e8\uc704\ub85c \uc544\ub798 \ud504\ub85c\uc2dc\uc838\ub97c \ub3cc\ub9ac\uba74 \uc54c\uc544\uc11c \uc624\ub298\ub85c\ubd80\ud130 10\uc77c\uc774\ud6c4\uae4c\uc9c0, \uc624\ub298\ub85c\ubd80\ud130 15\uc77c \uc774\uc804\uae4c\uc9c0 \ucd1d 25\uc77c\uc758 \ud30c\ud2f0\uc158\uc744 \ud56d\uc0c1 \uc720\uc9c0\uc2dc\ucf1c\uc90d\ub2c8\ub2e4. CREATE OR REPLACE procedure proc_partitions […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[3],"tags":[312,313,314,315,316,317],"jetpack_featured_media_url":"","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/653"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=653"}],"version-history":[{"count":0,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/653\/revisions"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=653"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=653"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=653"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}