Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> AW: Maximum row length in bytes (

AW: Maximum row length in bytes (

From: Stefan Jahnke <>
Date: Mon, 26 Apr 2004 16:43:58 +0200
Message-ID: <>

Hi Tanel

Thank you for your detailed answer. I thought there was an explicit = limit for the row length in bytes in Oracle. But it looks like the only = limit is roughly the size for the biggest (in bytes) data type = multiplied by the maximum number of columns per table plus some = overhead. I wasn't able to find additional information on that topic.


Stefan Jahnke
BOV Aktiengesellschaft
Tel.: +49 201/45 13-298
Fax: +49 201/45 13-144
Please remove nospam to contact me via email.
Abonnieren Sie unseren Newsletter:

Optimieren Sie durch Portale Ihr taegliches Geschaeft: Wie Anwendungen = in einem dynamischen Prozesskontext Ihre Informationsprozesse deutlich = beschleunigen koennen, erfahren Sie von

Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht = unter fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde = bitten wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz = die rechtliche Verbindlichkeit der vorstehenden Erklaerungen und = Aeusserungen ausschliessen.

As you are probably aware, e-mails sent via the Internet can easily be = copied or manipulated by third parties. For this reason we would ask for = your understanding that, for your own protection and ours, we must = decline all legal responsibility for the validity of the statements and = comments given above.

-----Urspr=A8=B9ngliche Nachricht-----
Von: Tanel P?der [] Gesendet: Friday, April 23, 2004 12:16 AM An:
Betreff: Re: Maximum row length in bytes (

> hi,Stefan Jahnke
> do not use lobs , one table may have 1000 columns , varchar2=3D
> ,4000*1000 =3D3D 4000000 bytes ?
> >Maybe I'm blind, but I couldn't find any information on the size=3D
> limit =3D3D
> >of a single row. Does anybody know to what maximum number of=3D
> bytes a row =3D3D
> >can grow in length? Does the usage of "inline" (B|C)LOBs effect=3D
> that =3D3D
> >limit somehow?


You can have 1000 columns in a table starting from 8.0. You can have your last column as LONG.
This means 999 columns storing varchar2(4000) for example + one LONG col storing 2GB.
Every char column having size larger than 250 bytes will require 3 bytes = for
specifying column length.

Since there is only one byte for column count in internal row structure, = row
chaining (even into the same block, if space permits) is used to get additional column count bytes for the row - so the row is practically = split
into 3 pieces.

Every row piece is internally like a different row, so every one has a 3-byte header (row flag, lock byte and column count), but also in case = of
chained row, every piece except the last one have additional 6 bytes for storing the next piece's address (4 bytes for DBA, 2 bytes for row# = inside
the block).

Also, since this large row definitely wouldn't fit into a single block, normal chaining because of block space lack is needed anyway, so a 4MB = row
would be split into hundreds of pieces anyway, so many additional = chaining
bytes are needed in beginning of row pieces.

I don't know that much of LONG internals, there might be some issues as well, but nevertheless, this formula of calculating the max row size is = very
complex and depends on many things (even more than I've mentioned here).

If you have a "enable storage in row" type LOB larger than 3964 bytes, = it
won't be stored in the row anymore, it goes automatically to LOB segment then.

Why exactly 3964? - 3964 bytes LOB data + 20 bytes lob locator + 16 = bytes
lob inode =3D 4000 bytes and 4000 bytes seems to be some kind of = internal limi
tation in Oracle data interface.


Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Please see the official ORACLE-L FAQ:
To unsubscribe send email to:
put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Mon Apr 26 2004 - 09:41:56 CDT

Original text of this message