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