Re: Newbie DBA Question: ORA-01658

It's an awful practice, frankly. You can make it slightly less awful by specifying a NEXT clause, so that when the file increases in size it does so in appreciable dollops rather than pathetic dribbles. (In the absence of a NEXT clause, the file acquires whatever space the object needing the extra space actually requires -so if it's a table with a NEXT extent clause of 16K, 16K is what you get). You needn't worry about it taking over the whole hard disk provided you specify a MAXSIZE clause, too. That stops autoextending when the limit is reached.

So if you must do autoextension, the full shmozzle would be:

alter database datafile 'path/filename' autoextend on next 50M maxsize 2000M;

(fill in whatever figures seem appropriate).

However, consider when the autoextension takes place: right in the middle of someone's transaction. So is it a good idea? Nope, not if you value performance. Frankly, it's lazy DBA'ing. Adding additional files, or proactively (i.e., *before* the need arises!!) resizing a file is infinitely less of a performance hassle -provided the resize is done during a quiet spell. On the other hand, I've been known to "autoextend on" everything in sight just before a holiday, because at least that way things will keep ticking over. First thing on getting back to work, though: autoextend off!

Regards
HJR

--

Oracle Resources : http://www.geocities.com/howardjr2000
========================================


"godmann" <allanwtham_at_yahoo.com> wrote in message
news:95cd51c.0110310103.730b42d1_at_posting.google.com...


> Howard,

>

>    Is letting the datafiles grow dynamically (autoextend on) a good

> practice? I am just wondering it may grow unbound if let unnoticed. What

> is the best practice if you do set it autoextend on

>

> Allan W. Tham

> DBA