alter table my_table add partition and subpartition

create table deneme (part varchar2(4000))

/* Formatted on 19/03/2013 14:03:41 (QP5 v5.240.12305.39446) */
DECLARE
v_dyntask LONG;
var VARCHAR2 (4000);
BEGIN
FOR cur1 IN (SELECT ’01’ prt FROM DUAL
UNION ALL
SELECT ’02’ prt FROM DUAL
UNION ALL
SELECT ’03’ prt FROM DUAL
UNION ALL
SELECT ’04’ prt FROM DUAL
UNION ALL
SELECT ’11’ prt FROM DUAL
UNION ALL
SELECT ’30’ prt FROM DUAL
UNION ALL
SELECT ’31’ prt FROM DUAL
UNION ALL
SELECT ’32’ prt FROM DUAL
UNION ALL
SELECT ’50’ prt FROM DUAL
UNION ALL
SELECT ’51’ prt FROM DUAL
UNION ALL
SELECT ’52’ prt FROM DUAL
UNION ALL
SELECT ’53’ prt FROM DUAL)
LOOP
FOR CUR2
IN (SELECT prt, (CASE
WHEN prt = ‘9’ THEN ‘Z’
ELSE TO_CHAR (TO_NUMBER (prt) + 1)
END) var
FROM (SELECT ‘0’ prt FROM DUAL
UNION ALL
SELECT ‘1’ prt FROM DUAL
UNION ALL
SELECT ‘2’ prt FROM DUAL
UNION ALL
SELECT ‘3’ prt FROM DUAL
UNION ALL
SELECT ‘4’ prt FROM DUAL
UNION ALL
SELECT ‘5’ prt FROM DUAL
UNION ALL
SELECT ‘6’ prt FROM DUAL
UNION ALL
SELECT ‘7’ prt FROM DUAL
UNION ALL
SELECT ‘8’ prt FROM DUAL
UNION ALL
SELECT ‘9’ prt FROM DUAL))
LOOP
FOR cur3 IN(
SELECT ‘0’ prt FROM DUAL
UNION ALL
SELECT ‘1’ prt FROM DUAL
UNION ALL
SELECT ‘2’ prt FROM DUAL
UNION ALL
SELECT ‘3’ prt FROM DUAL
UNION ALL
SELECT ‘4’ prt FROM DUAL
UNION ALL
SELECT ‘5’ prt FROM DUAL
UNION ALL
SELECT ‘6’ prt FROM DUAL
UNION ALL
SELECT ‘7’ prt FROM DUAL
UNION ALL
SELECT ‘8’ prt FROM DUAL
UNION ALL
SELECT ‘9’ prt FROM DUAL

)

LOOP
— dbms_output.put_line(cur1.prt||’_’||cur2.var||’_’||cur3.prt);
v_dyntask := ‘ALTER TABLE MY_TABLE ADD
PARTITION INVOICEPRINT_CDR_’||cur1.prt||’_’||cur2.prt||’_’||cur3.prt||’
VALUES LESS THAN (‘||cur1.prt||’, ‘||cur2.var||’, ‘||cur3.prt||’)
NOLOGGING
NOCOMPRESS
TABLESPACE SAMPLE
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
( SUBPARTITION CDR_’||cur1.prt||’_’||cur2.prt||’_’||cur3.prt||’_CDR_00_09 VALUES (”00”, ”01”, ”02”, ”03”, ”04”, ”05”, ”06”, ”07”, ”08”, ”09”) TABLESPACE SAMPLE,
SUBPARTITION CDR_’||cur1.prt||’_’||cur2.prt||’_’||cur3.prt||’_CDR_10_19 VALUES (”10”, ”11”, ”12”, ”13”, ”14”, ”15”, ”16”, ”17”, ”18”, ”19”) TABLESPACE SAMPLE,
SUBPARTITION CDR_’||cur1.prt||’_’||cur2.prt||’_’||cur3.prt||’_CDR_20_29 VALUES (”20”, ”21”, ”22”, ”23”, ”24”, ”25”, ”26”, ”27”, ”28”, ”29”) TABLESPACE SAMPLE,
SUBPARTITION CDR_’||cur1.prt||’_’||cur2.prt||’_’||cur3.prt||’_CDR_30_39 VALUES (”30”, ”31”, ”32”, ”33”, ”34”, ”35”, ”36”, ”37”, ”38”, ”39”) TABLESPACE SAMPLE,
SUBPARTITION CDR_’||cur1.prt||’_’||cur2.prt||’_’||cur3.prt||’_CDR_40_49 VALUES (”40”, ”41”, ”42”, ”43”, ”44”, ”45”, ”46”, ”47”, ”48”, ”49”) TABLESPACE SAMPLE,
SUBPARTITION CDR_’||cur1.prt||’_’||cur2.prt||’_’||cur3.prt||’_CDR_50_59 VALUES (”50”, ”51”, ”52”, ”53”, ”54”, ”55”, ”56”, ”57”, ”58”, ”59”) TABLESPACE SAMPLE,
SUBPARTITION CDR_’||cur1.prt||’_’||cur2.prt||’_’||cur3.prt||’_CDR_60_69 VALUES (”60”, ”61”, ”62”, ”63”, ”64”, ”65”, ”66”, ”67”, ”68”, ”69”) TABLESPACE SAMPLE,
SUBPARTITION CDR_’||cur1.prt||’_’||cur2.prt||’_’||cur3.prt||’_CDR_70_79 VALUES (”70”, ”71”, ”72”, ”73”, ”74”, ”75”, ”76”, ”77”, ”78”, ”79”) TABLESPACE SAMPLE,
SUBPARTITION CDR_’||cur1.prt||’_’||cur2.prt||’_’||cur3.prt||’_CDR_80_89 VALUES (”80”, ”81”, ”82”, ”83”, ”84”, ”85”, ”86”, ”87”, ”88”, ”89”) TABLESPACE SAMPLE,
SUBPARTITION CDR_’||cur1.prt||’_’||cur2.prt||’_’||cur3.prt||’_CDR_90_99 VALUES (”90”, ”91”, ”92”, ”93”, ”94”, ”95”, ”96”, ”97”, ”98”, ”99”) TABLESPACE SAMPLE ) ‘;