If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

newbie5, for sure in some situations not using autoextend can be useful, for some business need you can NEED to know how much space will go for what, example : I have in my system (a dw-like one) all the big tables partitioned by a date column, receiving data from foreigno sources I need to plan disk space usage forewards and I have only a few filesystems - if I do not use autoextending, pre-creating the datafiles for the next months, anytime when the damagement asks "what space we have for months x, y or z" it is very direct to do a sum of bytes in dba_datafiles and I have an exact and precise answer, I can grow or shrink it as needed, and I do NOT need to "receive" the data for Oracle grows... With autoextend I must respond "we have x free bytes, BUT I donīt know exactly HOW MUCH I can spare for months x or y, the space will be consumed as needed", many times damagement do not want/accept it in this way...

For performance, no : autoextending or not gives you more or less the same thing - maybe in a very very busy system the time needed to autoextend a datafile can be of concern, but this is something exceptional, in my experience... And even there, nothing stops you from pre-allocating some space in the busiest objects.

Manual space management has lots of advantages to the DBAs. It ensures job security and .... that's pretty much it. A company that has a strict policy of not using autoextend either likes to waste money on salary or doesn't fully understand how the technology works.

It really depends on the users and maturity level of non-dba but application operations.
Worst is low / no maturity and too much privileges...

IF there exists excellent capacity management with clear UNDERSTANDING of the growth, a forecast-graph including the expected usage from business and a real monitoring of the "expected size" compared to "real size" - and someone who immediately detects deviations and ACTS on that, autoextend is OK.

You are one of the most professional DBA's in this forum and in your case it's OK.

It might be also OK for tiny systems which never will ever reach significant data volume.

But I prefer not using "autoextend" as a "last" protection against
(1) users having more privileges than they should have (or application support staff just logging in using the application-owner account)
(2) other application problems

(And you can be sure that, if I get a warning that a tablespace soon will run out of space that I extend tablespaces only AFTER I verifyed WHY it did grow that much...).

==> In case of a low maturity level of IT operations the "autoextend" just postpones the "tablespace full" error to a date where everything is full. (Yes, there exist monitoring tools and scripts, but that still requires someone who acts on a warning...)

As DBA I can't influence (1) and (2), I just might get the night- and weekend shifts of cleanup ....

Biggest danger of (1) is application support staff creating copies of tables (before changing something...) and of course never removing them.

And if they create a copy of a real huge table then the archivelog destination might run full faster than 4 backup slaves can move the archivelog files to tape.... (I had to work with application staff not having any idea of table sizes...)

(2) Applications running in a application-bug can also create astonishing unusual behaviour....

In case that a full tablespace does not quickly terminate this disaster you might detect it after you get complaints
*) that performance is slow
*) Backup runs much longer, and still runs when scheduled billing-run starts ... (Or the cost center charging close to 1 Euro per GB and backup presents suddenly unusual hight invoices....)