Oracle 12c – Tablespace Max Size vs Bytes Used by Segments

oracleoracle-12ctablespaces

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.

Best Answer

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);
Related Question