Add A Datafile To An Oracle Tablespace

Brandon Mack September 22, 2015 3 Comments

The Error

We developed an Oracle application for Saskatoon’s Dakota Dunes Community Development Corporation that would take in grant applications. After a few years running we ran into an common error.

ORA-01653: unable to extend table WORKSPACE_NAME.TABLE_NAME by SOME_SIZE in tablespace TABLESPACE_NAME

If you receive the error mentioned above, it essentially means that you are out of space. The current datafile has a maximum size and it has been reached.

The Solution

The solution to this error is fairly simple. All you have to do is add a new datafile to the tablespace. First, you will have to login to sqlPlus. Once you are logged in, run the following SQL statements.

SELECT FILE_NAME, BYTES FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = ‘YOUR_TABLESPACE_NAME‘;

The output of this statement will give you the location of the current datafiles which you will need for the next statement.

ALTER TABLESPACE YOUR_TABLESPACE_NAME ADD DATAFILE ‘LOCATION_OF_CURRENT_DATAFILES/NEW_DATAFILE.dbf‘ SIZE 50M AUTOEXTEND ON NEXT 512K MAXSIZE 500M;

This statement will add a new datafile to the existing tablespace called NEW_DATAFILE.dbf starting at size 50M and will autoextend itself until it creates the maximum size of 500M. You can add more datafiles as you run out of space. Be careful though, adding more datafiles may take its toll on the database performance.