title: Oracle: how to drop a subpartition of a specific partition
body: <p>I am using an oracle 11 table with interval partitioning and list subpartitioning like this (simplified):</p>
  
  <pre><code>CREATE TABLE LOG
  (
    ID NUMBER(15, 0) NOT NULL PRIMARY KEY
  , MSG_TIME DATE NOT NULL
  , MSG_NR VARCHAR2(16 BYTE)
  ) PARTITION BY RANGE (MSG_TIME) INTERVAL (NUMTOYMINTERVAL (1,'MONTH'))
    SUBPARTITION BY LIST (MSG_NR)
      SUBPARTITION TEMPLATE (
       SUBPARTITION login VALUES ('FOO')
     , SUBPARTITION others VALUES (DEFAULT)
     )
     (PARTITION oldvalues VALUES LESS THAN (TO_DATE('01-01-2010','DD-MM-YYYY')));
  </code></pre>
  
  <p>How do I drop a specific subpartitition for a specific month without knowing the (system generated) name of the subpartition? There is a syntax <a href="http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_3001.htm#i2131072">"alter table ... drop subpartition for (subpartition_key_value , ...)"</a> but I don't see a way to specify the month for which I am deleting the subpartition. The <a href="http://docs.oracle.com/cd/B28359_01/server.111/b32024/part_admin.htm#autoId44">partition administration guide</a> does not give any examples, either. 8-}</p>