ORA-1654: unable to extend index in tablespace
1 min readFrom 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
- Find the status of tablespace details with free size:
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:
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
- Resize if required or add a new datafile for respective tablespace:(Replace the datafile path, tablespace_name and sizes correctly)
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:
alter system switch logfile;
