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.