How to Estimate maximum size of new column in a table and check actual size of existing column in a table in ORACLE

I have described here how to estimate the maximum size of a new column and check the actual size of exiting column. You might know it already then ignore it otherwise might be interesting to know.

How to estimate data in new column when adding new table:

Table 1: Message_TRANSACTION_LOG

select C.COLUMN_NAME,C.DATA_LENGTH from all_tab_columns c

where C.TABLE_NAME='MESSAGE_TRANSACTION_LOG'

order by C.COLUMN_NAME;

Table 2: Column size in bytes in table MESSAGE_TRANSACTION_LOG

Now let’s consider two columns CONSUMEROPERATORID and USERDATA here. From table 1 we can see the type of CONSUMEROPERATORID is number and USERDATA is varchar2(1024 char) and from table 2 we can see the maximum byte taken by these columns are 22 bytes and 4000 bytes. So if we consider the maximum size of these columns it should be:

Table 3: Estimated maximum size of the columns

Now anybody may wonder why the byte we consider 22 for CONSUMEROPERATORID and 4000 for USERDATA here where some number take 2 byte and single character take 1 byte. These sizes depend based on the character set and most of the database use character set %UTF8 now as we are using. In UTF8 character set every character can take up to 4 bytes (e.g if the character in some other language instead of English). Now you ask then for USERDATA it should be 4096(1024*4) but here it’s showing 4000 since the limit of varchar2 is 4000 bytes. Number can take 2 to 22 bytes. E.g

How to check the actual size of column in a table:

There is data dictionary view to check total size of a table, database, datafile, index, lob segment etc but there is no dictionary view to check the size of column as far I know L . It’s easy to get information from dictionary view since these views give the information from statistics and it takes less time.

But there is way to get the actual size of column by using a function vsize. vsize return the number of bytes.

Lest take a small table for example, here I am checking the estimated maximum size and actual size of column TARIFFCLASSGROUPNAMEID in CDR_ACCOUNT table:

select * from CDR_ACCOUNT;

select count(*) from CDR_ACCOUNT;

Estimated size in bytes:

select C.COLUMN_NAME,C.DATA_LENGTH "Maximum size in Bytes"

from all_tab_columns c

where C.TABLE_NAME='CDR_ACCOUNT'

order by C.COLUMN_NAME;

So estimated maximum size for column TARIFFCLASSGROUPNAMEID should be 256*13=3328 (Here considering value in every rows)

Actual size in bytes:

select sum(vsize(TARIFFCLASSGROUPNAMEID)) from CDR_ACCOUNT;

So the estimated maximum size of TARIFFCLASSGROUPNAMEID is 3328 where actual size is 48 here.

Note: Since vsize and sum are functions and not getting the information from statistics so to get the size of column in big table will take time and this thing should not be execute in big table in production database without notifying DBA. But by giving date/timestamp in where condition in big table you can check the actual size of a column for a day or a week then you can guess for a month or year. But then also need to notify DBA.