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 table-spaces and their size in human readable format.

Update: Replacing with Github Gist

	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

view raw Oracle Script - Check Tablespace Size.sql hosted with ❤ by GitHub