Oracle extents – How to have so many with this small size

oracleoracle-11g-r2storage

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.

Best Answer

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,
       extent_id, 
       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.

Related Solutions

I have a powerful machine with 70 GB RAM. How many Oracle instances with 20 GB can be created

Your kernel parameters need modifying.

Edit /etc/sysctl.conf and ensure the following lines are present:

kernel.shmall = 18350080
kernel.shmmax = 75161927680
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128

Then reboot the machine.

Oracle extents – How to have so few with this large size

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.