ADD_PART_SUBPART_.sql

CREATE OR REPLACE PROCEDURE ADD_PART_SUBPART_ (
v_period   IN VARCHAR2,
 t_name       IN   VARCHAR2,
 v_part_no      IN   NUMBER)
 IS
v_dyntask           LONG;
 xx                  VARCHAR2(100);
 xa                  NUMBER;
 pi                  VARCHAR2(100);
 v_count             NUMBER;
 pi_digit            NUMBER;

-- oeripek 04.04.2013

BEGIN
IF t_name='*'  THEN
FOR i IN (SELECT TABLE_NAME AS v_t_name  FROM USER_TABLES
 WHERE  TABLE_NAME IN ( 'TABLO1','TABLO2'))

LOOP
 IF i.v_t_name ='TABLO1' THEN
FOR v_last_digit IN 0 .. 9
LOOP
SELECT COUNT(1) INTO v_count FROM USER_TAB_PARTITIONS
 WHERE  TABLE_NAME='TABLO1' and PARTITION_NAME='PART_'||v_period||'_'||
v_last_digit||'_'||TO_CHAR (v_part_no)||'';

IF   v_count > 0 THEN
raise_application_error (-20002, 'TABLO1 tablosunun PART_'||v_period||'_'||
v_last_digit||'_'|| TO_CHAR (v_part_no)||'  partition''ı zaten var!');

ELSE
 EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DDL';
 EXECUTE IMMEDIATE 'SELECT CASE WHEN '||v_last_digit||'=''9'' THEN '||'''Z'''||
' ELSE TRIM (('||v_last_digit||')) END
 FROM DUAL'  INTO xx;
 IF v_last_digit+1 <> '10' THEN

v_dyntask := 'ALTER TABLE TABLO1
 SPLIT PARTITION PART_'||LPAD (TO_NUMBER(v_period),2,0)||'_'||
