I know this has been done to death, but I wanted to just go over what I do when OEM alerts me to a tablespace running out of space.
First, I check the free space of the tablespace using this query:
SET LINESIZE 100
COLUMN TABLESPACE FORMAT A15
select t.tablespace, t.totalspace as " Totalspace(MB)",
round((t.totalspace-fs.freespace),2) as "Used Space(MB)",
fs.freespace as "Freespace(MB)",
round(((t.totalspace-fs.freespace)/t.totalspace)*100,2) as "% Used",
round((fs.freespace/t.totalspace)*100,2) as "% Free"
from
(select round(sum(d.bytes)/(1024*1024)) as totalspace, d.tablespace_name tablespace
from dba_data_files d
group by d.tablespace_name) t,
(select round(sum(f.bytes)/(1024*1024)) as freespace, f.tablespace_name tablespace
from dba_free_space f
group by f.tablespace_name) fs
where t.tablespace=fs.tablespace
order by t.tablespace;
Secondly, I check if it is autoextendible and what the MAXBYTES is.
select file_name, bytes/1024/1024 size_MB, AUTOEXTENSIBLE, maxbytes/1024/1024 maxsize_MB from dba_data_files where tablespace_name = 'RBPTAB';
To my understanding, if you don't do this you wont allow for what the datafile has been allowed to autoextend up to. The first query will only give allocated size, not the real, actual, TOTAL size that it can grow to.
Once you know that, from query 1, there is no space left, and from 2 it is really hitting its MAXSIZE, (compare the MAXSIZE of 2 with the Used Space(MB) column from 1) ... then add a new one.
Does this make sense?