-
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.
-
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.
-
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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 09:54 AM.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
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?
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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..
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|