Re: calculate maximum size in bytes of a table row

From: news.verizon.net <kennedyii_at_verizon.net>
Date: Fri, 18 Apr 2008 14:18:59 GMT
Message-ID: <n52Oj.10$i45.7@trndny01>

"steven acer" <dudesterr_at_gmail.com> wrote in message news:5700e377-6091-4fac-8068-706d68f5fe6a_at_l64g2000hse.googlegroups.com...
> 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 Received on Fri Apr 18 2008 - 09:18:59 CDT

Original text of this message