# Thread: how to find the size of a table

1. Member
Join Date
Jan 2003
Posts
141

## how to find the size of a table

Hi,
How to find the size of a table?

Thanks,

2. For a simple calculation BLOCKS in DBA_TABLES will give you the number of blocks used.

HTH

3. Hello ,

Try this:

analyze table [table_name] compute statistics;

and then :

select blocks from user_tables where table_name=[table_name];

and multiply blocks with your block_size

I guess this is it (?)

HTH

4. jovery, you're too fast...

5. Code:
`select round(sum(bytes)/1024/1024) "Size of table in MB" from dba_segments where segment_name='TABLE_NAME'`
If you want to calculate from DBA_TABLES, then you have to ANALYZE and so...

When you get info in DBA_SEGMENTS why go for DBA_TABLES?

Abhay.

6. Your right Abhay, I misread the question (teach me for responding to fast) I thought he wanted number of used blocks in table.

Whooops

7. Originally posted by jovery
I thought he wanted number of used blocks in table.
Even that is in DBA_SEGMENTS.

Code:
```WW04_DBA> select Blocks from dba_segments where segment_name='WKLY_ADV_DELTA_AGGR_FIS';

BLOCKS
--------------------
300243

WW04_DBA> select round((Blocks)*8192/1024/1024) "Size of table in MB" from dba_segments where segment_name='WKLY_ADV_DELTA_AGGR_FIS';

Size of table in MB
--------------------
2346

WW04_DBA> select round(sum(bytes)/1024/1024) "Size of table in MB" from dba_segments where segment_name='WKLY_ADV_DELTA_AGGR_FIS';

Size of table in MB
--------------------
2346

WW04_DBA>```

8. USED blocks I said

Now who's too fast?

9. Pando & Company
Join Date
Jun 2000
Location
Posts
7,447
it depends, dba_segments tell the allocated size, dba_tables tells the real size

10. OS dosent see who many Blocks are used or Not..its just a logical approch for Oracle...

Allocated Space is what OS sees...and this is at file level..(Disk Level rather)
At file level Segment is allocated N bytes...and we can take it as size of the table.

So (N/8192) blocks are allocated for the segment & we can well treat this as size in context to OS.

If it boils down to Oracle application, then (N/8192) blocks will be misleading as some of the blocks allocated and not used, as seen from DBA_SEGMENTS.

So, if you see as physical point of view, then DBA_SEGMENTS will give accurate size of the table.

Abhay.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•