CLOB size matters!


Many developer and DBA has an idea about the storing clobs. Basic rule is “if length of clob is higher tan 4000 then it will be stored in lob segment not in table segment” which is correct but sometimes misinterpreted!

if you have a clob column in your table, your data might be in the table. since LOBs(large objects) can be too big Oracle stores them in a separate segment. this is very important because while you are selecting your table, you might be reading not just your table also lob segment which will decrease your performance of course. as tuning step Oracle stores your clob data in the table segment if length of lob is less than 4000. This means Oracle act to your data as varchar2 which will be faster but as I said this is causing a misunderstanding.

So you check your clob data and all of them has 4000 bytes length unfortunately all of those data are in the lob segment not in the table! why? because of the large object overhead! every large object has some header information and this is the part of that magical number “4000”. Because of that your data must be less than 4000 and the new magical number is 3964. your data must be equal or less than 3964 to be stored in the table segment.

here is the proof:

I just create a table with a clob column and inserted 10.000 rows with 4000 length of clob data. when we check the user_segments for our table we will simply see that it has 576KB which is really small. After that checking LOB segment size, we are able to see the truth! our lob segment size is 98496KB. so all of our data has been stored in lob segment!

Let’s run the same code for 3964 byte characters:

Here it is. with 3964 bytes clob data, our table size is 81920KB and lob segment size is 128KB. Simply all the data is stored in the table not in the lob segment.

Is there a misdirection on documentation about this 4000 size? NO! Oracle says, Large objects will be stored in the table segments if their size is less than 4000 but this size is not the size of our data. this size includes the LOB overhead.

have a nice day.

ORA-00942: table or view does not exist and sequences as default value


after 12c or above we are able to set sequence’s nextval as the default value of an ID column. One of my customer started to complain about the ORA 00942 (table or view does not exist) error even if they have all necessary privileges on the table. After a quick investigation I realized that real error is not related with the table but the sequence.

this is the sample code, I just assign seq_tmp as default value of tmp table. Now I create a user to test:

as you can see I give all privileges on tmp table to test_user. now lets try to do some inserts with test_user:

so far so good. I provide all necessary value of the table and used my value as ID not the sequence. so let’s continue:

viola! I use null as the value of ID which means sequence have to generate new id data but our insert statement got the ORA 00942. When I see this for the first time, I thought user doesn’t have necessary privileges on tmp table but now I’ve already known that user has privileges. Actually there is a small hint at this error! if you check, you can see that Oracle marks the error point with a star and it is under the “into” keyword, “o” if we want to be precise but if you really don’t have insert privilege on the table it would be marking table name:

as you can see star is under the first letter of the table name. anyway, if we continue to try inserting:

all the combinations of insert statements will be fail when it needs to use the sequence. so let’s use sequence directly:

error again and now we can see that our little star is under the sequence name! So real missing privilege is select on the sequence. after running: grant select on seq_tmp to test_user; all is fine and everything is working.

Don’t forget the sequences 🙂