Scripts
Tablespace used and allocated space
User Rating: / 1
- Details
-
Parent Category: Articles
-
Created on Wednesday, 17 March 2010 22:28
-
Last Updated on Tuesday, 10 December 2019 13:40
-
Published on Wednesday, 17 March 2010 22:28
-
Hits: 3666
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.