I know this appears to be a simple question; I hope in fact that it is.
The obvious answer seems to be to query DBA_SEGMENTS predicating on tablespace_name, but these days I see that if, for example, a table partition is newly created and contains no data it may not yet have a segment. Yet I still want to find it.
Then must I query DBA_TABLES, DBA_TAB_PARTITIONS, DBA_TAB_SUBPARTITIONS, DBA_INDEXES, etc for objects which may not have segments attached to them?
-- edit I am on 11g
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