Updating clob column

Posted by / 18-Jul-2017 10:14

Updating clob column

I’ve got a table with a single CLOB column holding a single row.

But the LOBINDEX is protected by undo in the standard fashion, so if another long-running query that started before our update needs to see the old version of the LOB it will create a read-consistent copy of the relevant – which means that from its perspective the index will automatically be pointing to the correct LOB chunk.This is where the (notionally invisible and you don’t need to know about it) LOBINDEX comes into play.Oracle maintains an index keyed by (LOB_ID, chunk_number) *** pointing to all the chunks of a LOB in order, so when you update a single chunk Oracle simply creates an updated copy of the chunk and changes the appropriate index entry to point to the new chunk.So here’s the clever bit – how big will the LOBSEGMENT grow when I update that one CLOB ?It’s common knowledge (to users of LOBs) that the undo mechanism Oracle has for LOBs is simply to leave the old LOB in place and create a new one – so the intial response to the question might be to guess that the LOBSEGMENT will grow to roughly double the size.

updating clob column-26updating clob column-38updating clob column-69

alter table t add y_copy varchar2(4000); update t set y_copy = y; update t set y = null; commit; alter table t modify y long; alter table t modify y clob; update t set y = y_copy; alter table t drop column y_copy; You need to have a commit after "update t set y = y_copy;" Otherwise you loose all the data, because the next statement drops the column and you will never able to recover.

One thought on “updating clob column”

  1. If they tell you a slightly embarrassing story, guess what you should do? If someone writes to you “Hey, I like that picture of you in Tulum! People are just testing the water with their first emails. There is no correlation between bad spelling or grammar and intelligence. Keep your homonyms straight (your, you’re) and your possessives minimal (it’s, its).

  2. features - The most advanced and powerful Movies/TV Series/Actors manager; - Catalog (movie database software) for alll your CD/DVD/Blu-ray/VHS movies; - Automatic HDD scan for all video files; - Access to many detailed online databases with movies descriptions and ratings; - Download tr Movie Dock 1.1All Your Favorite Movies Just A Click Away!