We had a problem with our prod environment. Suddenly the exception began to appear.
ORA-01654: unable to extend index EMA.TRANSFERI2 by 128 in tablespace SYSTEM
As the solution of the problem my collegue added new datafile. But the question is, why the autoextend mechanism didn't worked? I'm not DBA, but I checked the configuration and it seems ok to me. It occurs only on prod environment, so I would rather avoid experimenting.
We have the table in system tablespace, which I already know, should be moved to users tablespace. But anyway, autoextend should work also on system tablepsace. Here is my config of table, datafiles and tablespace
TABLESPACE_NAME | PCT_FREE | PCT_USED | INITIAL_EXTENT | NEXT_EXTENT | MIN_EXTENTS | MAX_EXTENTS | PCT_INCREASE SYSTEM | 10 | 40 | 65536 | 1048576 | 1 | 2147483645 | null
FILE_NAME | FILE_ID | TABLESPACE_NAME | BYTES | BLOCKS | STATUS | RELATIVE_FNO | AUTOEXTENSIBLE | MAXBYTES | MAXBLOCKS | INCREMENT_BY | USER_BYTES | USER_BLOCKS | ONLINE_STATUS /u01/app/oracle/oradata/XE/system.dbf | 1 | SYSTEM | 629145600 | 76800 | AVAILABLE | 1 | YES | 629145600 | 76800 | 1280 | 628097024 | 76672 | SYSTEM /u01/app/oracle/oradata/XE/system2.dbf | 5 | SYSTEM | 1048576000 | 128000 | AVAILABLE | 5 | YES | 2147483648 | 262144 | 25600 | 1047527424 | 127872 | SYSTEM
TABLESPACE_NAME | BLOCK_SIZE | INITIAL_EXTENT | NEXT_EXTENT | MIN_EXTENTS | MAX_EXTENTS | MAX_SIZE | PCT_INCREASE | MIN_EXTLEN | STATUS | CONTENTS | ALLOCATION_TYPE | SEGMENT_SPACE_MANAGEMENT | BIGFILE SYSTEM | 8192 | 65536 | null | 1 | 2147483645 | 2147483645 | 65536 | ONLINE | PERMANENT | LOCAL | SYSTEM | MANUAL | NO
The MAXBYTES value for your system.dbf file is set to 629145600, so when your file size reached that limit, it couldn't be extended any further. It had autoextended up to that point, but wouldn't extend beyond the soft limit that had been specified for the file. That was set when the tablespace was created, using the autoextend MAXSIZE clause.
The limit may have been set because of the size of the underlying file system, to cause an error in case of runaway/unexpected growth, unintentionally, or for some other reason now known only to whoever set the database up.
As an alternative to adding a second data file, your DBA could have increased the soft limit on the existing file with alter database. But neither should be done lightly; the reason for the original restriction should be understood (especially if the filesystem could run out of space as a result of an increase) and the reason for growth should be examined too.