Re: tablespace issue

Posted by ErmanArslansOracleBlog on Apr 11, 2018; 11:22am
URL: http://erman-arslan-s-oracle-forum.124.s1.nabble.com/tablespace-issue-tp5622p5625.html

If you add datafiles to a tablespace , those datafiles are used.. Also, your free size is calculated accordingly.
So there is no such thing -> "other datafiles not being used despite showing free space"

I can't understand the situation, by looking at the pictures that you sent to me..

1)Send me the exact error your users are getting. (also the sql they are executing)

2)What is the name of the problematic tablespace? As for the problematic tablespace , send me the following query output;

select a.tablespace_name,
       a.bytes_alloc/(1024*1024) "TOTAL ALLOC (MB)",
       a.physical_bytes/(1024*1024) "TOTAL PHYS ALLOC (MB)",
       nvl(b.tot_used,0)/(1024*1024) "USED (MB)",
       (nvl(b.tot_used,0)/a.bytes_alloc)*100 "% USED"
from ( select tablespace_name,
       sum(bytes) physical_bytes,
       sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
       from dba_data_files
       group by tablespace_name ) a,
     ( select tablespace_name, sum(bytes) tot_used
       from dba_segments
       group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name (+)
--and   (nvl(b.tot_used,0)/a.bytes_alloc)*100 > 10
and   a.tablespace_name not in (select distinct tablespace_name from dba_temp_files)
and   a.tablespace_name not like 'UNDO%'
order by 1
--order by 5
/