You can find the partition boundaries in the user_tab_partitions.high_value. Unfortunately this is a long, so you have to do some SQL wizardry to make the values something usable:
create table sample_tab_test (
id number,
name varchar2(50 byte),
start_time timestamp (6),
end_time timestamp (6)
) partition by range ( start_time ) (
partition part_201606080000_201606080010 values less than (timestamp' 2016-06-08 00:10:00') ,
partition part_201606080010_201606080020 values less than (timestamp' 2016-06-08 00:20:00') ,
partition part_201606080020_201606080030 values less than (timestamp' 2016-06-08 00:30:00') ,
partition part_201606080020_201606080040 values less than (timestamp' 2016-06-08 00:40:00')
);
insert into sample_tab_test
select rownum, 'x',
timestamp' 2016-06-08 00:00:00'+numtodsinterval(rownum, 'minute'),
timestamp' 2016-06-08 00:00:00'+numtodsinterval(rownum+1, 'minute')
from dual connect by level < 40;
with high_vals as
(select dbms_xmlgen.getxmltype('
select p.high_value,
p.partition_name
from user_tab_partitions p
where p.table_name = ''SAMPLE_TAB_TEST''')
as xml
from dual
), dts as (
select partition_name,
to_date(substr(high_value, 12, 19), 'yyyy-mm-dd hh24:mi:ss') st_dt,
lead(to_date(substr(high_value, 12, 19), 'yyyy-mm-dd hh24:mi:ss'))
over (order by high_value) en_dt
from high_vals p,
xmltable('/ROWSET/ROW'
passing p.xml
columns partition_name varchar2(30)
path '/ROW/PARTITION_NAME',
high_value varchar2(60)
path '/ROW/HIGH_VALUE'
)
)
select * from dts
where timestamp'2016-06-08 00:15:00' between st_dt and nvl(en_dt, timestamp'2016-06-08 00:15:00');
PARTITION_NAME ST_DT EN_DT
------------------------------ -------------------- --------------------
PART_201606080000_201606080010 08-JUN-2016 00:10:00 08-JUN-2016 00:20:00
Adrian Billington discusses more methods at:
http://www.oracle-developer.net/display.php?id=430 Having said this, I'm confused as to why you want this. Your query already has the partition key (start_time) in it. So Oracle will do partition elimination based on your from/to dates:
select /*+ gather_plan_statistics */* from sample_tab_test
where start_time between to_date('2016-06-08 00:15:00','yyyy-mm_dd hh24:mi:ss')
and to_date('2016-06-08 00:25:00','yyyy-mm_dd hh24:mi:ss');
select *
from table(dbms_xplan.display_cursor(null, null, 'BASIC +PARTITION'));
--------------------------------------------------------------------
| Id | Operation | Name | Pstart| Pstop |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| 1 | PARTITION RANGE ITERATOR| | 2 | 3 |
| 2 | TABLE ACCESS FULL | SAMPLE_TAB_TEST | 2 | 3 |
--------------------------------------------------------------------
Notice how Oracle only accesses partitions 2 & 3 (pstart/pstop columns).
If you want all the data in the start/end time range you pass, then you'll have to run the partition query multiple times to get it:
select * from sample_tab_test partition ( p1 )
where start_time between :st and :en;
select * from sample_tab_test partition ( p2 )
where start_time between :st and :en;
...
This is likely to be slower than letting Oracle do the partition elimination itself!