This discussion is archived
4 Replies Latest reply: Jun 30, 2013 11:57 PM by Hemant K Chitale RSS

Find all the objects in a tablespace.

1013553 Newbie
Currently Being Moderated

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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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 Oracle ACE
    Currently Being Moderated

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points