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

"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
******************************************