If the table partition is not holding any data then it should be having only 1 extent allocated and min no of bytes (64KB e.g), blocks & Header_block values . You can filter on that information in order to find out empty partition.
select * from dba_segments
where owner = 'SCHEMA_NAME or USERNAME' and segment_type = 'TABLE PARTITION'
and extents = 1;
This is wrong. If no segment has been created then zero extents will be allocated. The logic is consistent. If no segment has been created (SEGMENT_CREATED='NO') there will be no record in DBA_SEGMENTS.
That was the purpose in my question.
Thanks for your contribution - do you have any other ideas? As I mentioned, all I can think of doing is checking individually in DBA_TABLES, etc.
A no-segment object (e.g. a table or partition with no rows inserted yet) wouldn't appear in DBA_SEGMENTS, as you've correctly identified.
I don't know of a way to "unify" a query. You could start with DBA_SEGMENTS for all *existant* segments. Then query, individually DBA_TABLES, DBA_TAB_PARTITIONS, DBA_INDEXES and DBA_IND_PARTITIONS to find "expected" segments -- so do a union of the two queries.
Hemant K Chitale