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

Guessing whether tablespace has sufficient space

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

Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.