Viewing the size of a row/table in bytes 1

[mcowen]

Hello,

How do you do this? I need to analyse the size of the tables and project that over the next year. I will need to calculate the extent by which to increase when the table has filled up.

I have looked on the data dict for user_tables but anything to do with size seems to be empty.

Regards

 

[CRoberts]

In order for the cost based analyzer to work, as well as to update the dictionary views (Dba_Tables, User_Tables, etc.) you have to analyze the tables. There are a number of ways to do this, I typically use:

EXEC DBMS_UTILITY.ANALYZE_SCHEMA (UserName, 'COMPUTE');

This will put the statistics into the dictionary tables. This will also affect the way you queries are run. Up to this point, you have a rule base approach (RBO) when retrieving rows from a table. With the CBO now being used, you response times should (hopefully) improve. However, the init.ora parameter that controls this(Optimizer_Mode) has to be set to either CHOOSE or FIRST_ROWS, not RULE.

Caution: Do not "DO" the entire database. The data dictionary is supposed to always be RULE based. So, leave SYS and SYSTEM alone when analyzing various schemas.