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