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.
Printable View
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.
select sum(bytes)/1024/1024/1024 ||'Gb' from dba_extents where segment_name = 'XXX';
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
When a table has been analyzed you can get statistics to answer your question from the dba_tables, user_tables, or all_tables views.
Nope. At least not without trace-dumping all the blocks belonging to the particular segment and inspecting those dumps.Quote:
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?
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 ...Quote:
Originally posted by jmodic
Nope. At least not without trace-dumping all the blocks belonging to the particular segment and inspecting those dumps.
Hope i am correct in reading the requirement.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.
Abhay.
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).Quote:
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]
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?
The chances of err would be very very less..and that to only in cases of row chains/migration..Quote:
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.