Re: Extending Tablespace

ben dunlop wrote:
>
> Hi All,
>
> How would I go about a simple method to extend a table space from the
> command line ?
>
> If anyone can assist in the syntax and methodology I would be very
> grateful.
>
> Regards
>
> Ben {the new user}

Yup, it is easy look at ORACLE SERVER SQL Reference for the ALTER DATABASE command (should be around chapter 4 page 16-> 30.

Here is an example of what you need to do... Look at the data files for the tablespace in question with the following query.

Select tablespace_name,file_name, (bytes/1024/1024) from dba_data_files
where tablespace_name = <tablespace which needs to be extended>;

which will produce a listing of all the datafiles and their size (in MB) associated with a tablespace.. Note, there may be more than 1 datafile.

example:

Tablespace   File_name                                     Size MB
-----------  -------------------------------------------   -------
DATA         /ora01/oradata/prod/data/prod_data_01.dbf     100

then you would issue:

alter database datafile
'<data_file to be resize>' resize <size in k or m>;

example:
alter database datafile
'/ora01/oradata/prod/data/prod_data_01.dbf' resize 150M;

That is it...
BTW:
1) You can also add a datafile, but don't do that unless you are attempting to spread I/O over another device.... 2) You can set the datafile to autoextend which I would not recommend -- i've seen it lead to too many problems!

sincerely,
darryl dB Balaski
www.rdbms.org Received on Tue Jan 12 1999 - 12:17:22 CST