Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm not a DBA, nor is there a DBA on the project I'm currently working, so we have tablespaces with autoextend on next 6m. I have no idea if this is low, although I believe so.

I want to measure what's going on with the current configuration and, if possible, tune the autoextension of my tablespaces so that a low value does not cause this to happen very frequently.

share|improve this question
up vote 2 down vote accepted

If you know the original size, it is easy to calculate the number of extends that have occurred. The metadata for the datafile will include its age.

The overhead for an auto extend is low, but datafile fragmentation may cause problems.

I generally try to extend by 10 to 25 percent of the original size, and aim for as few extend operations as possible. Try to allocate enough space for at least a year's worth of data.

For development databases, I tend to size smaller, and extend more often.

You can find out how full a tablespace by querying dba_tablespace_usage_metrics. The Orafaq Tablespace page will give you additional information.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.