Find Oracle Table Size, Number Of Columns and Rows

Query to Find Table Size, Number of Rows and Columns in a Oracle Database

 Here is a query that gives the table size which include number of columns, number of rows and table data size in MB. I found these information are helpful during the data warehouse design phase.

 select col.table_name,

col.col_cnt as column_count,
rc.row_cnt as row_count,
s.size_in_MB as table_size_in_MB
from
(
/* number of columns */
SELECT upper(table_name), COUNT(*) col_cnt,
FROM dba_tab_columns
WHERE owner = 'V500'
group by upper(table_name)
) col

join

(
/* number of rows */
select
table_name,
to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c from '||table_name)),'/ROWSET/ROW/C')) as row_cnt
from dba_tables
where (iot_type != 'IOT_OVERFLOW'or iot_type is null)
and owner = 'SCOTT'
) rc
on upper(col.table_name) = upper(rc.table_name)

join

(
/* table size in MB */
SELECT
owner, table_name, (sum(bytes)/1024/1024) size_in_MB
FROM
(SELECT segment_name table_name, owner, bytes
FROM dba_segments
WHERE segment_type = 'TABLE'
and owner = 'SCOTT'
)

group by owner, table_name

) s
on upper(col.table_name) = upper(s.table_name);

Related Notes:

• Query To Retrieve Oracle Table Column Name And Data Type
• Find Current Running SQL Statements In Oracle