ORA-1654: unable to extend index in tablespace

by

From Alert logs

ORA-1654: unable to extend index SCHEMA.IN021S1 by 128 in tablespace TBS_01
2024-03-23T16:26:11.156475+05:30
ORA-1654: unable to extend index SCHEMA.IN021S1 by 128 in tablespace TBS_01
2024-03-23T16:26:19.194628+05:30
  1. Find the status of tablespace details with free size:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Select a.tablespace_name,sum(a.tots/1048576) Tot_Size, sum(a.sumb/1024) Tot_Free, sum(a.sumb)*100/sum(a.tots) Pct_Free, ceil((((sum(a.tots) * 15) - (sum(a.sumb)*100))/85 )/1048576) Min_Add
from (select tablespace_name,0 tots,sum(bytes) sumb
from dba_free_space a
group by tablespace_name
union
Select tablespace_name,sum(bytes) tots,0 from dba_data_files
group by tablespace_name) a group by a.tablespace_name
having sum(a.sumb)*100/sum(a.tots) < 10
order by pct_free;
 
*********************
OUTPUT:
*********************
 
TABLESPACE_NAME                  TOT_SIZE   TOT_FREE   PCT_FREE    MIN_ADD
------------------------------ ---------- ---------- ---------- ----------
  TBS_01                       131071.938          0          0      23131

2. Check the datafiles size and path present in respective tablespace:

1
2
3
4
5
6
7
8
9
10
11
select FILE_NAME,FILE_ID,TABLESPACE_NAME,BYTES/1024/1024 from dba_data_files where TABLESPACE_NAME='TBS_01';
 
***********************
OUTPUT:
***********************
 
FILE_NAME                          FILE_ID TABLESPACE_NAME    BYTES/1024/1024
---------------------------------- ------- ----------------   ---------------
+DATA/SID/DATAFILE/tbs_01.281.113    2      TBS_01              32767.9844
 
+DATA/SID/DATAFILE/tbs_01.354.113    13     TBS_01              32767.9844
  1. Resize if required or add a new datafile for respective tablespace:(Replace the datafile path, tablespace_name and sizes correctly)
1
ALTER TABLESPACE TBS_01 ADD DATAFILE '+DATA' SIZE 2G AUTOEXTEND ON NEXT 1G MAXSIZE 32767M;

4. Give a Log Switch and monitor the Alert Log:

1
alter system switch logfile;