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
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
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.