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 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.
The output of this statement will give you the location of the current datafiles which you will need for the next statement.
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.
Very clear steps to add datafile in Oracle.
Thanks for sharing the steps. Very well explained. I was searching for this to extend tablespace size in Oracle.
Glad it could help! 🙂