calculate maximum size in bytes of a table row

[steven acer]

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

[news.verizon.net]

"steven acer" <dude...@gmail.com> wrote in message
news:5700e377-6091-4fac...@l64g2000hse.googlegroups.com...

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

[DA Morgan]

CREATE TABLE t (
ccol VARCHAR2(10),
ncol NUMBER,
dcol DATE);

INSERT INTO t (ccol, ncol, dcol) VALUES ('ABCDEABCDE', 99.99, SYSDATE);

SELECT vsize(ccol), vsize(ncol), vsize(vcol)
FROM t;
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damo...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

[joel garry]

I just tried select max(length(field1)+length(field2)...) from
table_name; (from a real table with real fields, of course) and it was
syntactically correct and the number looked ok, but I don't have time
to actually verify it...

This would be a useful query.

jg
--
@home.com is bogus.
"due to an unfortunate and very rare combination of hardware problems
and backup configuration settings..." http://catless.ncl.ac.uk/Risks/25.11.html#subj5

[joel garry]

On Apr 18, 7:18 am, "news.verizon.net" <kenned...@verizon.net> wrote:
> "steven acer" <dudest...@gmail.com> wrote in message

Also, see the VSIZE function.

jg
--
@home.com is bogus.

I hope these posts are working, I don't see them yet...