It's never big enough!!!

Yeah! Yeah! I'm talking about the USER tablespace out here. I wanted my USER Tablespace to be big, bigger, biggest , so I decided to extend it's size by adding a datafile to it.

Ok, so should I create a file and then give it to the tablespace or should I just tell it to create one and add it to the USER tablespace. Well, I tried both methods.

Here I created a file foo2.dbf and used it to extend the tablespace. As we see it failed with an error message.

SQL> alter tablespace users add datafile '/u02/app/oracle/oradata/ABC/datafile/foo2.dbf' size 500M;
alter tablespace users add datafile '/u02/app/oracle/oradata/ABC/datafile/foo2.dbf' size 500M
*
ERROR at line 1:
ORA-01119: error in creating database file
'/u02/app/oracle/oradata/ABC/datafile/foo2.dbf'
ORA-27038: created file already exists
Additional information: 1

Next, I added the datafile with creating the file beforehand, and it worked fine.

SQL> alter tablespace users add datafile '/u02/app/oracle/oradata/ABC/datafile/foo1.dbf' size 500M;

Tablespace altered.

SQL> select file_name, tablespace_name from dba_data_files where tablespace_name='USERS';

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u02/app/oracle/oradata/ABC/datafile/o1_mf_users_6kt8jgbw_.dbf
USERS

/u02/app/oracle/oradata/ABC/datafile/foo1.dbf
USERS


SQL>

Moral of the story, you don't need to create the file in advance to extend tablespace. You just have to pass the file name along with the path, and the rest is taken care for you.