Extend Oracle tablespace files

Sometimes you want to enlarge the tablespace file, when the size is not enough to handle transactions. This is the case when you get an error like:

ORA-01650: Unable to extend rollback segment RBS6
by %s in tablespace ROLLBACKSEGS

In this case, the rollback segment is not large enough to handle the database transaction. Now, you have to check if the autoextensible flag is set or if the datafile is too small.

First, find the correct database file of the rollback segment:

SELECT * FROM dba_rollback_segs;

Use the file ID from the result above to find the correct file on the file system:

SELECT * FROM dba_data_files;

Now enlarge the file:

ALTER DATABASE DATAFILE 'D:\ORACLE\ROLLBACKSEGS01.DBF'
RESIZE 1024m;

or set the autoextend to ON:

ALTER DATABASE DATAFILE 'C:\ORACLE\ROLLBACKSEGS01.DBF'
AUTOEXTEND ON NEXT 256m MAXSIZE 2048m;