|
Home > Archive > Oracle Databases > October 2005 > must have been asked before...
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
must have been asked before...
|
|
|
| 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
| |
| Jim Kennedy 2005-10-27, 8:37 am |
|
"Mike" <mikee@mikee.ath.cx> wrote in message
news:TwE2f.6499$Zf.3988@fe05.lga...
> 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
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
|
|
|
|
|