| Register | FAQ | Calendar | Search | Today's Posts | Mark Forums Read |
|
#1
|
| ORA-14048: a partition maintenance operation may not be combined with other operations ORA-06512: at "EMACH.ADD_PARTITION_CDR_MONTH", line 19 ORA-06512: at line 1 While creating the table a default partition and subpartiton was created This is the procedure that i have created to include the partitons and subpartitons. CREATE OR REPLACE PROCEDURE Add_Partition_Cdr_Month IS IDATE DATE; INPUTDATE DATE; NDATE VARCHAR2(100); INPUT VARCHAR2(100); Res1 NUMBER :=0; testid table2.rcp_id%TYPE; CURSOR c1 IS SELECT DISTINCT (rcp_id) FROM table2 WHERE snd_id IN (SELECT DISTINCT opt_owner_id FROM CFG_T WHERE opt_id = 8 AND opt_owner_id <> 'ALLOP') AND ra_sts IN ('P', 'L', 'T') AND (ra_end IS NULL OR ra_end < SYSDATE) ; BEGIN SELECT LAST_DAY (SYSDATE)+1 INTO IDATE FROM dual; --- 1st june SELECT ADD_MONTHS (LAST_DAY (SYSDATE)+1 ,1) INTO INPUTDATE FROM dual; -- 1st july NDATE := TO_CHAR(IDATE,'YYYYMMDD'); INPUT := TO_CHAR(INPUTDATE,'YYYYMMDD'); EXECUTE IMMEDIATE 'ALTER TABLE table1 ADD PARTITION CDR_'||NDATE||' VALUES LESS THAN (TO_DATE('||INPUT||',''YYYYMMDD'')) (SUBPARTITION CDR_'||NDATE||'_000000 VALUES (''00000'')) TABLESPACE S_mem2_D'; FOR testid IN c1 LOOP -- check if subpartition exists SELECT COUNT(*) INTO Res1 FROM all_tab_subpartitions WHERE subpartition_name LIKE 'CDR_'||NDATE||'_'||testid.rcp_id; -- create subpartition IF Res1 = 0 THEN EXECUTE IMMEDIATE 'ALTER TABLE table1 MODIFY PARTITION CDR_'||NDATE||' ADD SUBPARTITION CDR_'||NDATE||'_'||testid.rcp_id||' VALUES ('''||testid.rcp_id||''') TABLESPACE S_mem1_D'; END IF; END LOOP; END; |
![]() |
| Thread Tools | |
| Display Modes | |