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

    Find all the objects in a tablespace.

    1013553

      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.
          sushaant

          Hi,

           

          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.
            1013553

            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.
              Marwim

              Hello,

               

               

              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.

               

               

              Regards

              Marcus

              • 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