Thursday, July 21, 2022

Finding Size and Number of Rows in Each Table of a Schema

 Sometimes DBAs need to fulfill requirements form development teams or the customer and provide different kind of information regarding database. One of them is to find size of the tables in a schema and number of rows in the tables. This information my be required for future planning or capacity sizing, or this could also be used during investigating a performance issue. There could be different ways of fetching this information; one of them that I feel quite simple is being explained here.

Following is a simple query to fetch this information. Replace the value of OWNER with the name of schema that contains the tables for which you are fetching sizing information.

select a.segment_name TABLE_NAME,a.bytes/1024/1024 SIZE_MB,b.num_rows NUM_ROWS,b.last_analyzed from dba_segments a, dba_tables b where
a.owner='SALMAN '
and a.segment_type='TABLE'
and a.owner=b.owner
and a.segment_name=b.table_name
order by a.bytes;

Size of table would be the exact size of segment, but value of number of rows that is returned in this query may have a litter difference as compared the actual number of rows in the table. This is because num_rows column in DBA_TABLES view is updated when stats are gathered on tables.

Another way to find the number of rows in each table is to use query “select count(*) from owner.table_name”. To do this, you would need to use following query to generate a script.

SQL> select 'select count(*) from salman.'||table_name||';' from dba_tables where owner='SALMAN';

'SELECTCOUNT(*)FROMSALMAN.'||TABLE_NAME||';'
---------------------------------------------------------------------------------------------------------------------
select count(*) from salman. TABLE1;
select count(*) from salman. TABLE2;
select count(*) from salman. TABLE3;
select count(*) from salman. TABLE4;
select count(*) from salman. TABLE5;
select count(*) from salman. TABLE6;
select count(*) from salman. TABLE7;
select count(*) from salman. TABLE8;
select count(*) from salman. TABLE9;
select count(*) from salman. TABLE10;
select count(*) from salman. TABLE11;
select count(*) from salman. TABLE12;
elect count(*) from salman. TABLE13;
select count(*) from salman. TABLE14;
select count(*) from salman. TABLE15;
select count(*) from salman. TABLE16;
select count(*) from salman. TABLE17;
select count(*) from salman. TABLE18;
select count(*) from salman. TABLE19;
select count(*) from salman. TABLE20;
select count(*) from salman. TABLE21;
select count(*) from salman. TABLE22;
select count(*) from salman. TABLE23;

 Above generated output of query can be spooled into a file and then that file can be used as script to get the counting of rows.

Above script needs to be used very carefully because when we perform “SELECT COUNT(*)”, a full table scan of each table would be done to count the number of rows in that table. Best way is to use primary key column (count(primary_key) instead of coun(*)) in the query and result of query would also be returned very fast.

No comments:

Post a Comment

Popular Posts - All Times