Re: datatype sizes

James B. Reynolds wrote:
>
> In <321E8AC5.6CE1_at_teldta.com> "Brian P. Mac Lean"
> <brian.maclean_at_teldta.com> writes:
> >
> >gshell wrote:
> >>
> >> Can anyone point me to a manual reference that gives the byte
> >> requirements for each of the Oracle datatypes. I'm trying to
 estimate
> >> the disk space requirements for a database. I've found the "higher
> >> level" formulas that calculate the files size based on a row of a
> >> given size. But they show NADA on how to calculate the row size.
> >> Specifically, I need the size for a DATE and a NUMBER. I assume,
> >> given that a NUMBER datatype can handle up to 32 significant digits,
> >> it must be at least 16 bytes. (If I did my math corrrectly. <big ole
> >> grin>)

Mr. Jim Reynolds and Mr. Brian Mac Lean's responds are very accurate and right on subjects. This is additional information you may interesting. The date datatype carries fixed 7 byte Oracle internal structure and number datatype is variable and you may estimate its size by roundup(precision divided by 2), then plus 1 byte. If the number has negative nubmer add another byte. This is only for data not including any row or block overhead.

On Oracle SAG manuals for Oracle Version 7.1, 7.2, and 7.3, the Block overhead size are 2 or 4 bytes different - actually less - from older versions. The calulation of examples are not accurate.

Joseph Y. Suh
Oracle DBA
DB Technology, Inc.
Atlanta, GA
770-352-3165
dbtech_at_inetnow.net.usa

> >> Thanks,
> >>
> >> Gary Shell
> >
> >I don't have the manual in front of me here so I'm wing'en this one.
> >Create a sample table and a few rows. Do an analyze on the table.
> >The dba_tables view should now have some info of the average size of
> >a row in your table. The sql ref also documents two functions, "dump"
> >and "vsize". I'm sure one of these will give you the real size of
> >any column.
> >
> > \\|//
> > (0-0)
> > +-----oOO----(_)-----------+
> > | Brian P. Mac Lean |
> > | Database Analyst |
> > | brian.maclean_at_teldta.com |
> > | http://www.teldta.com |
> > +-------------------oOO----+
> > |__|__|
> > || ||
> > ooO Ooo
>
> For Oracle7, see Chapter 8, Managing Schema Objects, of the Server
> Administrator's Guide (SAG) for calculating the space required for
> tables, indexes, etc. based on column datatype and estimated average
> length.
>
> Hope this helps.
>
> Jim Reynolds
> jbreynol_at_ix.netcom.com
Received on Sun Aug 25 1996 - 00:00:00 CEST