Really not possible using a spreadsheet or any sort of formula. Oh, there are formula's that try but due to the fact that:

o 999999999 takes three times the storage 10000000000000000000000
o indexes can be key compressed
o free space might not be used within a transaction but across transactions it
would be
o an IOT stores things very differently then a HEAP table.
o (any one of a thousand things)


The only way I know to accurately size a table and related structures is to load it on your system with data that is REPRESENTATIVE of the actual data you will be using. You don't have to load 100% of the data, anywhere from a fraction of 1% to 10% of the data (depending on the table size itself -- the bigger they are the less you need to sample percentage wise). Then you analyze the table and multiply. That'll give you your initial sizing.

As for pctfree and pctused -- they are a soft function of HOW you use the data -- there is no spreadsheet possible for them at all, not even a function. You set pctfree and pctused based on how the data will be manipulated. Some very very generalized rules of thumb might be:

o if you load a table and never update a row and never delete any rows the pctfree = 0 pctused = 1. pctfree = 0 because ROWS NEVER GROW, hence you want to pack the block all the way up. do not save any space for update. pctused = 1 because you really don't care what it is in this case. You never delete only add. When the block gets full it will be taken off of the free list (pctfree hit) and will never go back on (since you never delete)


o if you load a table with lots of rows at a point in time and expect that on average the rows will DOUBLE in size and you never delete you would set pctfree at say 55%. Now 55% of the block will be reserved for those updates. pctused is not relevant since once a block is half full, we want to reserve the rest of the space for updates so set pctused to 1 (block must be 99% empty to be a candidate for insertion)

o if you have no idea how the rows are going to grow and shrink over time, what effects deletes will have and so on, you would just leave then default and hope for the best.

and so on. Basically, you must take an understand of what pctfree and pctused do and see how they apply to YOUR data and the way YOU will be using your data. There are no forumlas for these.


Followup to the comment below regarding "...sometimes cause delays in projects like ...".

Let me rephrase it this way -- if you cannot come up with a small set of data that represents some percentage of the entire data set (representative set of numeric data for a field, varchars that represent how the actual data will be used and so on) at this point in time -- it does not MATTER what sizing you use, they will all be meaningless.

If you are going to a customer to tell them to buy such and such an amount of hardware and you do not know what the data is going to look like, the only thing you can do is give them the WRONG answer.

If you understand the data (a prerequisite for sizing), all you need is an Oracle instance somewhere with the appropriate block size you plan on using. Load it up with sample data and size it. You do not have to do it on their hardware, you just need to do it somewhere - and if you cannot do it, you could not size this with a formula either. That is because the only reason you could not do this is due to lack of knowledge of what the data looks like.


followup to comment 4 below

that is exactly the problem -- each DBA has their OWN spreadsheet with different ways of calculating values based on THEIR installation and their PAST experience with THEIR applications.

There is no generic solution to this. If the DBA switched jobs to a different company with a different set of applications -- they might as well through out their spreadsheets (they will have to modify them greatly at the very least).