I just checked the size of our tablespaces, because the admins said that they've made them bigger after we've had a full tablespace yesterday and I've discovered something really strange.

I hope someone can shed some light on this situation. Here's what I did:

1) I queried the size of a certain tablespace, let's call it 'XYZ' for this purpose. XYZ is the tablespace that was full yesterday and got an upgrade in size.

select (max_size / 1024 / 1024) as size_megabyte from user_tablespaces where tablespace_name = 'XYZ'

The result is 2048 MB.

2) I queried the segments using the tablespace 'XYZ' and summed up their used bytes

select sum(bytes)/1024/1024 as size_megabyte from user_segments where tablespace_name = 'XYZ'

The result is 36000 MB.

3) I queried the free space left on the tablespace 'XYZ'

select sum(bytes)/1024/1024 as size_megabyte from user_free_space where tablespace_name = 'XYZ'

The result is 4000 MB

At this point my jaw dropped, and I can't fathom what's going on here. Does anyone have a clue? Are my queries faulty?

Judging by the results of 2) and 3), I'd expect query 1) to return 40000 MB and not 2048 MB.


The way you have acquired the tablespace size is incorrect.

MAX_SIZE(NUMBER) returns Default maximum size of segments (in Oracle blocks).

You can query dba_data_files and dba_free_space to get the result

Following is the exceprt of monitoring script that we have been using.

SELECT * FROM (SELECT a.tablespace_name, SUM(a.tots)/1024/1024-SUM(a.sumb)/1024/1024 Tot_Used_MB, SUM(a.tots)/1024/1024 Tot_Size_MB,   
SUM(a.sumb)/1024/1024 Tot_Free_MB 
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);

First, as already stated, MAX_SIZE means the maximum segment size in blocks.

Second, *_FREE_SPACE views do not take into account the maximum size of autoextensible datafiles, so people using them usually end up asking why it returned incorrect results, or having false alarms.

You could just use the built-in view DBA_TABLESPACE_USAGE_METRICS. If you want the size in MB, you need to join DBA_TABLESPACES for the block size of the tablespace:

  m.used_space * t.block_size / 1024/ 1024 as used_mb,
  m.tablespace_size * t.block_size / 1024 /1024 as max_mb,
  dba_tablespace_usage_metrics m
  join dba_tablespaces t on m.tablespace_name = t.tablespace_name

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.