oracle a table of,Size of a Table in Oracle

Hi Vijay,

The two values will always be different: one gives you the physical size of the table in the Oracle DB, the other is a (rough) indicator of the size of the actual data.

The value in DB02(OLD) is the total amount of physical space (i.e. the size of the segment) the table occupies in the database. Although there is usually a relationship between the amount of data in the table and its segment size, this is not always true. For example, if a table once contained millions of rows but most of these have been deleted, then the segment is likely to be much larger than the amount of data still in the table; this is because by default Oracle will not shrink an oversized segment.

Measuring the space taken by actual table rows is more difficult. A reasonable approach is to take the number of rows and multiply it by the average row length. Both values are part of the Oracle database statistics. Again, a few warnings are in place however:

1) For large tables statistics are estimated rather than computed exactly, which means the value are not necessarily accurate

2) The SAP BR tools are optimized to only recalculate statistics for a table when its row count has changed by a sufficient amount (default 50%). For the Oracle optimizer, which is the prime consumer of statistical data, this is good enough, but for space estimates this would result in very inaccurate figures.

3) For tables containing RAW / BLOB fields (including SAP pools, SAP clusters and some other tables), the row length in the statistics does not include these fields, again leading to wrong results.

Function DB_GET_TABLE_SIZE uses the (row count * average length) formula. Looking at the source code it does not take the row length from the database but uses the structure length from the dictionary. I think this will overestimate the actual space in most cases, but it is probably better than the shake average row length from the DB stats.

Hope this helps,

Mark