Oracle Scratchpad

January 26, 2017

Basicfile LOBs

Filed under: Infrastructure,LOBs,Oracle — Jonathan Lewis @ 12:03 pm GMT Jan 26,2017

I wrote a short series a little while ago about some of the nasty things that can happen (and can’t really be avoided) with Basicfile LOBs and recently realised that it needed a directory entry so that I didn’t have to supply 6 URLs if I wanted to point someone to it; so here’s the catalogue:

At some stage I may also write a similar series about Securefile LOBs – because you do hit problems if you have a system that does a lot of work modifying a LOB segment whether it’s Basicfile or Securefile, and you need a strategy for damage limitation.


At the time of creating this catalogue I’ve had an SR open with Oracle for about 4 months on the problem that triggered this series, basically asking if there was a way to limit the number of chunks that could be taken off the reusable part of the index. So far I haven’t had an answer to that question; however the client was able to switch the table into a partitioned table and now drops old partitions rather than deleting old data.

The problem is now recorded against version as bug 22973766 so I’ve closed the SR and will keep an occasional eye on bug number:


Update on bug:  (see comments below): it turned out that the bug had nothing to do with the problem raised in the original SR; but I didn’t look too closely when I got the message, and I no longer remember the SR number.

Update (Jan 2021)

Logging on to MOS a little while ago I found that the original SR for this problem had been flagged up as recently changed. The SR summary reported “14 days ago” and the most recent entry showed 8+ hours ago. I’ll guess that I hadn’t received the usual email message about this because the SR had my old email address attached.

The gist of the update was that the bug had been fixed and the relevant bug number was 27108036. It’s not a public bug, but there is a descriptive note:  Doc ID 27108036.8 Excessive reads of LOB index blocks during Basicfile LOB block space reclaim which says the bug is fixed in 19.1 with the introduction of a new parameter: _reclaim_lob_index_scan_limit which seems to default to zero (probably meaning no limit) with a suggested value of 128.  Backports may be made available for older versions on request, but will make use of one of the “spare” parameters.





  1. Waiting for securefile LOB series. In one the applications that i work for we have LOB table which has a lot of DML(insert and delete’s) activity happening on it. The space used by securefile LOB are not getting freed after delete operation. Shrink did not yeild any results.

    And so eventually we ended up using DBMS_REDEFINITION to reclaim the space. Can you please throw some light on chunk size, block size, cache and nocache options when it comes to defining LOB columns.

    Comment by Rahul Malireddy — January 29, 2017 @ 2:16 pm GMT Jan 29,2017 | Reply

  2. Rahul,

    I made a couple of comments about securefile lobs in the comments section of part 6.
    I really need to work through a couple of large-scale production systems with problems before I can be confident about mechanisms and causes of problems but I did find problem with poor re-use of space.

    I am slightly surprised that a SHRINK didn’t help, but you didn’t specify the command or version of Oracle and if you search MoS you’ll find various bugs about space utilisation and a couple about bitmap blocks being inconsistent with actual space used.

    I think chunk size is ignored for securefile LOBs – the manuals state that one of the benefits of securefiles is an ability to use variable size chunks. I would be a little surprised if any of the other parameters you mention had anything to do with failure to reuse space – but maybe there’s some as yet undiscovered bug relating to space management for block sizes over 8KB.

    You could try posting a question to the OTN database forum to see if anyone else has had problems with space usage. If you do, remember to state the Oracle version, quote the DDL to create the tablespace and the table, (dbms_metadata.get_ddl() is useful for this, but set long 250000 first), and the exact SQL you used when trying the SHRINK command.

    For reference, if you have access to MoS, you should look at:

    Comment by Jonathan Lewis — January 30, 2017 @ 12:08 pm GMT Jan 30,2017 | Reply

  3. The bug 22973766 Is reported against SecureFiles, do you think that will cause Oracle investigate on your BASICFILE leak (especially since it is claimed to be fixed)

    Comment by eckes/Bernd — January 24, 2018 @ 6:44 am GMT Jan 24,2018 | Reply

    • eckes/Bernd,

      I can’t remember the details, but I think I closed the SR when I got the note giving me a bug number – I don’t think I checked the bug to see that it was a match so I hadn’t realised that it was about securefiles. So it’s probably completely irrelevant. On the other hand one of the postscripts on part 6 says ” Don’t even start to think that you can work around this by using securefiles”, so maybe the bugfix has addressed the problem I tested the code with securefiles.

      Comment by Jonathan Lewis — January 26, 2018 @ 9:50 am GMT Jan 26,2018 | Reply

  4. […] If I remember correctly, the database in the example above was Oracle 9i. Jonathan Lewis has a more recent and thorough description of Basic File LOB internals and issues: […]

    Pingback by The Secret Life of LOBs – NZ DBA — May 11, 2020 @ 3:13 am BST May 11,2020 | Reply

  5. […] Basicfile LOBs (Jan 2017): a series of 6 articles on basicfile LOB implementation and a performance threat due to high volume deletes and updates that may be fixed in 19c […]

    Pingback by LOB Catalogue | Oracle Scratchpad — May 17, 2022 @ 10:36 pm BST May 17,2022 | Reply

  6. […] code which can be triggered by the way the LOB is used – in which case it’s worth reading the series I wrote 5 years ago modelling a problem (mostly about time spent, in fact) a client had with […]

    Pingback by LOB space | Oracle Scratchpad — July 20, 2022 @ 5:07 pm BST Jul 20,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 )

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: