Oracle Scratchpad

May 10, 2017

Quantum Space

Filed under: humour,Oracle,Troubleshooting — Jonathan Lewis @ 1:31 pm BST May 10,2017

Here’s a not very serious note that makes a serious point.  I’ve got a small tablespace made up of 4 files, and here’s a little report I can run against the data dictionary for that tablespace:

select 'File space' What, nvl(sum(user_bytes)/1048576,0) MB from dba_data_files where tablespace_name = 'LOB_TEST'
union all
select 'Free space',      nvl(sum(bytes/1048576),0)         from dba_free_space where tablespace_name = 'LOB_TEST'
union all
select 'Extents',         nvl(sum(bytes/1048576),0)         from dba_extents    where tablespace_name = 'LOB_TEST'
union all
select 'Segments',        nvl(sum(bytes/1048576),0)         from dba_segments   where tablespace_name = 'LOB_TEST'

The name of the tablespace isn’t significant – it happens to be a tablespace I created to do some tests relating to space allocation with securefile LOBs, and it’s been hanging around ever since.

The query first reports the “user” space defined by the files – remember that there will be some space for header information and other metadata, and there may be some space near the end of the file which is smaller than the minimum extent allowed for that tablespace.

After the total user space the query reports :

  • the free space
  • the space currently allocated to extents
  • the space currently allocated to segments

Here’s the result I get currently:

WHAT               MB
---------- ----------
File space        196
Free space        196
Extents             0
Segments          196

The total user space is 196MB, of which 196MB are free and there’s no space allocated to extents — except 196MB has been allocated to segments.

Depending how you look at it the tablespace is either full, or empty.

If you’re in the know the puzzle resolves itself when you query dba_segments for the segment names, which look like: “BIN$TrDKUCvcVQbgUwEAAH9K2Q==$0” – I have a dropped segment in the recycle bin, so that I can recover it from the bin if I need it (with a call to “flashback table XXX to before drop”), but Oracle is allowed to reuse the space if it needs to. Like Schrodinger’s cat the object is both alive and dead until someone decides to peek.

After a call to “purge recyclebin” the result changes to:

WHAT               MB
---------- ----------
File space        196
Free space        196
Extents             0
Segments            0

On a production system you might need to issue “purge dba_recyclebin” (if you have the appropriate privilege) to resolve the apparent contradiction as the call to “purge recyclebin” applies only to objects in your own schema.

This note was prompted by a question on the OTN database forum about contradiction between a traditional SQL statement to report free and used space and a screen dump from Enterprise Manager.  I don’t think the thread reached a firm conclusion – but apart from the potential for the recyclebin to confuse the issue, there are extra possibilities thanks to auto-extensible data files, and the Enterprise Manager’s scope for querying a complete different set of views such as dba_tablespace_usage_metrics and v$filespace_usage. In fact the EM code clearly had at least one error in it (which makes any of its results suspect) because it managed to report the critical tablespace as 390% used!




  1. […] then there will be some space that is reported as free but is not quite free since the extents will still be allocated. This brings me to the topic for today’s […]

    Pingback by Free Space | Oracle Scratchpad — August 8, 2019 @ 1:29 pm BST Aug 8,2019 | Reply

  2. […] (The original table, though it’s dropped after the name switching, is not purged from the recyclebin.) The impact on undo and redo generation is noticeable but not hugely significant. Because the […]

    Pingback by 12c MView refresh | Oracle Scratchpad — February 15, 2022 @ 12:14 pm GMT Feb 15,2022 | Reply

  3. […] Quantum Space (May 2017): why some space can be both free and not free. […]

    Pingback by Fragmentation catalogue | Oracle Scratchpad — June 4, 2022 @ 11:16 am BST Jun 4,2022 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: