Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: AUTOEXTEND


From: Murching, Bob <>
Date: Tue, 4 Oct 2005 17:34:49 -0400
Message-ID: <>

Respectfully disagree... development (or operations, sales, the customer, ...) does not always know the storage requirements for a system. Sure, between hard standards, lots of process and a few rolls of red tape, it's possible to plan and control things precisely enough to pull it off. Unfortunately, as a counterpoint to your argument regarding lost DBA hours, many such shops wind up taking two weeks of meetings to extend a tablespace by 2GB. We autoextend datafiles as a matter of course; supplant that with very aggressive free space monitoring and prudent caps on maximum data file size, and you can have your cake (manageabilty) and eat it (agility) in one fell swoop.  

I won't speak for everyone but our DBAs are far too talented (and we have far too many projects in need of their talents) for them to be wasting time running storage reports when nowadays they are more sorely needed helping development teams to web service-enable PL/SQL or facilitating the use of materialized views to meet high throughput reporting requirements.

From: Powell, Mark D [] Sent: Tuesday, October 04, 2005 4:08 PM

I think auto-extend datafiles are a very bad idea. I do not know how many posts I have read in the metalink forum where some poor dba used the feature only to end up with no space left on the disk. Usually the rollback (now undo), temp, or system tablespace seems to be involved. Once or twice it has been a user data tablespace where development managed to place code with an insert in an infinite loop into production.  

Usually the poster wants to know why "shrink" does not work.  

The fact remains it is the job of the DBA to monitor the database space usage and to configure the database to support the applications and user load. Each tablespace needs to be a certain size and if it should attempt to extend beyond that size something is wrong or has changed. The DBA need to be aware of all major data loads and changes to how applications will interface with the database. Development is responsible for informing DBA of their activity.  

When Oracle first introduced extendable datafiles the word was the feature was introduced for small shops that did not have a fulltime or real DBA. If can be useful if you do not know how big something will be, but the feature is dangerous and has caused many a DBA hours of work reallocating tablespace usage.  

The feature is a crutch for lazy DBA's who do not want to perform trend analysis on the database space usage and who do not keep themselves informed of development plans.  

IMHO -- Mark D Powell --  

From: [] On Behalf Of ora_forum
Sent: Tuesday, October 04, 2005 3:09 PM

Hi All,  

I have consulting on site and he is trying to convince our management to use AUTOEXTEND almost on all datafiles and monitor on FS. Could you give me examples why we should not do AUTOEXTENTS?

I'm thinking about: 1. Keep all db files 4 GB in size for better maintenance...

Something else?


Yahoo! for Good
Click here <> to donate to the Hurricane Katrina relief effort.

Received on Tue Oct 04 2005 - 16:37:50 CDT

Original text of this message