Resumable Timeouts caused by ORA-01652 but Tablespace is not full

* when you have a resumable timeout set so that SQL waiting for tablespace resources can pause until that space becomes available.

I had this interesting problem the other day, and while the solution is reasonably obvious, it didn't occur to me immediately at the time.

You can detect resumable pauses with this statement...

select user_id, instance_id, status, name, error_msg, suspend_time, resume_time from dba_resumable;

This then shows one or more sessions where the resumable wait has cleared or an error is display showing a wait in progress. At my company we monitor for these waits extending to long and then report them to call-out.

In this case, as always, this is the error message shown. There were 2 sessions waiting on a large data warehouse.

ORA-01652: unable to extend temp segment by string in tablespace cma_ts1

Earlier in the day, I had got the same message and when I checked, the tablespace was indeed almost completely full. It was on ASM and the single file in the tablespace was 1.5Tb. Because it's a production database I didn't hang around and increased the datafile to 2Tb.

ALTER DATABASE DATAFILE '+DATA_DG/dgb_tsm1/datafile/cma_ts1.1145.851597989' AUTOEXTEND ON MAXSIZE 2000G
database datafile '+DATA_DG/DGB_TSM1/DATAFILE/CMA_TS1.1145.851597989' altered.

So with an additional 500Gb, I figured we'd be ok for some time. However 3 hours later I was called out again. Same error.

However now, only 91% of the data file was being used. This equates to 180Gb still free.

Of course what I didn't immediately spot was that just checking the size of the free space isn't enough. You must also consider the amount of space being requested by running processes.

This statement gives you the tablespace usage details.

select file_name, file_id, round(user_bytes/1024/1024,2) "Used(Mb)", round(user_bytes/1024/1024/1024,2) "Used(Gb)", status, autoextensible
from dba_data_files c
where tablespace_name = 'CMA_TS1' order by file_id desc;

And this the percent used. (There are other ways).

select tablespace_name, used_percent, tablespace_size, used_space, (tablespace_size - used_space) free
from dba_tablespace_usage_metrics
where tablespace_name = 'CMA_TS1';

Having obtained the space used figures, take a look at the amount of space currently being requested by the running processes.

select segment_name, bytes/1048576/1024 from dba_segments where tablespace_name='CMA_TS1'
order by 2 desc;

If the segment name is a number rather than an object name, then this represents those temporary segments we're looking for. In my case there were 2 (as expected), and these were sized at 337Gb and 240Gb. Clearly neither of these next extent requirements would fit into the free space available.

I could have added more diskspace, but the prudent thing to do was to locate the session owners and have a quiet word with them. Having done this and explained the realities of initial and next extents, we killed the processes and the scripts were rewritten.

HappyJohn