28 | 03 | 2024
Latest Articles
Popular Articles

Scripts

Tablespace used and allocated space

User Rating:  / 1
PoorBest 

Tablespace used space

This is a script I wrote some time ago. It used to run at regular interval for all of my databases. I still use it daily. This allows me to follow up the tablespace used space of any database. Note it takes in account whether the data files are autoextensible or not. Data files which are autoextensible is something I dislike. If I use this feature I always configure a maxsize.

This scripts selects from dba_segments and dba_data_files. Database block fragmentation and free space under the segments high water mark is not detected with it.

column tablespace_name format a30
column "USED_SPACE" format 999999.99
column "ALLOCATED_SPACE" format 999999.99
column "ALLOWED_MAX_SPACE" format 999999.99
spool used_space
select 
a.tablespace_name,round(sum(a.bytes)/(1024*1024),2) "USED_SPACE", 
(select sum(b.bytes)/(1024*1024) 
        from dba_data_files b 
        where a.tablespace_name=b.tablespace_name 
        group by b.tablespace_name) "ALLOC_SPACE",
(select 
   sum(greatest(b.bytes/(1024*1024),b.maxbytes/(1024*1024)))
   from dba_data_files b 
   where a.tablespace_name=b.tablespace_name 
   group by b.tablespace_name) "MAX_SPACE",
round((
(select sum(c.bytes)/(1024*1024) 
        from dba_segments c,dba_tablespaces d 
        where c.tablespace_name=d.tablespace_name and a.tablespace_name=d.tablespace_name 
        group by d.tablespace_name)*100/
(select sum(greatest(b.bytes/(1024*1024),b.maxbytes/(1024*1024))) 
        from dba_data_files b 
        where a.tablespace_name=b.tablespace_name 
        group by b.tablespace_name) 
),0) "PCT"
from dba_segments a 
group by a.tablespace_name 
order by a.tablespace_name
/

select a.file_id,a.bytes_used/(1024*1024) "HWM_TEMP",
a.bytes_used/(1024*1024) + a.bytes_free/(1024*1024) "ALLOC_TEMP",
round(greatest(b.bytes/(1024*1024),b.maxbytes/(1024*1024)),2) "MAX_TEMP"
from v$temp_space_header a, dba_temp_files b
where a.file_id=b.file_id
order by a.file_id;


spool off;

I post a sample output, note I hide the real names of the tablespaces for security reasons.

TABLESPACE_NAME             USED_SPACE ALLOC_SPACE MAX_SPACE     PCT
--------------------------- ---------- ----------- ---------- ------

TS1	                       81.25      200       2000           4
TS2                           1687.00     2048       4000         42
TS3                            305.00      400       2000         15
TS4                            187.13      500       2200          9
SYSAUX                         882.38      900       2048         43
SYSTEM                         704.88      900       2048         34
UNDOTBS2                        14.25      400       2048          1
TS5                             41.75      100       2000          2
TS6                            120.00      150       2000          6
TS7                     	25.25      100       2000          1
TS8	                	66.50      100       2000          3

11 rows selected.