Why the length of JOBHISTORY field is 172 here

oracleoracle-10gtablevarchar

I have created one varray of varchar2 of length 30 and size 5.. so according to that the total size of the column should be 30 * 5=150 but it shows 172.

It should be 150. Is Varray allocating some extra space?

Best Answer

Varray stores the total length of the varray and length of each element, so yes, it needs some extra bytes.

I am not aware of any public documentation on this topic, and I do not know the complete internal representation, so below is a simple example.

create or replace type jobs_done as varray(5) of varchar2(30);
/
create table emp_job_his (empno number(6), jobhistory jobs_done);
insert into emp_job_his values (1, jobs_done(
  'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA', 
  'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBB', 
  'CCCCCCCCCCCCCCCCCCCCCCCCCCCCCC', 
  'DDDDDDDDDDDDDDDDDDDDDDDDDDDDDD', 
  'EEEEEEEEEEEEEEEEEEEEEEEEEEEEEE')
);
commit;
alter system checkpoint;

select
  dbms_rowid.rowid_relative_fno(rowid) as fno,
  dbms_rowid.rowid_block_number(rowid) as block_number
from emp_job_his;

       FNO BLOCK_NUMBER
---------- ------------
         4          134

alter system dump datafile 4 block 134;

The relevant part from the dump:

col  1: [162]
 88 01 a2 01 01 00 05 1e 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41
 41 41 41 41 41 41 41 41 41 41 41 41 41 1e 42 42 42 42 42 42 42 42 42 42 42
 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 1e 43 43 43 43 43
 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43 43
 1e 44 44 44 44 44 44 44 44 44 44 44 44 44 44 44 44 44 44 44 44 44 44 44 44
 44 44 44 44 44 44 1e 45 45 45 45 45 45 45 45 45 45 45 45 45 45 45 45 45 45
 45 45 45 45 45 45 45 45 45 45 45 45

The 3rd byte, a2, is the length of the column in bytes = 162 (col 1: [162]).

The 7th byte, 05, is the number of elements in the array = 5.

Starting after that, each element is stored as length + data. Length is 1e = 30, and data is 30 times the letter A = 0x41, and so on.

Related Question