This is a different question than the one with the almost-identical title.

Again, 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 a table with the following characteristics:

Initial Ext: 1.44GB
Next Ext:    1MB
Num Extents: 26
Size:        4.18MB

In this case, I got the size by scanning 10% of the table rows. With an initial extent size so high, and the data so low, how did further extents get added? I am certain that the table did not become huge, then shrink drastically back down again.

Thank you.

  • 4
    Do you really have a dictionary managed tablespace in an 11.2 database? That's the only way to get those initial and next extent sizes. Can you post the query or queries you're using to get these numbers? May 3 '12 at 21:45
  • EXTENT MANAGEMENT LOCAL AUTOALLOCATE - I'm getting the data through Toad so I do not have the queries.
    – orbfish
    May 7 '12 at 19:06

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 26 extents, that implies that the table occupies 11 MB of space on disk. An initial extent of 1.44 GB makes no sense and a size of 4.18 MB seems rather low if you're saying it hasn't shrunk.

What does

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

show you? If you have 26 extents, you should see that extents 0-15 are 64 kb in size and extents 16-25 are 1 MB in size.

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

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

  • Not affecting the answer, but the 0-15, 16-25 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 a table that was created in a dictionary managed tablespace that was converted to locally managed? Jun 26 '12 at 17:45

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

This is not the 10% sampling I mentioned, but I have seen examples where the same disconnect appears (too many extents for small size).

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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