Had a situation last week where one group running an application to
'rebuild' their oracle database exported (under application control)
some or all tables so the tables could be deleted then reloaded
to 'condense' the data. The export filled the filesystem and failed.
My upper-level manager (VP) wants me to put something into place
that will calculate the size of the data within the database as
if that data had been exported, then compare that size to the freespace
in the live filesystem. I'm to send an alert if there is not enough
room for the export.

My question is, how do I calculate the size of the exported data
file given the amount of data in a table?

Mike

"Mike" <mikee (AT) mikee (DOT) ath.cx> wrote

1. The export/import thing is silly. It is rare that you really need to do
this type of thing. There are a lot of old myths that doing this will "make
the system go faster". If you factor in the down time and the rebuild time
etc. you are actually going slower.

2. There is a feature called vsize so you could do
select sum(vsize(column1))+(sum(vsize(column2))+... from table;
to get the actual size of the data. You can't do this with Long or long raw
data.

3. You could analyze all the tables and multiply the average row size by the
row count and add them up to get the size. Then add a little more room for
overhead. ( say 5K per table for the text to reproduct the table and
indexes)

4. You could do an export and see how large it is and also add up the
extents in the database and take a ratio for future estimates.(see docs,
dba_segments is the table I think.)
Jim