Add space to database

DBA's frequently need to add space to a database to cater for database growth. Space can be added to a tablespace by adding more datafiles or by resizing existing datafiles. Use one of the following commands to add space:

SQL> ALTER TABLESPACE ts1 ADD DATAFILE '/path/to/file/name' SIZE 100M;

SQL> ALTER DATABASE DATAFILE '/path/to/data/file/name' RESIZE 200M;

Before adding files, do a select from SYS.DBA_DATA_FILES to get a feel for the sizing and naming standard used on the particular database.

Notes for installations using ASM

ASM used Oracle Managed Files (OMF) by default and will allow the datafiles to grow automatically provided ASM has enough disks allocated to it.

Notes for installations using File Systems

When using file systems for storing data files, ensure you pick the right filesystem type and parameters. For example, a Veritas Filesystem will be much faster than a normal UFS fileystem.

Notes for installations using RAW Volumes

If your installation uses RAW Volumes, pre-allocate the volume or get your Operating System Support to create if for you. This is typically done using commands like: vxassist (Veritas) or lvcreate (HP-UX).

Unless you know exactly what you are doing, never try to resize files on RAW Volumes.

Note that Oracle is busy desupporting RAW volumes. RAW can still be used in Oracle 11gR2 (upgraded databases). However, one cannot create NEW databases on RAW anymore. RAW will be completely desupported in Oracle 12.