CLOB size matters!

Published on: 2019-07-31 by Mustafa Comment

Hi,

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:

create table tmp_lob (mydata clob);

insert into tmp_lob 
  select lpad('1', 4000, '1') 
  from dual 
  connect by level <= 10000;
commit;

select bytes/1024 size_in_KB 
from   user_segments 
where  segment_name ='TMP_LOB';
--------------------------------------
--576

select bytes/1024 size_in_KB 
from   user_segments 
where  segment_name = (select l.segment_name from dba_lobs l where table_name = 'TMP_LOB');
-----------------------------------
--98496

create table tmp_lob (mydata clob);

insert into tmp_lob

select lpad('1', 4000, '1')

from dual

connect by level <= 10000;

commit;

select bytes/1024 size_in_KB

from user_segments

where segment_name ='TMP_LOB';

--------------------------------------

--576

select bytes/1024 size_in_KB

from user_segments

where segment_name = (select l.segment_name from dba_lobs l where table_name = 'TMP_LOB');

-----------------------------------

--98496

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:

truncate table tmp_lob;

insert into tmp_lob 
  select lpad('1', 3964, '1') 
  from dual 
  connect by level <= 10000;
commit;

select bytes/1024 size_in_KB 
from   user_segments 
where  segment_name ='TMP_LOB';
-------------------------------
--81920

select bytes/1024 size_in_KB 
from   user_segments 
where  segment_name = (select l.segment_name from dba_lobs l where table_name = 'TMP_LOB');
-------------------------------
--128

truncate table tmp_lob;

insert into tmp_lob

select lpad('1', 3964, '1')

from dual

connect by level <= 10000;

commit;

select bytes/1024 size_in_KB

from user_segments

where segment_name ='TMP_LOB';

-------------------------------

--81920

select bytes/1024 size_in_KB

from user_segments

where segment_name = (select l.segment_name from dba_lobs l where table_name = 'TMP_LOB');

-------------------------------

--128

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

Published on: 2019-07-232019-07-23 by Mustafa Comment

Hello,

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.

create sequence seq_tmp;

create table tmp (id number default on null seq_tmp.nextval, my_Data varchar2(10));

create sequence seq_tmp;

create table tmp (id number default on null seq_tmp.nextval, my_Data varchar2(10));

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

create user test_user identified by test_user;
grant connect to test_user;
grant all on tmp to test_user;

create user test_user identified by test_user;

grant connect to test_user;

grant all on tmp to test_user;

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

SQL> select * from mustafa.tmp;

no rows selected

SQL> insert into mustafa.tmp values (1, 'test row1');

1 row created.

SQL> select * from mustafa.tmp;

no rows selected

SQL> insert into mustafa.tmp values (1, 'test row1');

1 row created.

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:

SQL> insert into mustafa.tmp values (null, 'test row2');
insert into mustafa.tmp values (null, 'test row2')
          *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> insert into mustafa.tmp values (null, 'test row2');

insert into mustafa.tmp values (null, 'test row2')

ERROR at line 1:

ORA-00942: table or view does not exist

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:

SQL> insert into mustafa.bla_bla_table_which_is_not_exist values(1);
insert into mustafa.bla_bla_table_which_is_not_exist values(1)
                    *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> insert into mustafa.bla_bla_table_which_is_not_exist values(1);

insert into mustafa.bla_bla_table_which_is_not_exist values(1)

ERROR at line 1:

ORA-00942: table or view does not exist

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

SQL> insert into mustafa.tmp (my_data) values ( 'test row2');
insert into mustafa.tmp (my_data) values ( 'test row2')
          *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> insert into mustafa.tmp (my_data) values ( 'test row2');

insert into mustafa.tmp (my_data) values ( 'test row2')

ERROR at line 1:

ORA-00942: table or view does not exist

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

SQL> insert into mustafa.tmp values (mustafa.seq_tmp.nextval, 'test row2');
insert into mustafa.tmp values (mustafa.seq_tmp.nextval, 'test row2')
                                        *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> insert into mustafa.tmp values (mustafa.seq_tmp.nextval, 'test row2');

insert into mustafa.tmp values (mustafa.seq_tmp.nextval, 'test row2')

ERROR at line 1:

ORA-00942: table or view does not exist

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 🙂

Hello world!

Published on: 2018-06-26 by Mustafa Comment

Welcome to WordPress. This is your first post. Edit or delete it, then start writing!

No need to follow standards.

Uncategorized

CLOB size matters!

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

Hello world!