Re: Tablespace, Please help

"Johnson" <syner_at_pd.jaring.my> wrote in message news:8on0q2$3fh$1_at_news6.jaring.my...
> Hello Erwin,
>
> Thank for your respond.
> I wonder, what the Autoextend Yes will do (My Question 1), will it create
> the new Datafile itself, or it will extend accordingly ? what are the
> storage parameter it will extend ?

A datafile that has autoextend set will grow by whatever it needs to inorder to accomodate the thing that is prompting the growth, unless you have also specified a 'next' clause.

In other words, this command will create a new 10M file, which will grow by 1 Mb increments:

Alter tablespace X add datafile 'path\filename' size 10M autoextend on next 1Mb;

If that last bit wasn't there, then suppose the EMP table needs more space, and comes in extents of (say) 16K. Then your datafile would extend in size by 16K each time EMP runs out of space.

All of which is utterly beside the point. Autoextend is a nice option to get you out of a hole in an emergency, but it's a lousy way to manage a production database. The datafile extends just when you don't want it to -when some poor soul is trying to insert a new record into the EMP table, for example. Proactive management of databases rather requires you to add new datafiles or MANUALLY resize your datafiles long before they get near the point of filling up. Autoextend is very much a lifebelt-on-the-titanic solution, and shouldn't be used as your primary method of allocating space.

For what it's worth, every database I've ever created has autoextend turned OFF for every datafile. It gets turned on when I go away on holidays, and that's about it.

Regards
HJR
>
> Thanks.
>
> Erwin Dondorp <erwin_at_dondorp.com> wrote in message
> news:39AD2D7B.42E2D6D1_at_dondorp.com...
> > Johnson wrote:
> > > - DATA tablespace - the free space only leave 0.13 percent, what
 should
 I do
> > > ? The autoextend is set to Yes
> > Do nothing. The autoextend will allocate all the space that is needed.
> >
> > > - When we create the tablespace, the autoextend clause is refer to
 datafile
> > > or tablespace?
> > autoextend always refers to a datafile.
> >
> > > - There are 3 datafiles in the DATA tablespace, all are set to
> > > autoextendable YES, how the raw data are write to this datafiles.
> > Oracle uses a sort of round-robin technique to allocate space in all
> > the datafiles for a tablespace.
> >
> > > - Why 3 datafiles, can it be only 1 Datafiles
> > The 3 files might reside on a different disk for example.
> > This giving better performance.
> > Another reason is that if one data file gets corrupted by a bad spot on
> > the disk, you only need to restore and recover that (relatively) small
> > file.
> >
> > Erwin
>
>
Received on Sun Sep 03 2000 - 03:58:18 CDT