We perform a load of several million records a day in a 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?
When your LMT is AUTOALLOCATE, then Oracle will allocate extent sizes of 64K, 1MB and 8MB. The next extent will be equal to, or larger than the largest extent already allocated. For instance, suppose your table has allocated sixteen 64K extents and two 1MB extents. The next extent allocated will not be 64K. It will be 1MB. After fourteen more 1MB extents have been allocated, 8MB extents will be allocated.
So you will need to check that your tablespace has sufficient 1MB extents or 8MB extents available in free space.
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.