Re: Alter tablespace add datafile file size error

Yes, but I don't know to what size it will default - infinite or smallest possible. Based on your error, I would say you are right - it will try to allocate all of your disk, or max file size, which would be quite large.

hth, Frank

Vaughan Jackson wrote:

> Hi Frank,
>
> Are you telling me that if I fail to provide a size argument
> in the statement below, it defaults to infinite size?
>
> Thanks for your help,
>
> Vaughan.
>
> In article <8s1htc$o66$1_at_porthos.nl.uu.net>,
> "Frank" <frankbo_at_interaccess.nl> wrote:
> > You must specify the size of your datafile:
> >
> > SQL> alter tablespace IMETABLES
> > > 2 add datafile '/data/OracleDatafiles/imetables2.dbf'
 size 1000M
> > > 3 autoextend
> > > 4 on next 1M
> > > 5 maxsize 1500M
> > > 6 /
> > >
> > I would *never* use a on next 1M - this may happen too often and kill
> > perofmance.
> > If your start size would be 1G, and maxsize 1.5G, try to end there in
 5 to
> > 20 steps max:
> > that would result in on next 100M (down to on next 25M).
> > Similary, 500 - 1500M: next would be in the 200M .. 50M range.
> > --
> > Frank
> > Vaughan Jackson <vaughan_jackson_at_my-deja.com> schreef in berichtnieuws
> > 8rt07e$a79$1_at_nnrp1.deja.com...
> > > Hi,
> > >
> > > I have been trying to add a second datafile to a tablespace using
 the
> > > following syntax:
> > >
> > > SQL> alter tablespace IMETABLES
> > > 2 add datafile '/data/OracleDatafiles/imetables2.dbf'
> > > 3 autoextend on
> > > 4 next 1M
> > > 5 maxsize 1500M
> > > 6 /
> > >
> > > However, I find that no matter what maxsize value I provide (even
 100M),
> > > the datafile created keeps growing (at moment of creation), until
> > > it takes up ALL of the available diskspace. At this point, an error
> > > message appears, basically saying that no more space is left:
> > >
> > > alter tablespace IMETABLES
> > > *
> > > ERROR at line 1:
> > > ORA-19510: failed to set size of 578816 blocks for file
> > > "/data/OracleDatafiles/imetables2.dbf" (blocksize=4096)
> > > ORA-27059: skgfrsz: could not reduce file size
> > > SVR4 Error: 28: No space left on device
> > > Additional information: 2
> > > ORA-19502: write error on file

 "/data/OracleDatafiles/imetables2.dbf",
> > > blockno
> > > 578561 (blocksize=4096)
> > > ORA-27072: skgfdisp: I/O error
> > > SVR4 Error: 25: Inappropriate ioctl for device
> > > Additional information: 578561
> > >
> > > This message does not make sense for a number of reasons, including:
> > >
> > > 1. there is over 2GB available, but I have limited the maxsize to
 1500M;
> > > 2. it says it has failed to set the size of a 578816 block file,
> > > whereas the OS is telling me that we have over 4M blocks available
> > > (different blocks?):
> > >
> > > [VJ]<vjackson> scorpius:/data/OracleDatafiles df .
> > > /data (/dev/dsk/c0t0d0s7 ): 4747446 blocks 469300
 files
> > >
> > > Other details: Oracle 8i, Solaris 2.7.
> > >
> > > Am I doing something wrong here?
> > >
> > > Thanks,
> > >
> > > Vaughan Jackson
> > >
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
> >
> >
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Mon Oct 16 2000 - 11:00:32 CDT