Thursday, January 26, 2012

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);

2 comments:

Unknown said...

Hello bob,
Is this script is running bec when I execute the script in oracle 11g, there is no output and no error. how could I see the result.
My requirement is to list all the tables with row count, table size, tablespace name in all schema. could you help me or provide the script to fulfill my requirement. your help will be appreciated.

Amit

Unknown said...

Hello bob,
Is this script is running bec when I execute the script in oracle 11g, there is no output and no error. how could I see the result.
My requirement is to list all the tables with row count, table size, tablespace name in all schema. could you help me or provide the script to fulfill my requirement. your help will be appreciated.

Amit