How to add datafile in oracle database

This document describe how to add a datafile to Non-ASM database.

Step 1:

Find the name of the database.

SQL> select name,open_mode from v$database;

NAME              OPEN_MODE
---------             --------------------
PRIMARY        READ WRITE


Find the tablespace name and datafile location to which we need to add datafile.


SQL> select tablespace_name, file_name from dba_data_files where tablespace_name like  'TEST';

TABLESPACE_NAME                FILE_NAME
------------------------------           -------------------------------------------------------
TEST                      /u02/oradata/PRIMARY/test01.dbf


Step 2: Add datafile to the tablespace

SQL> alter tablespace TEST add datafile '/u02/oradata/PRIMARY/test02.dbf' size 50m autoextend on next 10m maxsize unlimited;

Tablespace altered.

SQL>

Verify the added datafile

SQL> select tablespace_name, file_name from dba_data_files where tablespace_name like  'TEST';

TABLESPACE_NAME                FILE_NAME
------------------------------           -------------------------------------------------------
TEST                      /u02/oradata/PRIMARY/test01.dbf
TEST                     /u02/oradata/PRIMARY/test02.dbf

SQL>

Thanks for your patience to view this post............