-
An article on this site flagged as new entitled
LSS "How To" Series: Calculating Tablespace and Table Byte Sizes by David Nishimoto:
Has an incorrect piece of SQL.
The author suggests in this article that to find Tablespace byte sizes use the following SQL
Tablespace Byte Sizes
select distinct a.tablespace_name,
sum(a.bytes) as free_bytes,
sum(b.BYTES) as Total_Bytes
from dba_free_space a, dba_data_files b
Where a.tablespace_name = b.tablespace_name group by a.tablespace_name
Ohh no, big mistake.
Are these articles moderated, if so who moderates them because that is a query nearly all DBA's should know about.
Example:
SALES tablespace is made up of two datafiles both of 5Mbytes.
The SALES tablespace has some free space one 2Mbytes the other 3 Mbytes.
Correctly we would have 5Mbytes of free space and 10Mbytes total space.
The query above would report 10Mbytes of free space and 20Mbytes of total space.
You can't join dba_free_space and dba_data_files as above.
Try the following:
SELECT dfs.tablespace_name tablespace_name,
ddf.total_size total_size,
ddf.total_size - dfs.total_free total_used,
dfs.total_free total_free,
(ddf.total_size - dfs.total_free) / ddf.total_size * 100 cap,
dfs.total_chunks total_chunks,
dfs.largest_chunk largest_chunk
FROM (SELECT a.tablespace_name,
SUM(a.bytes) / 1024 / 1024 total_free,
COUNT(a.bytes) total_chunks,
MAX(a.bytes) / 1024 / 1024 largest_chunk
FROM dba_free_space a
GROUP BY a.tablespace_name) dfs,
(SELECT b.tablespace_name,
SUM(b.bytes) / 1024 / 1024 total_size
FROM dba_data_files b
GROUP BY b.tablespace_name) ddf
WHERE dfs.tablespace_name = ddf.tablespace_name
ORDER BY dfs.tablespace_name
Did look to see if I could report this error to someone but couldn't find a feedback e-mail address, anyone know of one?
David Nashimoto, shame on you.
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
|