RE: AUTOEXTEND

Mark,  

    May I take exception to your statement? Now whereas I don't allow autoextend on those critical tablespaces (system, undo, temp) I do allow it on other tablespaces, but to only a specified size. Why? Well for one it's damned impossible to get development to tell the infrastructure group what the heck their up to in the first place. Their suppose to comply with the change control provisions of IS&T, but don't on a routine basis especially since 75% of them aren't in IS&T in the first place. Second the volume of data that we have to retain is driven more by manufacturing and sales than anyone else. We make estimates of disk space usage based on the last 5 years, but that's just a SWAG at best. If we have a good year or bad parts the data growth can get right out of hand fast. And GOD forbid that a tablespace can't accept data from the manufacturing line. That's a reason to wake up all of the company VP's. Talk about being on the HOT SEAT!! You'll hear about it for the next 6 months for sure & it'll be included in your annual review on the not favorable side. Do I monitor space, yes, and I have to report space usage each month with the deltas from the previous month. But there is no way of knowing when things will get out of hand around here. Management knows, but management in this case prefers to rely on the crutch. And BTW, did you ever hear the phrase "No disk before it's time"???

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Powell, Mark D Sent: Tuesday, October 04, 2005 4:08 PM
To: oracle-l_at_freelists.org
Subject: RE: AUTOEXTEND

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: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of ora_forum Sent: Tuesday, October 04, 2005 3:09 PM
To: oracle-l_at_freelists.org
Subject: AUTOEXTEND

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?

Thanks.

Yahoo! for Good
Click here to donate <http://store.yahoo.com/redcross-donate3/> to the Hurricane Katrina relief effort.

--
http://www.freelists.org/webpage/oracle-l