Re: calculate maximum size in bytes of a table row

From: joel garry <>
Date: Fri, 18 Apr 2008 10:02:07 -0700 (PDT)
Message-ID: <>

On Apr 18, 7:18 am, "" <> wrote:
> "steven acer" <> wrote in message
> > how can i calculate the maximum amount of bytes that can be occupied
> > by a table row.
> > The table might not have any data in it, i'm looking for a calculation
> > based on the data types used for columns.All i found so far was
> > algorithms to estimate the average size for existing rows.
> > i'm trying to spool the contents of some of my tables to flat files,
> > and for that i'm trying to figure out if the rows would not be
> > truncated by sqlplus due to its bytes/line limitation by calculating
> > the maximum size a row can reach in bytes.
> > database version is 10g R2 running on RHEL 4
> A data column is 7 bytes
> A char or varchar column is the size of max size of the column
> a number column is up to 22 bytes
> a timestamp column is 12 (I think)
> a lob is up to 4 gig (might be higher for 10G)
> You could do a sum on a decode of dba_columns grouped by table
> like
> select table_name, sum(decode(column_type,'NUMBER',
> 22,'DATE',12,'VARCHAR2',column_length,....) from dba_columns
> group by table_name;
> You will have to check the column names and what type is returned.
> Jim

Also, see the VSIZE function.


-- is bogus.
I hope these posts are working, I don't see them yet...
Received on Fri Apr 18 2008 - 12:02:07 CDT

Original text of this message