How to calculate table + index size

# Thread: How to calculate table + index size

1. Member
Join Date
Jan 2001
Posts
138

## How to calculate table + index size

I need to calculate (in gigabytes) sum of the table and index size for a particular table. Is there a way to find out this? Thanks.

2. Senior Advisor
Join Date
Sep 2002
Location
England
Posts
7,334
select sum(bytes)/1024/1024/1024 ||'Gb' from dba_extents where segment_name = 'XXX';

3. Join Date
Jan 2003
Location
india
Posts
175
select sum(bytes)/1024/1024/1024 ||'Gb' from dba_extents where segment_name = 'XXX';

this shows the extents allocated to the table.

is there any way to find how much of the extents
are occupied with rows?

-Raja

4. Foreign Script Kiddie
Join Date
Aug 2002
Location
Colorado Springs
Posts
5,253
When a table has been analyzed you can get statistics to answer your question from the dba_tables, user_tables, or all_tables views.

5. Super Moderator
Join Date
Dec 2000
Location
Ljubljana, Slovenia
Posts
4,439
Originally posted by rajabalachandra
select sum(bytes)/1024/1024/1024 ||'Gb' from dba_extents where segment_name = 'XXX';

this shows the extents allocated to the table.

is there any way to find how much of the extents
are occupied with rows?
Nope. At least not without trace-dumping all the blocks belonging to the particular segment and inspecting those dumps.

6. Originally posted by jmodic
Nope. At least not without trace-dumping all the blocks belonging to the particular segment and inspecting those dumps.
Why not...and why do u wana go for dumping all blocks to know which extent has how many rows.. crazy idea.. when it can be done in a single query ...

Code:
```Select
Count(*)
From
My_Table
Where
Dbms_Rowid.Rowid_Relative_Fno(Rowid) = My_Extent's_Relative_File_Number
;

Or possibally

Select
Segment_Name  ,
Extent_Id     ,
A.CNT
From
(
Select
Dbms_Rowid.Rowid_Relative_Fno(Rowid) RFN ,
Count(*) CNT
From
My_Table
Group By
Dbms_Rowid.Rowid_Relative_Fno(Rowid)
) A,
DBA_EXTENTS
Where
Segment_Name = 'My_Table' And
RELATIVE_FNO  = A.RFN
;

Ofcourse this will eat up CPU.```
Hope i am correct in reading the requirement.

Abhay.
Last edited by abhaysk; 02-18-2004 at 08:54 AM.

7. Super Moderator
Join Date
Dec 2000
Location
Ljubljana, Slovenia
Posts
4,439
Originally posted by abhaysk
[BWhy not...and why do u wana go for dumping all blocks to know which extent has how many rows.. crazy idea.. [/B]
Khm, not so crazy. In fact, as I said, it's the only way to find out if the block contains any data or not (for tables, anyway; indexes are even more complicated).

Neither of your queries will display correct result with 100% accuracy, some blocks that actually contain data might not be encountered by your queries.

Speaking about crazy ideas - the only crazy idea in this thread was the requirement to find which blocks contain any data and which do not. I mean, even if it can be done, what would the result tell you anyway?

8. Originally posted by jmodic
Neither of your queries will display correct result with 100% accuracy, some blocks that actually contain data might not be encountered by your queries.
The chances of err would be very very less..and that to only in cases of row chains/migration..

#### Posting Permissions

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

Click Here to Expand Forum to Full Width