title: Reporting tablespace usage link: https://onemoretech.wordpress.com/2009/08/25/reporting-tablespace-usage/ author: lembobro description: post_id: 264 created: 2009/08/25 19:59:12 created_gmt: 2009/08/25 19:59:12 comment_status: open post_name: reporting-tablespace-usage status: publish post_type: post

Reporting tablespace usage

Thanks to V.S. Babu for this neat little SQL script that gives a quick enumeration of tablespace usage perfect for harried Oracle Internet Directory admins. I found this code in one of V.S.’s posts entitled Tablespace Information. V.S. has a whole section on Oracle administration and development that is worth a look.

For OID admins I recommend connecting as the ODS user. The database you’ll connect to will be your metadata repository for the infrastructure tier, of course.

[me@testbox ~]$ sqlplus ODS/xxxxxx@infradb1 ... SQL> @tablespaces.sql

Heres the code:

`

select	a.TABLESPACE_NAME,
	a.BYTES bytes_used,
	b.BYTES bytes_free,
	b.largest,
	round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used
from
	(
		select 	TABLESPACE_NAME,
			sum(BYTES) BYTES
		from 	dba_data_files
		group 	by TABLESPACE_NAME
	)
	a,
	(
		select 	TABLESPACE_NAME,
			sum(BYTES) BYTES ,
			max(BYTES) largest
		from 	dba_free_space
		group 	by TABLESPACE_NAME
	)
	b
where 	a.TABLESPACE_NAME=b.TABLESPACE_NAME
order 	by ((a.BYTES-b.BYTES)/a.BYTES) desc
/

`

Copyright 2004-2019 Phil Lembo