wrote: Hi,=20 I want to increase tablespace, just want to know which way is = better: 1. ALTER TABLESPACE SYSTEM ADD DATAFILE '/u01/oradata/orcl/users02.dbf' SIZE 1M AUTOEXTEND ON NEXT 1M MAXSIZE 100M; 2.alter tablespace system add datafile = '/u01/oradata/orcl/users02.dbf' size 100m; Thanks, Jack=20 -------------------------------------------------------------------------= ----- Yahoo! Mobile - Check & compose your email via SMS on your Telstra or Vodafone = mobile. ------=_NextPart_000_009A_01C34ECE.F67BBDC0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Hi!

In multi-user environments IO times = won't probably=20 be much slower with 1M extensions, because disk reading heads will very = likely=20 move away from original location right after first request to serve = other=20 sessions request. Also, if you think about indexed access to tables, = then data=20 from completely different locations of Oracle datafiles are read=20 anyway.

You just should make sure that your = extent sizes=20 match the (multiple of) size of db_block_size * = db_file_multiblock_read_count.=20 That way if you even got 32 block size * 64 d_f_m_r_c =3D 2MB and = autoextend 1M=20 you won't have any problem, because if extent size is 2MB, 2MB is = autoextended=20 anyway.

I personally prefer to precreate = datafiles (usually=20 with uniform sizes) for highly loaded systems and monitor space in them, = for the=20 reason that otherwise lot's of extra IO is generated when extending 100M = for=20 example. Also, the session extending segment, has to wait until the = extension is=20 complete... This might cause locking explosions etc.. in = high-concurrency=20 systems etc.. Instead, I monitor the free space inside tablespaces and = add=20 datafiles during low activity periods.

But for small databases in sense = of disk=20 usage and load, I usually use autoextend and monitor free space on = disk=20 just to avoid extra maintenance.

Tanel.

----- Original Message -----

From:=20 Jos=20

To: Multiple recipients of list = ORACLE-L=20

Sent: Saturday, July 19, 2003 = 5:49=20 PM

Subject: Re: Increase = tablespace, which=20 way is better?

Please correct me if I am wrong. With option 1, the datafile will = dynamically extend, 1M at a time and there is no guarantee the = datafile is=20 contiguous on disk. That will increase your I/O time when Oracle need = to find=20 data all over the disk. And also with autoextend, you may run out of = space on=20 that disk at any time (ie someone else created a big datafile on that = disk),=20 with option 2 at least you know you have 100M available for that = datafile on=20 the disk.

Jos

"Liu, Jack" <jliu@atla.com> = wrote:

Hi,
I want = to increase=20 tablespace, just want to know which way is better:
1. ALTER = TABLESPACE=20 SYSTEM
ADD DATAFILE '/u01/oradata/orcl/users02.dbf'
SIZE=20 1M
AUTOEXTEND ON
NEXT 1M
MAXSIZE 100M;

2.alter = tablespace system=20 add datafile '/u01/oradata/orcl/users02.dbf' size 100m;

Thanks,

Jack

Yahoo! Mobile
- Check & compose your =