Oracle DBA Daily Experiences

the script below will help you to see how big auto allocation is set plus all the report for the current used and free space.

[spocoradb1.aeso.ca]/dbadmin/workspace2/alima/sql>cat db_tablespaces_report.sql
set linesize 200
set trimspool on
set pagesize 100
set feedback off
column dummy noprint
column pct_used format 999.9 heading “%|Used”
column name format a30 heading “Tablespace Name”
column Kbytes_alloc format 999,999,999,999 heading “Alloc|KBytes”
column currently_used format 999,999,999,999 heading “Used|KBytes”
column kbytes_free format 999,999,999,999 heading “Free|KBytes”
column Kbytes_max format 999,999,999,999 heading “MaxPoss|Kbytes”
column pct_free format 999.99 heading “%Free”
column max_kbytes_free format 999,999,999,999 heading “MaxPoss|Free|KBytes”
column max_pct_used format 999.99 heading “%Max|Used”
column host_name format a30

spool $vfile_email

select TO_CHAR(sysdate, ‘DD-MON-YYYY HH24:MI:SS’) TODAY FROM dual
/
SELECT host_name, instance_name FROM v$instance
/
break on report
compute sum of Kbytes_alloc on report
compute sum of kbytes_free on report
compute sum of currently_used on report
compute sum of Kbytes_max on report
select decode(extent_management,’LOCAL’,'*’,”) ||
decode(segment_space_management,’AUTO’,'a ‘,’m ‘) ||
b.tablespace_name name,
Kbytes_alloc,
kbytes_max,
(Kbytes_alloc – NVL(kbytes_free, 0)) currently_used,
NVL(kbytes_free, 0) kbytes_free,
(NVL(kbytes_free, 0))*100/Kbytes_alloc pct_free,
(Kbytes_max – Kbytes_alloc + NVL(kbytes_free, 0)) max_kbytes_free,
(Kbytes_alloc – NVL(kbytes_free, 0))*100/kbytes_max max_pct_used
FROM
( select sum(bytes)/1024 Kbytes_free,
max(bytes)/1024 largest,
tablespace_name
from sys.dba_free_space
group by tablespace_name ) a,
( select sum(bytes)/1024 Kbytes_alloc,
sum(decode(AUTOEXTENSIBLE, ‘NO’, bytes,
decode(sign(maxbytes – bytes), 1, maxbytes, -1, bytes, 0,maxbytes)))/1024 Kbytes_max,tablespace_name
from sys.dba_data_files
group by tablespace_name) b,
dba_tablespaces c
where a.tablespace_name (+) = b.tablespace_name
AND c.tablespace_name = b.tablespace_name
AND b.tablespace_name NOT IN (select distinct TABLESPACE_NAME from
DBA_UNDO_EXTENTS)
— 5% free and less than 2GB
– AND (Kbytes_max – Kbytes_alloc + NVL(kbytes_free, 0))*100/kbytes_max < 5
– AND (Kbytes_max – Kbytes_alloc + NVL(kbytes_free, 0)) < (2*1024*1024)
ORDER BY (Kbytes_max – Kbytes_alloc + NVL(kbytes_free, 0))*100/kbytes_max, b.tablespace_name
/
spool off