4 Replies Latest reply: Jul 1, 2013 1:57 AM by Hemant K Chitale RSS

    Find all the objects in a tablespace.


      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

        • 1. Re: Find all the objects in a tablespace.



          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;

          • 2. Re: Find all the objects in a tablespace.

            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.

            • 3. Re: Find all the objects in a tablespace.




              the description of this forum space is

              Discussion of Oracle Object-Relational Technology issues

              Please mark this question as answered and ask your question in the appropriate space.





              • 4. Re: Find all the objects in a tablespace.
                Hemant K Chitale

                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