| DBMS_SPACE or dba_segments [message #176567] |
Thu, 08 June 2006 15:19  |
theophanie
Messages: 1
Registered: June 2006
|
Junior Member |
|
|
Hi everybody !
This is my first message on this forum.
Greetings from France where I am working as a dba ( beginner ).
I would like to find the amount of free space inside a segment.
I know the dba_free_space is designed for tablespaces.
So, after searching the Web, I found out the DBMS_SPACE package.
With the results returned by the unused_space procedure ( columns total_bytes and unused_bytes ), I then calculated a certain amount of used space inside the segment ( a simple table with no partition ) : (unused_bytes / total_bytes ) * 100 ==> about 83% of used space
I then used the dba_segments view to make another calculation using the blocks column as well as the rowid as shown hereafter :
SELECT BLOCKS ALLOCATED_BLKS,
COUNT(DISTINCT SUBSTR(T.ROWID,1,8)
|| SUBSTR(T.ROWID,15,4)) USED,
(COUNT(DISTINCT SUBSTR(T.ROWID,1,8)
|| SUBSTR(T.ROWID,15,4))
/ BLOCKS) * 100 PCT_USED
FROM SYS.DBA_SEGMENTS E,
&TAB_NAME T
WHERE E.SEGMENT_NAME = UPPER ('&TAB_NAME')
AND E.SEGMENT_TYPE = 'TABLE'
GROUP BY E.BLOCKS;
The result was completely different ( about 43% used space in blocks).
How is it possible to get such a difference ?
I have certainly made a huge error, confusing two opposite concepts.
Thanks for your help.
Jean-michel
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
