Friday, October 14, 2011

Oracle: Sql script to check tablespace size

Every once in a while one of my test jobs fails, throwing this error that the table-space its created in has no further space to allocate for this table. And then comes the next sets of steps. Find out the other table space which is free.
Here is a very basic sql scripts which I use to list down all the tablespaces and their size in human readable format.


SELECT /* + RULE */  
df.tablespace_name AS "Tablespace",
df.bytes / (1024 * 1024 * 1024) AS "Size (GB)",
Trunc(fs.bytes / (1024 * 1024 * 1024)) AS "Free (GB)"       
FROM 
(
SELECT 
tablespace_name,
Sum(bytes) AS bytes 
FROM 
dba_free_space
GROUP BY
tablespace_name
) fs, 
( 
SELECT 
tablespace_name,
SUM(bytes) AS bytes
FROM 
dba_data_files
GROUP BY 
tablespace_name

) df
WHERE 
fs.tablespace_name = df.tablespace_name
ORDER BY 3 desc

1 comment:

  1. Great Thank you so much for this script

    ReplyDelete