Now, this customer has multiple sites, and the database is replicated between all of the sites, and all were experiencing the issue.
We did some digging, and ended up looking to the Oracle Alert logs which were in
/u01/app/oracle/admin/<instance>/bdump
The alert log is called:
alert_<instance>.log
What we found were entries like this:
ORA-1654: unable to extend index <schema>.<index> by 128 in tablespace <tablespace>
This was a pretty clear indication of what the issue was, because the timestamps on the messages coincided with the timestamps on the IVR messages.
So we knew that the tablespace for the index was full. How do you fix that.
Well, you need to add more space to the tablespace. Here's a simple way to do that:
alter tablespace <tablespace>
add datafile '<data file path and name>'
size <size>m
autoextend off
This will add a datafile to the tablespace with a size as specified.
We did this and the errors went away. But now the question is how can we be proactive and prevent this in the future.
Well, periodically the free space in all of the tablespaces should be checked.
Here's a simple query to figure out how much free space is available in each tablespace:
select tablespace_name,
sum(bytes)/1024/1024 MB_Free
from dba_free_space
group by tablespace_name
order by sum(bytes)/1024/1024;
This will give you a list of each tablespace and the amount of free space in Megabytes.
If you see the free space of one of the tablespaces getting real low, it's time to add more.
