How to calculate table + index size

[pcotten]

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.

[davey23uk]

select sum(bytes)/1024/1024/1024 ||'Gb' from dba_extents where segment_name = 'XXX';

[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?

-Raja

[slimdave]

When a table has been analyzed you can get statistics to answer your question from the dba_tables, user_tables, or all_tables views.

[jmodic]

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.

[abhaysk]

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

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.

[jmodic]

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?

[abhaysk]

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