Tablespace

-- свободное место во всех табличных пространствах
SELECT * FROM
  (SELECT a.tablespace_name,
          round((b.alc_blocks+b.grow_blocks)*8192/1024/1024,2) AS total_mb,
          round(b.alc_blocks*8192/1024/1024,2) AS alc_mb,
          round((a.free_blocks+b.grow_blocks)*8192/1024/1024,2) AS free_mb,
          round(100*(a.free_blocks+b.grow_blocks)/(b.alc_blocks+b.grow_blocks),0) AS free_percent
   FROM
     (SELECT tablespace_name, Sum(blocks) AS free_blocks FROM dba_free_space GROUP BY tablespace_name) a,
     (SELECT tablespace_name,
             Sum(CASE WHEN autoextensible='YES' THEN floor((maxblocks-blocks)/increment_by)*increment_by ELSE 0 END) AS grow_blocks,
             sum(blocks) AS alc_blocks
      FROM dba_data_files GROUP BY tablespace_name) b
   WHERE a.tablespace_name = b.tablespace_name)
;
-- WHERE free_mb<10240 and free_percent<10;

--=========================================================================================================================
-- работа с файлами данных
-- максимально допустимый размер файла табличного пространства 32G-16K=33554416K;
--=========================================================================================================================
-- файлы табличного пространства TS1
SELECT file_id,file_name,round(bytes/1024/1024/1024,2) as GB, autoextensible, round(maxbytes/1024/1024/1024,2) as MaxGB, increment_by FROM dba_data_files
WHERE tablespace_name='TS1';

-- изменение размера файла данных (M - Megabyte, G - Gigabyte)
ALTER DATABASE DATAFILE '<path>' RESIZE <new_size>;

-- настройка автоинкримента
ALTER DATABASE DATAFILE '<path>' AUTOEXTEND ON NEXT <inc_size> MAXSIZE <max_size>;

-- добавление файла в табличное пространство
ALTER TABLESPACE <tablespace_name> ADD DATAFILE '<path>' SIZE <first_size> AUTOEXTEND ON NEXT <incr_size> MAXSIZE <max_size>;