Discrepancy in total objects count between Tablespace | Objects, and Object Explorer

Discrepancy in total objects count between Tablespace | Objects, and Object Explorer | Indexes. (4234342)

Return

Feedback Submitted

Did this article solve an issue for you?

Select Rating

Title

Discrepancy in total objects count between Tablespace | Objects, and Object Explorer | Indexes.
Description

There is a discrepancy with the index count in the a Toad Tablespace | Object window, compared to the Toad Object Explorer | Index window or Oracle catalog dba_indexes.
A particular tablespace (INV_INDX) is known to contains only index objects. But when displaying the object list in Toad, the count is less the count when querying Oracle's catalog via sql/plus.
Why is there a difference?
Cause

The Tablespaces | Objects window queries dba_segments. So the question here is what is difference with the indexes count between a query for dba_segments and others, like dba_indexes. Or why are some indexes are found when querying dba_indexes but not in dba_segments.
This is a 3rd party article/forum that discusses this discrepancy:
https://community.oracle.com/thread/3598525?tstart=0
It comes down to this, DBA_SEGMENTS view showing the segment which has storage allocation. The indexes not included in Toad's Tablespace | Object window may be unusable, where no storage is being used, and so would not show up in this view. But the indexes were created at some point and haven't actually been dropped, so querying DBA_INDEXES it is still listed there.
Resolution

In Toad, to specifically review a list of indexes that were created at some point, use the Object Explorer and go to the Index tab. All indexes are listed there as that tab queries dba_indexes and will give a full list of just indexes. This is a better way of reviewing indexes and objects in general. If needed a filter to filter by tablespace (or any other condition) can be added to adjust the view of that list.
Additional Information

This may apply to various versions of Toad.

Feedback Submitted

Did this article solve an issue for you?

Select Rating

Request a KB Article

Discrepancy in total objects count between Tablespace | Objects, and Object Explorer | Indexes. (4234342)

Title

Description

Cause

Resolution

Additional Information

Leave a Comment