SQL & PL/SQL

Hi All,

I am using oracle 11gr2 database.

I have a table as given below

CREATE TABLE SCMSA_ESP.PP_DROP
(
ESP_MESSAGE_ID VARCHAR2(50 BYTE) NOT NULL ,
CREATE_DT DATE DEFAULT SYSDATE,
JOB_LOG_ID NUMBER NOT NULL ,
MON NUMBER GENERATED ALWAYS AS (TO_CHAR("CREATE_DT",'MM'))
)
TABLESPACE SCMSA_ESP_DATA
PARTITION BY RANGE (JOB_LOG_ID)
SUBPARTITION BY LIST (MON)
(
PARTITION PMINVALUE VALUES LESS THAN (1)
( SUBPARTITION PMINVALUE_M1 VALUES ('01') TABLESPACE SCMSA_ESP_DATA,
SUBPARTITION PMINVALUE_M2 VALUES ('02') TABLESPACE SCMSA_ESP_DATA,
SUBPARTITION PMINVALUE_M3 VALUES ('03') TABLESPACE SCMSA_ESP_DATA,
SUBPARTITION PMINVALUE_M4 VALUES ('04') TABLESPACE SCMSA_ESP_DATA,
SUBPARTITION PMINVALUE_M5 VALUES ('05') TABLESPACE SCMSA_ESP_DATA,
SUBPARTITION PMINVALUE_M6 VALUES ('06') TABLESPACE SCMSA_ESP_DATA,
SUBPARTITION PMINVALUE_M7 VALUES ('07') TABLESPACE SCMSA_ESP_DATA,
SUBPARTITION PMINVALUE_M8 VALUES ('08') TABLESPACE SCMSA_ESP_DATA,
SUBPARTITION PMINVALUE_M9 VALUES ('09') TABLESPACE SCMSA_ESP_DATA,
SUBPARTITION PMINVALUE_M10 VALUES ('10') TABLESPACE SCMSA_ESP_DATA,
SUBPARTITION PMINVALUE_M11 VALUES ('11') TABLESPACE SCMSA_ESP_DATA,
SUBPARTITION PMINVALUE_M12 VALUES ('12') TABLESPACE SCMSA_ESP_DATA
),
PARTITION PMAXVALUE VALUES LESS THAN (MAXVALUE)
( SUBPARTITION PMAXVALUE_M1 VALUES ('01') TABLESPACE SCMSA_ESP_DATA,
SUBPARTITION PMAXVALUE_M2 VALUES ('02') TABLESPACE SCMSA_ESP_DATA,
SUBPARTITION PMAXVALUE_M3 VALUES ('03') TABLESPACE SCMSA_ESP_DATA,
SUBPARTITION PMAXVALUE_M4 VALUES ('04') TABLESPACE SCMSA_ESP_DATA,
SUBPARTITION PMAXVALUE_M5 VALUES ('05') TABLESPACE SCMSA_ESP_DATA,
SUBPARTITION PMAXVALUE_M6 VALUES ('06') TABLESPACE SCMSA_ESP_DATA,
SUBPARTITION PMAXVALUE_M7 VALUES ('07') TABLESPACE SCMSA_ESP_DATA,
SUBPARTITION PMAXVALUE_M8 VALUES ('08') TABLESPACE SCMSA_ESP_DATA,
SUBPARTITION PMAXVALUE_M9 VALUES ('09') TABLESPACE SCMSA_ESP_DATA,
SUBPARTITION PMAXVALUE_M10 VALUES ('10') TABLESPACE SCMSA_ESP_DATA,
SUBPARTITION PMAXVALUE_M11 VALUES ('11') TABLESPACE SCMSA_ESP_DATA,
SUBPARTITION PMAXVALUE_M12 VALUES ('12') TABLESPACE SCMSA_ESP_DATA
)
)
ENABLE ROW MOVEMENT;


I have populate two sets of data.
One with Positive job_log_id and another with Negative job logid as given below.

Step 1:
Data going to PMAXVALUE Partition
INSERT INTO PP_DROP ( ESP_MESSAGE_ID, CREATE_DT,JOB_LOG_ID)
SELECT LEVEL, SYSDATE+TRUNC(DBMS_RANDOM.VALUE(1,300)), 1 FROM DUAL CONNECT BY LEVEL <=300;

Step 2:
Data going to PMINVALUE partition
INSERT INTO PP_DROP ( ESP_MESSAGE_ID, CREATE_DT,JOB_LOG_ID)
SELECT LEVEL, SYSDATE+TRUNC(DBMS_RANDOM.VALUE(1,300)), -1 FROM DUAL CONNECT BY LEVEL <=300;

Now the question is how to truncate the data that is present only in the Positive partitions subpartition
Like in the PMAXVALUE partition I have 10 subpartitions and I need to truncate the data in the JAN MONTH Partition only of the PMAXVALUE partition.

Appreciate your valuable response.

Thanks,
MK.