LOB Catalogue

This is a list of all articles I’ve written that talk about LOB handling. Each entry has a date stamp and a short note of the contents. The articles will generally be listed most-recent first.

I am currently working backwards through my entire blog adding articles to catalogues as I go, so any individual catalogue may grow very slowly.

• Char Semantic trouble (August 2022): A combination of varchar2(x CHAR), LOB columns, and transparent data encryption (TDE) trigger an error ORA-12899 on “alter table move”
• LOB Space (July 2022): considerations when comparing dbms_space.space_usage() with sum(dbms_lob.getlength())
• Dropping tablespaces (July 2022): composite partitioning, LOBs, and reusing the names of dropped tablespaces can result in subsequente surprising errors.
• Control File Waits (Jan 2022): Investigating where time can be lost on I/O with basicfile Lobs.
• GTT LOBs (Aug 2021): a delayed report of an error in the 12.1.0.2 data dictionary about lobindex placement, and a reminder that you can associate global temporary tables with a specific temporary tablespace.
• In-row LOB lengths (Nov 2020): Oracle is not good at reporting the column lengths when the column is a LOB (and not consistent across versions), so you need to check the details carefully.
• Costs of reading LOBs (Aug 2019): it’s important to check exactly what a client program does to fetch LOBs from the database – it may be doing far more network round-trips than you expect.
• How big are your LOBs (May 2019): a few key points to avoid surprises about the space that disappears when you start using LOBs.
• Error logging LOBs (Aug 2018): If you set max_string_size to extended then you need to think about how you’re going to handle any “error logging” tables.
• Optimizer LOB costs (May 2018): an example of the optimizer including unexpected and unnecessary arithmetic adjustments for predicates involving LOBs.
• 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
  • Part 1 – Statement of problem
  • Part 2 – Introduction to LOB Indexes and Freepools
  • Part 3 – Design strategies and testing
  • Part 4 – First signs of contention problems
  • Part 5 – Losing space in the LOB data segment
  • Part 6 – Losing time due to asymmetrical insert/delete patterns
• Quiz Night 29 (Feb 2016): a side-effect of setting max_string_size to extended
• Basicfile LOB space (Jan 2015): dbms_space counts chunks, not blocks, for basicfile LOBs.