Q
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

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.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close