TO_NUMBER(v_last_digit+1)||'_'||'0'||'
 AT  ('''||LPAD (TO_NUMBER(v_period),2,0)||''', '''||xx||''', '''||
TO_CHAR (v_part_no+1)||''')
 INTO (PARTITION PART_'||LPAD (TO_NUMBER(v_period),2,0)||'_'||v_last_digit||
'_'||TO_CHAR(v_part_no)||',
 PARTITION PART_'||LPAD (TO_NUMBER(v_period),2,0)||'_'||TO_NUMBER(v_last_digit+1)
||'_'||'0'||')';

--dbms_output.put_line(v_dyntask);
 EXECUTE IMMEDIATE v_dyntask;

ELSE
IF v_period='53' THEN
 v_dyntask := 'ALTER TABLE TABLO1 ADD
 PARTITION PART_'||v_period||'_'||v_last_digit||'_'||TO_CHAR(v_part_no)||
 ' VALUES LESS THAN ('''||v_period||''', '''|| xx||''', '''|| TO_CHAR (v_part_no)
||''')
 NOLOGGING
 NOCOMPRESS
 PCTFREE    10
 INITRANS   1
 MAXTRANS   255
 STORAGE    (
 BUFFER_POOL      DEFAULT
 FLASH_CACHE      DEFAULT
 CELL_FLASH_CACHE DEFAULT
 )
 (SUBPARTITION PART_'||v_period||'_'||v_last_digit||'_'||TO_CHAR (v_part_no)
||'_PART_00_09 VALUES (''00'', ''01'', ''02'', ''03'', ''04'', ''05'', ''06'',
 ''07'', ''08'', ''09''),
 SUBPARTITION PART_'||v_period||'_'||v_last_digit||'_'||TO_CHAR (v_part_no)
||'_PART_10_19 VALUES (''10'', ''11'', ''12'', ''13'', ''14'', ''15'', ''16'',
 ''17'', ''18'', ''19''),
 SUBPARTITION PART_'||v_period||'_'||v_last_digit||'_'||TO_CHAR (v_part_no)
||'_PART_20_29 VALUES (''20'', ''21'', ''22'', ''23'', ''24'', ''25'', ''26'',
 ''27'', ''28'', ''29''),
 SUBPARTITION PART_'||v_period||'_'||v_last_digit||'_'||TO_CHAR (v_part_no)
||'_PART_30_39 VALUES (''30'', ''31'', ''32'', ''33'', ''34'', ''35'', ''36'',
 ''37'', ''38'', ''39''),
 SUBPARTITION PART_'||v_period||'_'||v_last_digit||'_'||TO_CHAR (v_part_no)
||'_PART_40_49 VALUES (''40'', ''41'', ''42'', ''43'', ''44'', ''45'', ''46'',
 ''47'', ''48'', ''49''),
 SUBPARTITION PART_'||v_period||'_'||v_last_digit||'_'||TO_CHAR (v_part_no)
||'_PART_50_59 VALUES (''50'', ''51'', ''52'', ''53'', ''54'', ''55'', ''56'',
 ''57'', ''58'', ''59''),
 SUBPARTITION PART_'||v_period||'_'||v_last_digit||'_'||TO_CHAR (v_part_no)
||'_PART_60_69 VALUES (''60'', ''61'', ''62'', ''63'', ''64'', ''65'', ''66'',
 ''67'', ''68'', ''69''),
 SUBPARTITION PART_'||v_period||'_'||v_last_digit||'_'||TO_CHAR (v_part_no)
||'_PART_70_79 VALUES (''70'', ''71'', ''72'', ''73'', ''74'', ''75'', ''76'',
 ''77'', ''78'', ''79''),
 SUBPARTITION PART_'||v_period||'_'||v_last_digit||'_'||TO_CHAR (v_part_no)
||'_PART_80_89 VALUES (''80'', ''81'', ''82'', ''83'', ''84'', ''85'', ''86'',
 ''87'', ''88'', ''89''),
 SUBPARTITION PART_'||v_period||'_'||v_last_digit||'_'||TO_CHAR (v_part_no)
||'_PART_90_99 VALUES (''90'', ''91'', ''92'', ''93'', ''94'', ''95'', ''96'',
 ''97'', ''98'', ''99''))';

EXECUTE IMMEDIATE v_dyntask;
ELSE
EXECUTE IMMEDIATE 'SELECT CASE WHEN '||v_period||'=''01''  THEN 02
 WHEN '||v_period||'=''02''  THEN 03
 WHEN '||v_period||'=''03''  THEN 04
 WHEN '||v_period||'=''04''  THEN 11
 WHEN '||v_period||'=''11''  THEN 32
 WHEN '||v_period||'=''32''  THEN 50
 WHEN '||v_period||'=''50''  THEN 51
 WHEN '||v_period||'=''51''  THEN 52
 WHEN '||v_period||'=''52''  THEN 53
 ELSE ('||v_period||') END FROM DUAL'  INTO xa;

v_dyntask := 'ALTER TABLE TABLO1
 SPLIT PARTITION PART_'||LPAD (TO_NUMBER(xa),2,0)||'_'||'0'||'_'||'0'||'
 AT  ('''||LPAD (TO_NUMBER(v_period),2,0)||''', '''||xx||''', '''||TO_CHAR 
(v_part_no+1)||''')
 INTO (PARTITION PART_'||LPAD (TO_NUMBER(v_period),2,0)||'_'||v_last_digit
||'_'||TO_CHAR(v_part_no)||',
 PARTITION PART_'||LPAD (TO_NUMBER(xa),2,0)||'_'||'0'||'_'||'0'||')';

--dbms_output.put_line(v_dyntask);
 EXECUTE IMMEDIATE v_dyntask;

END IF;
END IF;

FOR i IN (select PARTITION_NAME,SUBPARTITION_NAME,SUBPARTITION_POSITION from
 user_tab_subpartitions  where table_name='TABLO1'
 AND SUBPARTITION_NAME LIKE '%SYS%')

LOOP
IF substr(i.PARTITION_NAME,8,6)=v_period||'_'||v_last_digit||'_'||TO_CHAR
 (v_part_no) and i.SUBPARTITION_POSITION='1' THEN
EXECUTE IMMEDIATE 'ALTER TABLE TABLO1 RENAME SUBPARTITION  '||i.SUBPARTITION_NAME
||' TO PART_'||v_period||'_'||v_last_digit||'_'||TO_CHAR (v_part_no)
||'_PART_00_09';

EXECUTE IMMEDIATE 'ALTER INDEX IX_TABLO1_01 RENAME SUBPARTITION  '||
 i.SUBPARTITION_NAME|| ' TO PART_'|| v_period|| '_'|| v_last_digit|| '_'||
 TO_CHAR (v_part_no)|| '_PART_00_09';

EXECUTE IMMEDIATE 'ALTER INDEX IX_TABLO1_01 REBUILD SUBPARTITION PART_'||
 v_period|| '_'|| v_last_digit|| '_'|| TO_CHAR (v_part_no)||
 '_PART_00_09  PARALLEL 8';

ELSIF substr(i.PARTITION_NAME,8,6)=v_period||'_'||v_last_digit||'_'||
TO_CHAR (v_part_no) and i.SUBPARTITION_POSITION='2' THEN

EXECUTE IMMEDIATE 'ALTER TABLE TABLO1 RENAME SUBPARTITION  '||
i.SUBPARTITION_NAME||' TO PART_'||v_period||'_'||v_last_digit||'_'||
TO_CHAR (v_part_no)||'_PART_10_19';

EXECUTE IMMEDIATE 'ALTER INDEX IX_TABLO1_01 RENAME SUBPARTITION  '||
 i.SUBPARTITION_NAME|| ' TO PART_'|| v_period|| '_'|| v_last_digit|| '_'||
 TO_CHAR (v_part_no)|| '_PART_10_19';

EXECUTE IMMEDIATE 'ALTER INDEX IX_TABLO1_01 REBUILD SUBPARTITION PART_'||
 v_period|| '_'|| v_last_digit|| '_'|| TO_CHAR (v_part_no)||
 '_PART_10_19  PARALLEL 8';

ELSIF substr(i.PARTITION_NAME,8,6)=v_period||'_'||v_last_digit||'_'||
TO_CHAR (v_part_no) and i.SUBPARTITION_POSITION='3' THEN

EXECUTE IMMEDIATE 'ALTER TABLE TABLO1 RENAME SUBPARTITION  '||
i.SUBPARTITION_NAME||' TO PART_'||v_period||'_'||v_last_digit||'_'||
TO_CHAR (v_part_no)||'_PART_20_29';

EXECUTE IMMEDIATE 'ALTER INDEX IX_TABLO1_01 RENAME SUBPARTITION  '||
 i.SUBPARTITION_NAME|| ' TO PART_'|| v_period|| '_'|| v_last_digit|| '_'||
 TO_CHAR (v_part_no)|| '_PART_20_29';

EXECUTE IMMEDIATE 'ALTER INDEX IX_TABLO1_01 REBUILD SUBPARTITION PART_'||
 v_period|| '_'|| v_last_digit|| '_'|| TO_CHAR (v_part_no)||
 '_PART_20_29  PARALLEL 8';

ELSIF substr(i.PARTITION_NAME,8,6)=v_period||'_'||v_last_digit||'_'||
TO_CHAR (v_part_no) and i.SUBPARTITION_POSITION='4' THEN

EXECUTE IMMEDIATE 'ALTER TABLE TABLO1 RENAME SUBPARTITION  '||
i.SUBPARTITION_NAME||' TO PART_'||v_period||'_'||v_last_digit||'_'||
TO_CHAR (v_part_no)||'_PART_30_39';

EXECUTE IMMEDIATE 'ALTER INDEX IX_TABLO1_01 RENAME SUBPARTITION  '||
 i.SUBPARTITION_NAME|| ' TO PART_'|| v_period|| '_'|| v_last_digit|| '_'||
 TO_CHAR (v_part_no)|| '_PART_30_39';

EXECUTE IMMEDIATE 'ALTER INDEX IX_TABLO1_01 REBUILD SUBPARTITION PART_'||
 v_period|| '_'|| v_last_digit|| '_'|| TO_CHAR (v_part_no)||
 '_PART_30_39  PARALLEL 8';

ELSIF substr(i.PARTITION_NAME,8,6)=v_period||'_'||v_last_digit||'_'||
TO_CHAR (v_part_no) and i.SUBPARTITION_POSITION='5' THEN

EXECUTE IMMEDIATE 'ALTER TABLE TABLO1 RENAME SUBPARTITION  '||
i.SUBPARTITION_NAME||' TO PART_'||v_period||'_'||v_last_digit||'_'||
TO_CHAR (v_part_no)||'_PART_40_49';

EXECUTE IMMEDIATE 'ALTER INDEX IX_TABLO1_01 RENAME SUBPARTITION  '||
 i.SUBPARTITION_NAME|| ' TO PART_'|| v_period|| '_'|| v_last_digit|| '_'||
 TO_CHAR (v_part_no)|| '_PART_40_49';

EXECUTE IMMEDIATE 'ALTER INDEX IX_TABLO1_01 REBUILD SUBPARTITION PART_'||
 v_period|| '_'|| v_last_digit|| '_'|| TO_CHAR (v_part_no)||
 '_PART_40_49  PARALLEL 8';

ELSIF substr(i.PARTITION_NAME,8,6)=v_period||'_'||v_last_digit||'_'||
TO_CHAR (v_part_no) and i.SUBPARTITION_POSITION='6' THEN

EXECUTE IMMEDIATE 'ALTER TABLE TABLO1 RENAME SUBPARTITION  '||
i.SUBPARTITION_NAME||' TO PART_'||v_period||'_'||v_last_digit||'_'||
TO_CHAR (v_part_no)||'_PART_50_59';

EXECUTE IMMEDIATE 'ALTER INDEX IX_TABLO1_01 RENAME SUBPARTITION  '||
 i.SUBPARTITION_NAME|| ' TO PART_'|| v_period|| '_'|| v_last_digit|| '_'||
 TO_CHAR (v_part_no)|| '_PART_50_59';

EXECUTE IMMEDIATE 'ALTER INDEX IX_TABLO1_01 REBUILD SUBPARTITION PART_'||
 v_period|| '_'|| v_last_digit|| '_'|| TO_CHAR (v_part_no)||
 '_PART_50_59  PARALLEL 8';

ELSIF substr(i.PARTITION_NAME,8,6)=v_period||'_'||v_last_digit||'_'||
TO_CHAR (v_part_no) and i.SUBPARTITION_POSITION='7' THEN

EXECUTE IMMEDIATE 'ALTER TABLE TABLO1 RENAME SUBPARTITION  '||
i.SUBPARTITION_NAME||' TO PART_'||v_period||'_'||v_last_digit||'_'||
TO_CHAR (v_part_no)||'_PART_60_69';

EXECUTE IMMEDIATE 'ALTER INDEX IX_TABLO1_01 RENAME SUBPARTITION  '||
 i.SUBPARTITION_NAME|| ' TO PART_'|| v_period|| '_'|| v_last_digit|| '_'||
 TO_CHAR (v_part_no)|| '_PART_60_69';

EXECUTE IMMEDIATE 'ALTER INDEX IX_TABLO1_01 REBUILD SUBPARTITION PART_'||
 v_period|| '_'|| v_last_digit|| '_'|| TO_CHAR (v_part_no)||
 '_PART_60_69  PARALLEL 8';

ELSIF substr(i.PARTITION_NAME,8,6)=v_period||'_'||v_last_digit||'_'||
TO_CHAR (v_part_no) and i.SUBPARTITION_POSITION='8' THEN

EXECUTE IMMEDIATE 'ALTER TABLE TABLO1 RENAME SUBPARTITION  '||
i.SUBPARTITION_NAME||' TO PART_'||v_period||'_'||v_last_digit||'_'||
TO_CHAR (v_part_no)||'_PART_70_79';

EXECUTE IMMEDIATE 'ALTER INDEX IX_TABLO1_01 RENAME SUBPARTITION  '||
 i.SUBPARTITION_NAME|| ' TO PART_'|| v_period|| '_'|| v_last_digit|| '_'||
 TO_CHAR (v_part_no)|| '_PART_70_79';

EXECUTE IMMEDIATE 'ALTER INDEX IX_TABLO1_01 REBUILD SUBPARTITION PART_'||
 v_period|| '_'|| v_last_digit|| '_'|| TO_CHAR (v_part_no)||
 '_PART_70_79  PARALLEL 8';

ELSIF substr(i.PARTITION_NAME,8,6)=v_period||'_'||v_last_digit||'_'||
TO_CHAR (v_part_no) and i.SUBPARTITION_POSITION='9' THEN

EXECUTE IMMEDIATE 'ALTER TABLE TABLO1 RENAME SUBPARTITION  '||
i.SUBPARTITION_NAME||' TO PART_'||v_period||'_'||v_last_digit||'_'||
TO_CHAR (v_part_no)||'_PART_80_89';

EXECUTE IMMEDIATE 'ALTER INDEX IX_TABLO1_01 RENAME SUBPARTITION  '||
 i.SUBPARTITION_NAME|| ' TO PART_'|| v_period|| '_'|| v_last_digit|| '_'||
 TO_CHAR (v_part_no)|| '_PART_80_89';

EXECUTE IMMEDIATE 'ALTER INDEX IX_TABLO1_01 REBUILD SUBPARTITION PART_'||
 v_period|| '_'|| v_last_digit|| '_'|| TO_CHAR (v_part_no)||
 '_PART_80_89  PARALLEL 8';

ELSIF substr(i.PARTITION_NAME,8,6)=v_period||'_'||v_last_digit||'_'||
TO_CHAR (v_part_no) and i.SUBPARTITION_POSITION='10' THEN

EXECUTE IMMEDIATE 'ALTER TABLE TABLO1 RENAME SUBPARTITION  '||
i.SUBPARTITION_NAME||' TO PART_'||v_period||'_'||v_last_digit||'_'||
TO_CHAR (v_part_no)||'_PART_90_99';

EXECUTE IMMEDIATE 'ALTER INDEX IX_TABLO1_01 RENAME SUBPARTITION  '||
 i.SUBPARTITION_NAME|| ' TO PART_'|| v_period|| '_'|| v_last_digit|| '_'||
 TO_CHAR (v_part_no)|| '_PART_90_99';

EXECUTE IMMEDIATE 'ALTER INDEX IX_TABLO1_01 REBUILD SUBPARTITION PART_'||
 v_period|| '_'|| v_last_digit|| '_'|| TO_CHAR (v_part_no)||
 '_PART_90_99  PARALLEL 8';

END IF;
END LOOP;
 --
 END IF;
END LOOP;

ELSIF  i.v_t_name ='TABLO2' THEN
SELECT COUNT(1) INTO v_count FROM USER_TAB_PARTITIONS
 WHERE  TABLE_NAME='TABLO2' and PARTITION_NAME='PART_'||v_period||'_'||
 TO_CHAR (v_part_no)||'';

IF   v_count > 0 THEN
raise_application_error (-20002, 'TABLO2 tablosunun PART_'||v_period||'_'||
 TO_CHAR (v_part_no)||' partition''ı zaten var!');

ELSE
EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DDL';
 IF v_period='53' THEN

v_dyntask := 'ALTER TABLE  TABLO2  ADD
 PARTITION PART_'||53||'_'||TO_CHAR(v_part_no)||
 ' VALUES LESS THAN ('''||54||''', '''||TO_CHAR (v_part_no)||''')
 NOLOGGING
 NOCOMPRESS
 PCTFREE    10
 INITRANS   1
 MAXTRANS   255
 STORAGE    (
 BUFFER_POOL      DEFAULT
 FLASH_CACHE      DEFAULT
 CELL_FLASH_CACHE DEFAULT
 )';

EXECUTE IMMEDIATE v_dyntask;

ELSE
EXECUTE IMMEDIATE 'SELECT CASE WHEN '||v_period||'=''01''  THEN 02
 WHEN '||v_period||'=''02''  THEN 03
 WHEN '||v_period||'=''03''  THEN 04
 WHEN '||v_period||'=''04''  THEN 11
 WHEN '||v_period||'=''11''  THEN 30
 WHEN '||v_period||'=''30''  THEN 32
 WHEN '||v_period||'=''32''  THEN 50
 WHEN '||v_period||'=''50''  THEN 51
 WHEN '||v_period||'=''51''  THEN 52
 WHEN '||v_period||'=''52''  THEN 53
 ELSE ('||v_period||') END FROM DUAL'  INTO xa;
 v_dyntask := 'ALTER TABLE TABLO2
 SPLIT PARTITION PART_'||LPAD (TO_NUMBER(xa),2,0)||'_'||'0'||'
 AT  ('''||LPAD (TO_NUMBER(v_period),2,0)||''', '''||TO_CHAR (v_part_no+1)||''')
 INTO (PARTITION PART_'||LPAD (TO_NUMBER(v_period),2,0)||'_'||TO_CHAR(v_part_no)
||',
 PARTITION PART_'||LPAD (TO_NUMBER(xa),2,0)||'_'||'0'||')';

--dbms_output.put_line(v_dyntask);
 EXECUTE IMMEDIATE v_dyntask;

END IF;
END IF;
END IF;
END LOOP;
ELSE
 raise_application_error (-20002, 'Tablolara Partition eklenmesi için gerekli
 parametre değeri girilmedi!');
END IF;

DBMS_OUTPUT.put_line ('Tüm Partition''lar eklenmiştir.');
 EXCEPTION
 WHEN OTHERS
 THEN
 raise_application_error ('-20009', SQLERRM);

END;
 /