Re: extend table question

frankyp22_at_yahoo.com (Franky) wrote in message news:<fb49377c.0108290839.5807c30e_at_posting.google.com>...
> Hi,
> I apologize for my ignorance, as I have had no formal database
> training.
>
> I have Oracle 7.3 and got the error message (ORA-01653) about needing
> to extend the tablespace. I use these commands to do it:
> 1) ALTER TABLESPACE tablespace OFFLINE
> 2) ALTER TABLESPACE tablespace
> ADD DATAFILE 'file' [SIZE integer [K|M] ] REUSE
> NEXT integer [K|M]
> MAXSIZ UNLIMITED
> 3 ALTER TABLESPACE tablespace ONLINE
>
> My question is, the .dat file that we currently have is 524288000
> bytes.
> What size should I resize it to? When I specify in the #2 command, do
> I say in the NEXT integer part, how much I want to increase it by, or
> the total?
> In other words, do I say:
> NEXT 200 M
> to say increase it by 200 megs.
> Or say:
> NEXT 724 M
> to say that should be the total size.
> Does this make sense?
> Thanks!

  Hi,

   Say you have a tablespace called USERS and the datafile called user01.dat. You can do two things:

1. Just Issue a command to resize user01.dat to something larger.

     eg. alter database datafile 'c:\blahblah\user01.dat' resize 1000M;
      (right now you have 524288000 Bytes which is 500M)

  2. Add another datafile to your existing tablespace, USERS (in this example)
     by issuing the command: (by the way, there is no need to bring the
     tablespace down)

      eg.  alter tablespace users add datafile 'c:\blahblah\users02.dat'
           size 500M;

      In this case, you will have two datafiles with 500M each. Autoextend
      feature is not recommended. Also, make sure you name your datafile
      with consistent name, eg user02.dat, users03.dat etc

Allan W. Tham
DBA Received on Thu Aug 30 2001 - 07:39:59 CEST