How to determine tables size in Oracle

Question

I use Oracle 11 and want to find out the size of my tables (like in megabytes). What should I do? Should I check the file size on server? Or is there any query to run?

duplicate of http://dba.stackexchange.com/q/3457 – ninesided Nov 19 '14 at 08:58 — ninesided, Nov 19 '14 at 08:58

Mudit Saklani · Answer 1 · 2014-11-20T03:46:22.207

70

If you don't have DBA rights then you can use user_segments table:

select bytes/1024/1024 MB from user_segments where segment_name='Table_name'

edited Nov 20 '14 at 03:46

answered Nov 19 '14 at 09:21

Mudit Saklani

752
4
10

2

Thanks, that above query is awesome because usually we don't have DBA access . – Osama Al-Banna Sep 15 '19 at 09:34

score 59 · Accepted Answer · edited Nov 19 '14 at 09:10

59

Here is a query, you can run it in SQL Developer (or SQL*Plus):

SELECT DS.TABLESPACE_NAME, SEGMENT_NAME, ROUND(SUM(DS.BYTES) / (1024 * 1024)) AS MB
  FROM DBA_SEGMENTS DS
  WHERE SEGMENT_NAME IN (SELECT TABLE_NAME FROM DBA_TABLES)
 GROUP BY DS.TABLESPACE_NAME,
       SEGMENT_NAME;

edited Nov 19 '14 at 09:10

psaraj12

4,772
2
21
30

answered Nov 19 '14 at 08:57

Query is useful but doesn't include index. For index size see https://dba.stackexchange.com/questions/1151/how-to-find-the-actual-space-being-consumed-by-indexes-on-a-table-in-oracle – Jean-Christophe Blanchard Jun 05 '18 at 15:02
DBA/USER SEGMENTS still showing table info even was deleted, Is ther a way to refresh the info or force the new watermark? – Osy Mar 20 '19 at 02:12

How to determine tables size in Oracle

2 Answers2

Linked