Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Note on duplication:

This is not a duplicate of "Oracle extents - How can I have so many with this small size?" This is a separate question I entered which happens to be in the same domain.

This is a duplicate of "Oracle extents - how is it possible to get so many? [closed]" - this is a question that was closed because someone thought it was a duplicate of the above question, apparently without reading either question, and despite the fact that neither question had an answer. I see why this Stackexchange is not exactly flourishing.


This is more curiosity about how Oracle Database works than anything I need to change (yes I know multiple extents are fine, I read Tom Kyte too).

I have an index with the following characteristics:

Size:        376MB
Initial Ext: 118MB
Next Ext:    1MB
Num Extents: 12

In case it's not apparent, my question is, how can I have something that size, with those extents sizes, and only 12 extents? 376 > 118 + (1 * 11)

FYI: to Justin Cave, yes, it's locally-managed.

Thank you.

share|improve this question
If you read that much Tom Kyte you'd answer it yourself :) Anyway, please edit your question with the output of select * from dba_extents where segment_name = 'NAMEOFMYINDEX' – Phil May 7 '12 at 20:54
As far as flourishing, see which lists this site as #23 out of 81 SE sites with regard to traffic. Most other stats are comparable. – Leigh Riffel May 7 '12 at 21:02
@Phil Was not claiming to be omniscient, or eidetic, just trying to head off people saying how many extents you have does not affect performance, which was not my question. – orbfish Jun 26 '12 at 17:14
@LeighRiffel I guess I'm used to StackOverflow, 5K+ questions a day vs. 26 questions a day is a different world ;) – orbfish Jun 26 '12 at 17:14
@orbfish I see. A house plant and a mature tree can both be flourishing, just on different scales. – Leigh Riffel Jun 26 '12 at 17:35
up vote 3 down vote accepted

You may believe that this is a different question but my answer is going to be essentially identical assuming the additional information from the prior question is the same-- you're using automatic extent allocation and you're not sure how Toad is determining these numbers.

The data you're getting from Toad appears to be incorrect or, at least, misleading. If you are using a locally managed tablespace with automatic extent allocation, Oracle will determine your initial and next extent sizes automatically. In 11.2, the first 16 extents are going to be 64k in size (for a total of 1 MB). The next 63 extents are going to be 1 MB in size. So if you have 12 extents, that implies that the table occupies 768 kb of space on disk. An initial extent of 118 MB makes no sense and a size of 376 MB makes no sense.

What does

SELECT tablespace_name,
       bytes/1024 kb
  FROM user_extents
 WHERE segment_name = <<name of index>>

show you? If you have 12 extents, you should see that extents 0-11 are 64 kb in size.

SELECT SUM(bytes)/1024/1024 size_in_mb
  FROM user_extents
 WHERE segment_name = <<name of index>>

will show you the total size of the table's extents on disk.

share|improve this answer
I think the OP saw it as a different question because this is regarding an index & the previous question was about a table. – Phil May 8 '12 at 8:14
Not affecting the answer, but the 0-11 thing is not correct according to user_extents: I see an index with 6 extents, all are 64K except that the 3rd is 8K. Another has 20 extents and 10 distinct sizes, only one of which, the 18th, is 64K. FYI. – orbfish Jun 26 '12 at 17:11
@orbfish - Can you edit your post to include the output from user_extents. Also, post the results of querying user_indexes to get the tablespace_name and dba_tablespaces to get the extent_management and allocation_type for that tablespace. Is this an index that was created in a dictionary managed tablespace that was converted to locally managed? – Justin Cave Jun 26 '12 at 17:42

To give details for future reference, Toad appears to be getting its information as follows:

Size:        dba_segments.bytes
Initial Ext: dba_segments.initial_extent
Next Ext:    dba_segments.next_extent
Num Extents: dba_segments.extent

The size matches the sum of the extent sizes from dba_extents, and the number matches the number of records in dba_extents, but the initial and next extent sizes appear unrelated. I would like to know where these numbers come from, since according to Oracle they're accurate, and they do have an effect (when importing tables, for example, you can't import a table with a huge initial extent size if there is not enough space for it in the tablespace, even though according to dba_extents, it's not using that much space).

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.