Re: calculate maximum size in bytes of a table row

On Apr 18, 7:18 am, "news.verizon.net" <kenned..._at_verizon.net> wrote:
> "steven acer" <dudest..._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

Also, see the VSIZE function.

jg

--
@home.com is bogus.
I hope these posts are working, I don't see them yet...