Re: What's best - to resize datafiles or add new ones?

Niall Litchfield wrote:

> "Richard Stanton" <richardpstanton_at_yahoo.com> wrote in message
> news:87bc8a5f.0301070706.21d1089c_at_posting.google.com...
> > Hi
> >
> > Sorry if this is a stupid question, but I am new to oracle and would
> > appreciate any insight you can offer.
> >
> > I am running ver 8.0.5 on nt4. It is stored on raid 5.
> >
> > I have received an error after trying to insert data into a table:
> > "ORA-1653: unable to extend table tablename by 61446 in tablespace
> > tablespace_name". I presume I must either extend the current datafile
> > or add a new one?
>
> That is correct. I think that there is a pretty strong case for making all
> datafiles the same size, but there is nothing to stop you resizing a
> datafile if you wish. The case for making the the same size is essentially
> for ease of maintenance and for the ability to move files around from disk
> to disk. The only comment I have is that the 61446 value is in database
> blocks. Even if you only have a 2k block size that corresponds to a next
> extent of 120M (if my maths is correct). This seems likely to me to be a
> segment with inappropriate storage clauses (PCTINCREASE 50 being the most
> likely culprit). You may wish to review the storage for that object.
>
> >
> > I would be grateful if anyone could explain to me which would be the
> > better solution. Does either one require the database or tablespace
> > to be taken offline or backed up?
>
> Neither, though the sooner you backup after making the change the better,
> for recovery reasons.
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer
> ******************************************

First time I've ever heard someone make the statement that there is an argument (good or otherwise) to make all datafiles the same size.

I'd be interested in the issues as you see them.

Thanks.

Daniel Morgan Received on Tue Jan 07 2003 - 10:39:30 CST