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

Allocating multiple datafiles in a tablespace

Oracle expert Brian Peasland explains how Oracle allocates multiple datafiles in a tablespace.

We have a 10gR2 database on AIX. At present we have multiple datafiles per tablespace and I'm wondering what Oracle's policy for spreading out the segments between these files would be. We have autoextended the files up to a limit of 8G and I would have expected Oracle to try and fill all the files (at their existing size) before extending any of them, but after a recent data refresh (via import) we have very lopsided datafiles in our index tablespace. Looks like Oracle autoextended the first file as far as it could and only then went onto the next file. Is there a reason for this? Should we adopt a different strategy for our datafiles?
Normally, Oracle allocates the first extent in the first datafile of the tablespace. When a new extent is needed, the new extent is allocated in the second datafile of the tablespace. The nth extent is allocated in the nth datafile of the tablespace (assuming n datafiles comprise the tablespace). The n+1 extent is allocated back in the 1st datafile.

The above process is how Oracle allocates extents for empty tablespaces. For tablespaces that have segments allocated, Oracle tries to allocate extents where it can find space. And the extent is not allowed to cross a datafile. So if you drop segments and leave holes of free space, Oracle will attempt to fill in those holes. So you may not notice the same behavior.

There are other issues which may affect this behavior as well. A high setting for PCTINCREASE may cause Oracle to behave differently. Or, if you have extents that are larger than your free space fragments, Oracle may exhibit other behavior as well. It all depends on the tablespace's allocation strategy, i.e. locally or dictionary managed.

Dig Deeper on Oracle database administration

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