Oracle – Quick Tip – Find Table & Index Size (SQL Server Equivalent Of sp_spaceused)

Often, a common requirement would involve determining the space usage by an Oracle table and/or index. Knowing that the information in stored in the system views, we can query for the size as follows (just substitute the table name):

SELECT owner, segment_name AS table_name, NULL as index_name, SUM(bytes)/1024/1024 size_mb
FROM dba_segments
WHERE segment_name = 'YOUR_TABLE_NAME'
    AND owner = 'YOUR_TABLE_OWNER'
GROUP BY owner, segment_name
----------
UNION ALL
----------
SELECT i.owner, i.table_name, i.index_name, SUM(bytes)/1024/1024 size_mb
FROM dba_indexes i, dba_segments s
WHERE s.owner = i.owner
    AND s.segment_name = i.index_name
    AND i.table_name = 'YOUR_TABLE_NAME'
    AND i.owner = 'YOUR_TABLE_OWNER'
GROUP BY i.owner, i.table_name, i.index_name
OWNER TABLE_NAME INDEX_NAME SIZE_MB
MY_TABLE_OWNER MY_TABLE_NAME 3929
MY_TABLE_OWNER MY_TABLE_NAME MY_TABLE_NAME_PK 436
MY_TABLE_OWNER MY_TABLE_NAME MY_TABLE_NAME_IDX02 379
MY_TABLE_OWNER MY_TABLE_NAME MY_TABLE_NAME_IDX03 479
MY_TABLE_OWNER MY_TABLE_NAME MY_TABLE_NAME_IDX04 10
MY_TABLE_OWNER MY_TABLE_NAME MY_TABLE_NAME_IDX05 1678
MY_TABLE_OWNER MY_TABLE_NAME MY_TABLE_NAME_IDX06 509
MY_TABLE_OWNER MY_TABLE_NAME MY_TABLE_NAME_IDX07 1482
MY_TABLE_OWNER MY_TABLE_NAME MY_TABLE_NAME_IDX08 1

Since we group at the table level, this will work for both partitioned and non-partitioned tables. Partitioned tables will have an entry per partition in the dba_segments table.

Knowing the difference between, DBA*, ALL* and USER* views, we can now query the size information for any table and its associated indexes.

Since there can be more than one table with the same name across schemas, the column “owner” should also be included in the query. Also, if the table has LOB data, please note that the size is not included in the above SQL and it involves querying dba_lobs.

Entire Database Size

This is the query I have been using to find the entire database size.

select    round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
,    round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
    round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
,    round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from    (select    bytes
    from    gv$datafile
    union    all
    select    bytes
    from     gv$tempfile
    union     all
    select     bytes
    from     gv$log) used
,    (select sum(bytes) as p
    from dba_free_space) free
group by free.p

How to do this in SQL Server?

As to the question of accomplishing the equivalent in SQL Server:

USE [MY_DATABASE]
EXEC sp_spaceused 'MY_TABLE_OWNER.MY_TABLE_NAME'
name rows reserved data index_size unused
MY_TABLE_NAME 23308287 8339632 KB 6344568 KB 1982216 KB 12848 KB

..and for the entire database, just leave out the parameter

USE [MY_DATABASE]
EXEC sp_spaceused
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s