Problem solve Get help with specific problems with your technologies, process and projects.

Size of extents with AUTOALLOCATE

We perform a load of several million records a day in an Oracle 9i database. We can roughly estimate the total size the load will take in the database, in bytes. But before we go ahead with loading the records using SQL*Loader, we need to be able to state whether the tablespace has sufficient number of free extents to accomodate the load. In Uniform Size extents, we can know this for sure. But with Autoallocated extents, the sizes of extents are unpredictable. Using the data dictionary views or some other technique, is it possible to guess whether the tablespace has sufficient space? Can you suggest a way of checking this? Are you aware of whether Oracle allocates the Best fit or First fit of free extent when it requires an extent of a certain size?

With AUTOALLOCATE, the sizes of the extents are very predictable. They will be 64K, 1MB, or 8MB. The nice thing about these extent sizes is that they are integer multiple of each other. This makes management much easier for the LMT. The only big trick is if you explicitly state the extent size. If you state that the extent size is 128K, then the database will allocate two 64K extents. If you state that the extent size is 2MB, then the database will not allocate lots of 64K extents, but rather skip right to allocating two 1MB extents.

Let the LMT manage this for you. If you don't specify the extent size when loading, then you shouldn't have any problems. Checking DBA_FREE_SPACE should satisfy your request.

You can make your life easier if you do two things. One, make sure that your datafiles are set up to autoextend if necessary. This way, you will have enough space to load your data. Two, if you are on 9i, make use of the RESUMABLE space transactions. This way, if you run out of space, your load will just stop until you fix the space problem.

For More Information

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.