Manage a Tablespace in the Oracle Database

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: 

  1. 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.

  1. 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.

  1. 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. 

  1. 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).


Related Articles

New Articles