Oracle7 Server Administrator's Guide

Changing a Datafile's Size

This section describes the various ways to alter the size of a datafile, and includes the following topics:

Enabling and Disabling Automatic Extension for a Datafile

Manually Resizing a Datafile

Enabling and Disabling Automatic Extension for a Datafile

You can create datafiles or alter existing datafiles so that they automatically increase in size when more space is needed in the database. The files increase in specified increments up to a specified maximum.

Setting your datafiles to extend automatically results in the following:

reduces the need for immediate intervention when a tablespace runs out of space

ensures applications will not halt because of failures to allocate extents

creates FILEXT$, which contains information about the autoextend characteristics of a datafile

Note: FILEXT$ is not created with the data dictionary scripts, so there is no easy way to create database views on it. Thus, catalog scripts will be unsuccessful if you attempt to create a database view on FILEXT$. FILEXT$ is the only place you can query where datafiles have autoextend turned on, and the current settings of their parameter values.

You can specify automatic file extension when you create datafiles via the following SQL commands:

CREATE DATABASE

CREATE TABLESPACE

ALTER TABLESPACE

You can enable or disable automatic file extension for existing datafiles, or manually resize a datafile using the SQL command ALTER DATABASE.

The following example enables automatic extension for a datafile, FILENAME2, added to the USERS tablespace:

ALTER TABLESPACE users
   ADD DATAFILE 'filename2' SIZE 10M
      AUTOEXTEND ON
      NEXT 512K
      MAXSIZE 250M

The value of NEXT is the minimum size of the increments added to the file when it extends. The value of MAXSIZE is the maximum size to which the file can automatically extend.

The next example disables automatic extension for the datafile FILENAME2:

ALTER DATABASE DATAFILE 'filename2'
   AUTOEXTEND OFF

See Also: For more information about the SQL commands for creating or altering datafiles, see the .

Manually Resizing a Datafile

You can manually increase or decrease the size of a datafile using the ALTER DATABASE command.

Because you can change the sizes of datafiles, you can add more space to your database without adding more datafiles. This is beneficial if you are concerned about reaching the maximum number of datafiles allowed in your database.

Manually reducing the sizes of datafiles allows you to reclaim unused space in the database. This is useful for correcting errors in estimates of space requirements.

In this example, assume that the datafile FILENAME2 has extended up to 250M. However, because its tablespace now stores smaller objects, the datafile can be reduced in size.

The following command decreases the size of datafile FILENAME2:

ALTER DATABASE DATAFILE 'filename2'
   RESIZE 100M

Note: It is not always possible to decrease the size of a file to a specific value.

See Also: For more information about the implications resizing files has for downgrading, see the Oracle7 Server Migration.

For more information about the ALTER DATABASE command, see the .