Apex upgrade and ORA-01653: unable to extend table SYS.SOURCE$ by 1024 in tablespace SYSTEM

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

If you, like me, have a long trail of Oracle Application Express upgrades over the years, at a certain point you might hit an error like the following while executing the script apexins.sql:

ORA-01653: unable to extend table SYS.SOURCE$ by 1024 in tablespace SYSTEM

This error is returned because you don't have enough room in the SYSTEM tablespace. The error happens because you, like me, didn't check the free space left before launching the script, something that you easily do by querying DBA_FREE_SPACE:

select sum(bytes)/(1024*1024) as MB
from dba_free_space
where tablespace_name='SYSTEM';

The same error code may appear against other objects, depending on when the tablespace became full, but in any case a possible solution is to execute the following statement, provided you have enough free disk space:

ALTER DATABASE DATAFILE 'C:\oraclexe\oradata\XE\SYSTEM.DBF' RESIZE new_size M;

where new_size is the current size in megabytes of the datafile holding the SYSTEM tablespace plus the space required for the new apex repository, which is estimated at 85MB as per installation guide instructions. There are also specific requirements for the apex tablespace, but if you are installing it into SYSAUX, the tablespace should be set to autoextend mode, so there shouldn't be any problems unless you have no physical disk space left.
If you are installing in a different tablespace, make sure autoextend is on or verify the available space on this tablespace too.

You need also to ensure that the path C:\oraclexe\oradata\XE\SYSTEM.DBF applies to your machine or change it accordingly.

After successfully executing the statement above and before retrying the upgrade, you should also remove the leftovers from the previous installation attempt and you can easily do that by dropping the newly created apex owner, that in my case was FLOWS_030100:

DROP USER FLOWS_030100 CASCADE;

Be sure not to drop the current apex owner, FLOWS_030000 in my case.

You should be now ready to restart the upgrade script.
While i was at this, i removed older apex repositories like FLOWS_020100, that were just taking space in the SYSTEM tablespace (executing DROP USER statements like that above).
Please note that if you have a couple of old apex repositories, you might get back the space required by the new version in this fashion, in other words it could be that you don't need to resize the SYSTEM tablespace at all.

See message translations for ORA-01653 and search additional resources.