Alter Tablespace Add Datafile in Oracle

October 27, 2023

To overcome tablespace full issue, you need to add datafile to the tablespace in Oracle otherwise you may face
“ORA-01658: unable to create INITIAL extent for segment in tablespace” which means tablespace is full and datafile is unable to extend further.

To oversome this issue, check the space allocated to the tablespace and add datafile to the tablespace.

Check current tablespace utilization, initial size, maxsize, autoextensible etc.

SQL> select file_name, bytes/1024/1024/1024, maxbytes/1024/1024/1024, autoextensible from dba_data_files where tablespace_name='ORAHOWDATA';

FILE_NAME                                BYTES/1024/1024/1024 MAXBYTES/1024/1024/1024 AUT
---------------------------------------- -------------------- ----------------------- ---
/dbdata/oracle/DBOH7/orahowdata.tbf                 31.999939              31.9999847 YES
/dbdata/oracle/DBOH7/orahowdata1.tbf               30.5289307              31.999847 YES

In above output we can see that two datafiles belongs to ORAHOWDATA tablespace having maxsize 32GB which is almost full.

You can also check the actual utilization using below query:

SQL> select sum(bytes)/1024/1024/1024 from dba_segments where tablespace_name='ORAHOWDATA';

SUM(BYTES)/1024/1024/1024
-------------------------
               62.51 GB

You can use alter tablespace command to extend tablespace in Oracle:

SQL> alter tablespace RAIDDATA add datafile 'FILE_LOCATION' size 100m autoextend on maxsize 31G;

Note: You can find file location from the 1st query as mentioned above. In case of ASM, use diskgroup name as a file location, or check below:
Adding datafile in ASM

Example:
SQL> alter tablespace RAIDDATA add datafile '/dbdata/oracle/DBOH7/orahowdata2.tbf' size 10m autoextend on maxsize 31G;

In case, if you have multiple datafiles with autoextend off, you can resize the existing datafile or enable to autoextend on.

To resize datafile, use below command:

SQL> alter database datafile 'datafile_name' resize 31G;

Must Read: