Fix ORA-01653: unable to extend table FMEMC.SOURCE_OBJECTS

By default, the datafiles where migration center data is stored are created to extend unlimited. Nevertheless, a max size can be set to the datafiles (after installation or during a custom installation) so after a while, when trying to scan or import more data the following error may occur:

 ORA-01653: unable to extend table FMEMC.SOURCE_OBJECTS_HISTORY

or

 ORA-01653: unable to extend table FMEMC.SOURCE_OBJECTS

This error typically has to two causes:

1) there is no enough disk space for extending the datafile

2) the datafiles have reached their maximum size. This may happen either because a max size to the datafile was set during or after installation or because the maximum allowed size by Oracle has been reached. Max. allowed size by Oracle is 16 GB for a block size of 4KB and 32 GB for a block size of 8 KB.

For solving the error the following action might be performed:

1) Get the location where datafiles are store in the Oracle server by running the following query connected as a DBA user (SYSTEM or SYS):

SELECT FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'FMEMC_DATA';

2) Make sure there is enough free space on the drive where the datafiles are located.

3) Set the datafile to extend unlimited so they automatically extend when necessary (replace the paths with the values returned in the step 1)

ALTER DATABASE 
DATAFILE 'D:\ORACLE\ORADATA\MIGDB1\FMEMC_DATA1.DBF'
AUTOEXTEND ON NEXT 10485760 MAXSIZE UNLIMITED;

ALTER DATABASE
DATAFILE 'D:\ORACLE\ORADATA\MIGDB1\FMEMC_DATA2.DBF'
AUTOEXTEND ON NEXT 10485760 MAXSIZE UNLIMITED;

4) As an alternative to 3) you can add one or more datafiles with the following command

ALTER TABLESPACE FMEMC_DATA
ADD DATAFILE 'C:\ORACLE\ORADATA\MIGDB1\FMEMC_DATA3.DBF'
SIZE 20M
AUTOEXTEND ON NEXT 10485760 MAXSIZE UNLIMITED;

 

 

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.