How to select rows from a specific partition

The syntax to use if you want to select directly from a specific partition:

SELECT row1, row2 ... 
FROM       my_partitioned_table
PARTITION (my_partition_name)
WHERE ....;

From a subpartition:

SELECT row1, row2 ... 
FROM       my_partitioned_table
SUBPARTITION (my_subpartition_name)
WHERE ....;

With Oracle 11g interval partitioning, the syntax has changed so that you can reference a date range, rather than a partition name, since the auto generated names won't say anything meaningful about their content:

select row1, row2 ..
from my_partitioned_table
PARTITION FOR (to_date('25-FEB-2015','dd-mon-yyyy')); 

Remember that aliases must be specified after the entire FROM-clause, including the PARTITION-part, like this:

select ee.col1
from my_partitioned_table
partition (SYS_P2447) ee
where ee.col2 = 'string1'
and ee.col3 like '%string2%'
order by ee.col1,col2;

If you place the alias immediately after the table name, you'll get the error:

partition (SYS_P2447)
          *
ERROR at line 3:
ORA-00924: missing BY keyword

upon execution.