Wednesday, May 29, 2013

Table Information - including: rows counts, column counts and size

The script below can be run to get a Row Count (from Stats), Column Count and size (in MEG) for all Tables in a Schema:

select 
   a.table_name, 
   a.num_rows ROW_count_FROM_STATS,
   b.col_count,
   C.MEG
from 
   dba_tables a, (select TABLE_NAME, count(column_name) as col_count from dba_tab_cols where owner='&owner' GROUP BY TABLE_NAME
ORDER BY TABLE_NAME) b,
(SELECT
   owner, table_name, TRUNC(sum(bytes)/1024/1024) Meg
FROM
(SELECT segment_name table_name, owner, bytes
 FROM dba_segments
 WHERE segment_type = 'TABLE' and owner = '&owner'
 UNION ALL
 SELECT i.table_name, i.owner, s.bytes
 FROM dba_indexes i, dba_segments s
 WHERE s.segment_name = i.index_name
 AND   s.owner = i.owner
 AND   s.segment_type = 'INDEX'
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.segment_name
 AND   s.owner = l.owner
 AND   s.segment_type = 'LOBSEGMENT'
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.index_name
 AND   s.owner = l.owner
 AND   s.segment_type = 'LOBINDEX')
WHERE owner in UPPER('&owner')
GROUP BY table_name, owner
HAVING SUM(bytes)/1024/1024 > 5  /* Ignore really small tables */
ORDER BY SUM(bytes) desc) C
where 
   a.owner = '&owner'
   and A.TABLE_NAME = b.table_name(+)
   AND A.TABLE_NAME = C.TABLE_NAME(+)
order by 
   a.table_name;