Tablespace datafiles must be given sufficient disk space to prevent performance issues. When setting up tablespaces on your server, you can set the datafile size to automatically extend to a specified maximum value, when needed. If you see the error: "ORA-01654: unable to extend index" the auto-extensible option is not set for the datafile.
To troubleshoot the "ORA-01654: unable to extend index" error:
- Run the following query to check whether the auto-extensible option is enabled and if there is free space available in the datafile:
SELECT de.file_id, df.file_name, df.AUTOEXTENSIBLE, df.bytes/1024/1024, SUM(de.bytes)/1024/1024
FROM dba_extentions de, dba_data_files df
WHERE de.file_id=df.file_id GROUP BY de.file_id,df.file_name, AUTOEXTENSIBLE, df.bytes/1024/1024;
If the above query shows any free space, perform step 2. If there is no free space, go to step 3.
- Run the following query to combine all free space into one large chunk.
ALTER TABLESPACE aglindex COALESCE;
Verify if you can now operate the RiskVision Server as before.
- If the issue persists, run the following query to add a new datafile to the tablespace:
ALTER TABLESPACE aglindex ADD DATAFILE '~/oradata/agl/datafile.dbf' AUTOEXTEND ON NEXT 500M MAXSIZE 20G;
This will add a new datafile.
- If the issue persists, run the following query to change the size of the existing datafile. Run this query only when you have reached the maximum number of datafiles allowed for a tablespace.
ALTER DATABASE DATAFILE '<~/datafile.dbf>' RESIZE 10G;
The sizes can be specified in the megabyte, gigabyte or terabyte. You must check your free disk space before specifying the value for NEXT (the minimum amount that a datafile can increment when it extends) and MAXSIZE (the maximum amount that a datafile can automatically extend).
