Adding a datafile to an Oracle ASM (Automatic Storage Management) database involves several steps. ASM is a feature of Oracle Database that manages storage and simplifies database file management.

Here’s a general outline of the steps to add a datafile to an ASM database.

Before start to adding the datafile in ASM we need below 2 things

1) Diskgroup Name which is necceseary for adding datafile
2) Tablespace Name which is neccesarry for adding datafile. We need to find out tablespace created on which diskgroup

Here I have taken Diskgroup Name “+DATA” and Tablespace Name “USERS”

Lets start the steps

First, Check Diskgroup name by using below query

SQL> SELECT name, free_mb, total_mb, free_mb/total_mb*100 as percentage FROM v$asm_diskgroup;

NAME FREE_MB TOTAL_MB PERCENTAGE

DATA 2000 4000 50
RECO 500 100 10

As you can see we have 2 Diskgroups: +DATA and +RECO.

Now let us Check the diskgroup name which is mapped to YOUR_TABLESPACE .

SQL> select tablespace_name,file_name from dba_data_files where tablespace_name=’YOUR_TABLESPACE’;

TABLESPACE FILE_NAME

YOUR_TABLESPACE +DATA/DB_NAME/datafile/YOUR_TABLESPACE.265.982605875

Example for adding a new datafile to tablespace

   ALTER TABLESPACE your_tablespace_name
   ADD DATAFILE '+diskgroup_name' SIZE your_size;

Replace your_tablespace_name with the name of your tablespace, +diskgroup_name with the ASM disk group name where you want to add the datafile (using a ‘+’ sign before the disk group name indicates it’s an ASM path), and your_size with the size of the datafile.

Confirm that the datafile has been added successfully by querying the database. You can check the datafile in the database’s data dictionary views.

   SELECT file_name, tablespace_name, bytes
   FROM dba_data_files
   WHERE tablespace_name = 'your_tablespace_name';