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

Your email address will not be published. Required fields are marked *