we want to know if it is possible whether any process is selecting the data from a particular partition in a table. we want to know this so that we can purge the older partitions but since the tables are very old we are not sure if any legacy processes are selecting any data from those partitions.
You can query v$segment_statistics to see if there has been any activity for that partition since startup, or keep an eye on deltas to the figures to see if activity is more recent.
If you want to see if there is stuff accessing those partitions "right now", if you are licensed for Active Session History, you could query for I/O related events where the file#, block# fall into the partition of interest.
Querying dba_extents is quite resource intensive, so better to take a copy just for the relevant partition(s), eg
create table partition_extents as
select file_id, block_id, blocks
from dba_extents
where owner = '...'
and segment_name = '...'
and partition_name = '...';
select a.*
from v$active_session_history a,
partition_extents p
where a.p1 = p.file_Id
and a.p2 between p.block_id and p.block_id+p.blocks-1
and a.wait_class = 'User I/O';
If you are not licensed for ASH, you can do the same thing against v$session_wait_history, but you'd need to sample more aggressively.
Hope this helps.