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 or not it’s Basicfile or Securefile, and you need a strategy for damage limitation.

Footnote

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.

 

2 Comments »

  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), and the exact SQL you used when trying the SHRINK command.

    For reference, if you have access to MoS, you should look at:
    Bug 21576517 : SECUREFILES LOB NOT RELEASING SPACE ON DELETE

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


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Powered by WordPress.com.