You may get the error 'ORA-01653: unable to extend table TABLE_NAME by XXX in tablespace TABLESPACE_NAME' in various cases.
Some of the possible scenarios when you can get this error are:
- generating one dispatch for two orders via HS0C but job is failing
- buying team is trying to input costs into PD0A
- trying to alter or add POs in PS0A
- trying to delete the pick via di0d in TEST CIMS
The underlying error 'ORA-01653: unable to extend table TABLE_NAME by XXX in tablespace TABLESPACE_NAME' happens because a background process is trying to create a record in the database but it has found a lack of available disk space to add data in one of its data files.
Please contact Customer Support and we will come back to you with the root cause and solution.
In order to get rid of this error, the simplest solution is to resize the data file associated with the failed tablespace, giving it available space to resume inserting data.
- Connect to the database as a DBA.
- Identify which data file or files are associated with the failed tablespace. It might have expanded into multiple data files. To identify the data files, use the following SQL statement:
select a.owner, b.file_name
from dba_tables a, dba_data_files b
where a.tablespace_name=b.tablespace_name and a.table_name='Your table name'
- Resize the data file using the following SQL statement, providing its new size in Megabytes (M) or Gigabytes (G).
alter database datafile '/eva_cims2/oradata/B/dbsB.igtd3'
- In case the Disk volume has no further space to grow and a new data file needs to be created in a different volume, use a SQL similar to this:
ALTER TABLESPACE tablespace_name ADD DATAFILE '/eva_cims2/oradata/B/dbsB.sp6'Note: The queries will need to be adjusted according to the client's implementation and architecture.