set echo off
set feedback off
set verify off
set linesize 110
spool c:\check_tablespace.log
SELECT a.tablespace_name,
       d.maxbytes max_size,
       d.COUNT number_of_files,
       d.currentbytes current_size,
       nvl(b.free,   0) free_space,
       d.currentbytes -nvl(b.free,   0) used_size,
  CASE WHEN d.currentbytes > d.maxbytes
       THEN 100 -ROUND(((a.avail -nvl(b.free,   0)) / d.currentbytes) * 100,   2)
       ELSE 100 -ROUND(((a.avail -nvl(b.free,   0)) / d.maxbytes) * 100,   2)
   END percent_free
  FROM
       (
       SELECT tablespace_name,
              SUM(COUNT) COUNT,
              SUM(ROUND(ma / 1048576,    2)) maxbytes,
              SUM(ROUND(mc / 1048576,    2)) currentbytes
         FROM
              (
              SELECT tablespace_name,
                     SUM(bytes) ma,
                     SUM(bytes) mc,
                     COUNT(file_name) COUNT
                FROM dba_data_files
               WHERE maxbytes = 0
            GROUP BY tablespace_name
           UNION ALL
              SELECT tablespace_name,
                     SUM(maxbytes) ma,
                     SUM(bytes) mc,
                     COUNT(file_name) COUNT
                FROM dba_data_files
               WHERE maxbytes <> 0
            GROUP BY tablespace_name
              )
     GROUP BY tablespace_name
       ) d,
       (
       SELECT tablespace_name,
              ROUND(SUM(bytes) / 1048576,    2) avail
         FROM dba_data_files
     GROUP BY tablespace_name
       ) a,
       (
       SELECT tablespace_name,
              ROUND(SUM(bytes) / 1048576,    2) free
         FROM dba_free_space
     GROUP BY tablespace_name
       ) b
 WHERE a.tablespace_name = b.tablespace_name(+)
   AND a.tablespace_name = d.tablespace_name
-- AND ROUND(((a.avail -nvl(b.free,0)) / d.maxbytes) * 100,   2) > 90
/
spool off



=====================================================================================================

select file_name, tablespace_name from dba_data_files where tablespace_name = 'xxxx' order by 1;

=====================================================================================================

alter tablespace ***** add datafile 'D:\ORACLE\ORADATA\OPERA\******Next available number 04.DBF' size 2048M autoextend off;