question

tomc avatar image
tomc asked

Create table limits

What is the largest row size that I can create in a table? How many columns or bytes can it contain?

tabletables
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

HillbillyToad avatar image HillbillyToad commented ·
Depends, what version of Oracle are you running?
0 Likes 0 ·
Kastaka avatar image Kastaka commented ·
Not sure about the original asker, but I'm interested mostly in 10 and 11, possibly as low as 9... all the info I've found seems to be trying to calculate max row size from the number of columns you can have in a table, which gets confusing with out of row storage...
0 Likes 0 ·

1 Answer

·
Andrew Mobbs avatar image
Andrew Mobbs answered

In general, this is answered by the "Database Limits" section of the Reference Manual (Appendix A in 10g and 11g, Section 4 in 8i and 9i).

It would help to understand the distinction between in line storage and out of line storage for LOBs. LOBs stored "in line" are stored alongside the row data, "out of line" are stored in a separate segment, and a pointer to that data is stored in the row.

The largest amount of data that could be possibly be stored for a single row would be 1000 columns of LOBs in a database 32k block size, at (2^32 - 1) * (2^15) ~= 128 TB per field, or 125 PB (petabytes) per row.

The largest with in-line storage would be 1000 columns of 4000 byte VARCHARs at just under 4MB per row. (Or if you prefer IEC units, that's exactly 4MB, and the previous result was 125PiB, but "pebibytes" just sounds odd.)

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.