ORA-01653: Adding, Resizing of datafiles in Oracle

Datafiles are part of the Oracle Database, these are the files that have the actual data in them.

Datafiles can be located on ASM or file system on disk. So either you can add datafile in ASM or else you can add datafiles to tablespace in Oracle located on file system.

There are two different ways how these datafiles are configured and maintained: Oracle Managed Files (A pre-defined folder on the server which holds all datafiles) and non-oracle managed files. 

For older installations, the non-oracle managed files was commonly used, for more recent installations, the database is usually configured with Oracle Managed files.

Difference between Oracle Managed Files and Filesystem (Non-Oracle Managed files)

Oracle Managed Files (OMF) are automatically created and managed by the Oracle Database. A location is specified during the setup of the database, and the database will create all datafiles in that area (unless it is explicitly included in a statement). For non-oracle managed files, the datafiles have to be created with a /u01/app/oracle/oradata... clause - to tell the database, where the file should be stored.

How to determine if either OMF or Non-OMF is used?

select name from v$datafile;

You will either see names like this:

/u01/app/oracle/oradata/asrblg/system01.dbf
or,
/u01/app/oracle/oradata/asrblg/o1_uyetvsnh_euystv.dbf

The "o1_uy" is showing, that the installation is configured with Oracle Managed Files;
whereas the SYSTEM01.dbf is showing that the database is not using Oracle Managed Files.

Extending Tablespaces

The most common reason why it is required to reconfigure datafiles is to either add or resize a datafile. Datafiles can either be configured with a fixed size, or they can allocate the space dynamically as they are growing.

Before you add or resize a datafile, make sure, that there is enough space available on the drive where the datafiles are stored. To find out where they are, use:

select name from v$datafile;

This will show you the diskdrive and the folder on the database server where the datafiles are stored.

When a datafile is completely full, you will see an error message like the one below - these errors can be found in the alertlog of the database as well.

ORA-01653: unable to extend table ASRBLG by 8192 in tablespace ASRBLG

The error message in this example shows, that the ASRBLG tablespace is full and there is no space left to store more data in it.

There are three options to resolve this situation:

1. Resize
2. Enable autoextension
3. Add a new datafile

Before adding or resizing a datafile, you MUST make sure there is enough free diskspace available on the harddrive!

Resizing and Auto-Extension are the recommended options – there is usually no need to add a new datafile, unless the maximum size of the file (which is 30GB) is reached!

RESIZING A DATAFILE

Resizing the datafile will "grow" the actual file to the specified value. In order to resize a datafile, please follow the steps below:

1. Identify the proper datafile
2. Resize the datafile
3. Enable automatic extension

Use the SQL Statement below, to get a list of the datafiles. This procedure is the same for Oracle Managed Files and Non-OMF.

set underline off
set linesize 4000
select file# "DATAFILE#" ,name "DATAFILE" from v$datafile;

DATAFILE#     DATAFILE
-----------    ----------------

1 /u01/app/oracle/oradata/asrblg/O1_MF_SYSTEM_F9HZLSPQ_.DBF
2 /u01/app/oracle/oradata/asrblg/O1_MF_SYSAUX_F9HZLXBC_.DBF
3 /u01/app/oracle/oradata/asrblg/O1_MF_UNDOTBS1_F9HZLZ5D_.DBF
4 /u01/app/oracle/oradata/asrblg/O1_MF_USERS_F9HZMOOB_.DBF
5 /u01/app/oracle/oradata/asrblg/O1_MF_V8ARCHIV_F9HZMOV6_.DBF
6 /u01/app/oracle/oradata/asrblg/O1_MF_V8CONF_F9HZMW7T_.DBF
7 /u01/app/oracle/oradata/asrblg/O1_MF_V8INDEX_F9HZN08M_.DBF
8 /u01/app/oracle/oradata/asrblg/O1_MF_V8LARGE_F9HZN3MO_.DBF
9 /u01/app/oracle/oradata/asrblg/O1_MF_V8MAIN_F9HZN6Y7_.DBF

In the example above, the datafile has the file# 8 - it is the only datafile in the ASRBLG tablespace (in this example), but there might be more datafiles, you may resize or extend any datafile in the affected tablespace.

alter database datafile 8 resize 2048M;
alter database datafile 8 autoextend on next 512M maxsize unlimited;

Database altered.

The commands above resize the datafile to a value, you need to specify. You may use values like "10G" for 10 Gigabyte or "512M" for 512 Megabytes. (Or any other value)

When you see the "Database altered" confirmation in SQLplus, the datafile has been succesfully resized and the automatic extension has been enabled.

The auto-extension will allow the database to store data in this datafile, until it reaches the 30GB limit. Once this limit is hit, a new datafile will have to be added.

ADDING A DATAFILE

The steps to add a datafile are:

- Identify either OMF or Non-OMF is used
- In case you DO NOT have Oracle Managed Files, you will need to specify the location and a name of the datafile.
- Add the datafile

Example:
ORA-01653: unable to extend table ASRBLG by 8192 in tablespace ASRBLG

This tells us, that the ASRBLG tablespace is full and a datafile has to be added.

The command WITH Oracle Managed Files is:

alter tablespace ASRBLG add datafile size 2048M;

Tablespace altered.

WITHOUT Oracle Managed Files:

- Identify the location of the datafiles
- Assign a new, unique name to the datafile

First, get the name for the location and the existing filenames by querying the V$DATAFILE view.

We will use the same example as above for the ASRBLG datafile - this can of course be a different tablespace

select name from v$datafile where name like '%ASRBLG%';

/u01/app/oracle/oradata/asrblg/ASRBLG01.DBF
/u01/app/oracle/oradata/asrblg/ASRBLG02.DBF
/u01/app/oracle/oradata/asrblg/ASRBLG03.DBF

The datafile that you will add needs to have a unique name, it is recommended, to take the number from the last existing datafile (03) and increase it by one.

So the new name for the datafile in this example will be /u01/app/oracle/oradata/asrblg/ASRBLG04.DBF

Adding the datafile:

ALTER TABLESPACE V8LARGE add datafile '/u01/app/oracle/oradata/asrblg/ASRBLG04.DBF' size 1024M; 

You can change the size (1G, 10G, 500MB..) so it matches the expected size of the file in the environment.

 It is recommended, to allow automatic extension after adding the new datafile.

ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/asrblg/ASRBLG04.DBF' autoextend on next 512M maxsize unlimited;

Useful SQL Statements for managing tablespaces:

Status, size and autoextension for datafiles:

select a.tablespace_name, a.avail, b.free, round(b.free/nvl(a.avail,18496)*100,2) PERC_FREE, CASE WHEN round(b.free/nvl(a.avail,0)*100,2) < 10 THEN 'DANGER' WHEN round(b.free/nvl(a.avail,0)*100,2) > 10 AND round(b.free/nvl(a.avail,0)*100,2)<20 THEN 'WARNING' ELSE 'OK' END STATUS from ( select tablespace_name,round(sum(bytes)/1048576,2) AVAIL from dba_data_files group by tablespace_name ) a, (select tablespace_name,round(sum(bytes)/1048576,2) FREE from dba_free_space group by tablespace_name) b where a.tablespace_name=b.tablespace_name;

List all datafiles and tablespaces:

select file_name, tablespace_name from dba_data_files;

Hope it worked !! 🙂