Oracle row length of table

SELECT a.table_name, a.num_rows, c.total_bytes sizeof_table,
CASE a.num_rows WHEN 0 THEN 0 ELSE c.total_bytes / a.num_rows END arl_wo, a.arl_woo, b.index_len,
a.arl_woo + b.index_len total_arl_woo, (a.arl_woo + b.index_len) * 1000000 for_million_records
FROM (SELECT table_name, avg_row_len arl_woo, num_rows
FROM user_tables) a,
(SELECT table_name, SUM (column_length) index_len
FROM user_ind_columns
GROUP BY table_name) b,
(SELECT SUM (BYTES) total_bytes, segment_name AS table_name
FROM user_extents
WHERE segment_type = ‘TABLE’
GROUP BY segment_name) c
WHERE a.table_name = b.table_name AND a.table_name = c.table_name;

Note: All values are in bytes.
NUM_ROW : Number or rows in table
SIZEOF_TABLE: Size of table
ARL_WO: Average row length with overhead
ARL_WOO: Average row length without overhead
INDEX_LEN: Bytes occupied by the index on table for each row
TOTAL_ARL_WOO: Total average row length with index included

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: