SQL query to show tables vs indexes in your tablespaces

SQL query below displays a tables / indexes breakdown of your tablespaces.

SELECT tablespaces.tablespace_name, 
			NVL( tables, 0) as tables, 
			NVL( indexes, 0) as indexes
FROM 	dba_tablespaces tablespaces
	LEFT OUTER JOIN ( 
				SELECT COUNT(*) tables, tablespace_name 
				FROM dba_segments 
				WHERE segment_type = 'TABLE' 
				GROUP BY tablespace_name ) tables
		ON tablespaces.tablespace_name = tables.tablespace_name
	LEFT OUTER JOIN ( 
				SELECT COUNT(*) indexes, tablespace_name 
				FROM dba_segments 
				WHERE segment_type = 'INDEX' 
				GROUP BY tablespace_name ) indexes
		ON tablespaces.tablespace_name = indexes.tablespace_name
/

Sample output;

SQL> /
TABLESPACE_NAME                    TABLES    INDEXES
------------------------------ ---------- ----------
***************                         5          3
*******                                11         28
************                           56        118
************                           64        120
SYSAUX                                536        759
SYSTEM                                602        754
USERS                                  15         25
TOOLS                                   0          0
UNDO                                    0          0
************                            0          0
TEMP                                    0          0
************                            0          0

12 rows selected.


Leave a Reply

Helpful? - leave your note below so I can brag

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>