What do you think of a policy in a company that no datafile should be on autoextend. All the datafiles must be fully sized and manually extended if necessary.
Can someone enunciate the advantages of auto extend versus manual?
Printable View
What do you think of a policy in a company that no datafile should be on autoextend. All the datafiles must be fully sized and manually extended if necessary.
Can someone enunciate the advantages of auto extend versus manual?
Database processing doesn't simply stop if the monitoring of free space fails to predict a need to extend.
Of course, if the monitoring is 100% reliable, then the data file will never auto extend anyway so it wouldn't matter which it was set to, so it might as well be set to autoextend.
Are there any advantages in not using autoextend? For example if the database is i/o intensive, is there an advantage of fully sizing the datafiles versus autoextend?
With autoextend you will need to keep an eye of the physical space on the server or you could fill the physical disk, which could then cause you more problems
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.
[]s
Chiappa
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...)
(3) I avoid files bigger than 2 GB (or 4 GB) on medium systems (300 GB - 2 TB), therefore a max. file size is required.
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....)
bah, just another excuse for manual intervention for an automated process. If your users are creating copies of tables that big, you have a permission problem, not a space problem.