CREATE TABLE EMPL 
PARTITION BY RANGE(DEPTNO)
SUBPARTITION BY LIST(JOB)
(PARTITION P10 VALUES LESS THAN(20)
(SUBPARTITION P10JOBA VALUES ('ANALYST'),
SUBPARTITION P10JOBC VALUES('CLERK'),
SUBPARTITION P10JOBM VALUES('MANAGER'),
SUBPARTITION P10JOBS VALUES('SALESMAN')),
PARTITION P20 VALUES LESS THAN(30)
(SUBPARTITION P20JOBA VALUES ('ANALYST'),
SUBPARTITION P20JOBC VALUES('CLERK'),
SUBPARTITION P20JOBM VALUES('MANAGER'),
SUBPARTITION P20JOBS VALUES('SALESMAN')),
PARTITION P30 VALUES LESS THAN(40)
(SUBPARTITION P30JOBA VALUES ('ANALYST'),
SUBPARTITION P30JOBC VALUES('CLERK'),
SUBPARTITION P30JOBM VALUES('MANAGER'),
SUBPARTITION P30JOBS VALUES('SALESMAN')))
AS SELECT * FROM EMP
WHERE 1=2

INSERT INTO EMPL
SELECT * FROM EMP

select * from empl and job equal to ANALYST



SELECT * FROM EMPL PARTITION(P10)

SELECT * FROM EMPL PARTITION(P10) SUBPARTITION(P10JOBA)

orclz> INSERT INTO EMPL
  2  SELECT * FROM EMP;
INSERT INTO EMPL
            *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition


orclz>

SQL> SELECT * FROM EMPL SUBPARTITION(P10JOBA);

no rows selected