Like all FEATURES, it has its uses. Some systems are in a growth mode (think of DW applications). Autoextend makes sense here. UNLIMITED should never be used, unless you want to practice your recovery skills ;-) . I have a system that I have nightly loads into and the data is growing. My management will NOT let me put piles of disk out there and then set file sizes. They want me to incrementally add disk. I use autoextend with calculated MAXSIZE values to fill up the filesystem to 98%. Then when the tablespace reaches 95% of MAXSIZE, OEM pages me. When the filesystem reaches 97% the SA gets paged. We work together, add disk, and recalculate MAXSIZE. I don't waste my time with watching the day to day sizing since I know it won't overrun the filesystem, and I'll get a notice before the tablespace is full. It's not LAZYNESS it's that I have better things to do. Like work with the developers to help the design their stuff for proper data integrity and properly tuned SQL.

Rodd Holman
Enterprise Data Systems Engineer
LodgeNet Entertainment Corporation

Powell, Mark D wrote:
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 --


Other related posts: