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

Hi Richard,

Choose an appropriate maxumum size for your datafiles and don't let the datafiles exceed that max. size. When ( or rather : *before* -) ) a tablespace runs full, add new datafiles.

Like this :

   Create tablespace mytbs datafile
'C:\ORACLE\ORADATA\DB1\mytbs_001.dbf' size 128M autoextend on next 128M maxsize 2048M;

When the datafile extends and gets near the max. of 2048M, you add another one :

   Alter tablespace mytbs add datafile
'C:\ORACLE\ORADATA\DB1\mytbs_002dbf' size 128M autoextend on next 128M maxsize 2048M;

Which maxsize is appropriate is up to you and your operating system, but it should of course not exceed the max. file size of your O/S. Also remember that very large files can be awkward to handle regarding backup, file distribution over disks, disk fragmentation a.o.

A very common maxsize is 2 Gb. For the reasons given above, I would not recommend you a maxsize > 2Gb.

• Kenneth Koenraadt

richardpstanton_at_yahoo.com (Richard Stanton) 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?
>
> 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?
>
> thanks in advance
>
> Richard Stanton
Received on Tue Jan 07 2003 - 15:26:43 CST