Find Tablespace Usage Percent
I have written a sql query which would output the usage percent of all the tablespaces in a given database. Although lots of tablespace usage queries are available, most of them, if not all, utilise dba_free_space view as the tool of measurement. It is well known that this view is inadequate when datafiles have autoextend feature turned on. I have tried to address this inadequacy in my query. Caveat : This query is not optimised for performance. Hence, you might observe slow performance. set linesize 100 set pagesize 10000 set feedback off verify off col tablespace_name format a18 col num_files format 999 col total_space format 9999999.99 col free_space format 9999999.99 col used_percent format 999.99 select /*+ parallel(tbs) */ tbs.tablespace_name, count(tbs.file_id) num_files, round(sum(TOTAL_SPACE),2) TOTAL_SPACE_MB, sum(FREE_SPACE) FREE_SPACE_MB, round(((sum(tbs.USED_SPACE)/sum(tbs.TOTAL_SPACE)) * 100),2) USED_PERCENT from (select ddf.file_id file_id, ddf.file_name, ddf.tablespace