СУБД Oracle. Работа с табличными пространствами (tablespace)

Вывести список табличных пространств (ТП):

SQL> SELECT free.tablespace_name TABLESPACE, 

    ROUND(files.bytes / 1073741824, 2) gb_total,

    ROUND((files.bytes - free.bytes)  / 1073741824, 2) gb_used,

    ROUND(free.bytes  / files.bytes * 100) || '%' "%FREE" 

FROM (SELECT tablespace_name, SUM(bytes) bytes FROM dba_free_space GROUP BY tablespace_name) free,

  (SELECT tablespace_name, SUM(bytes) bytes FROM dba_data_files GROUP BY tablespace_name) files

WHERE 

  free.tablespace_name = files.tablespace_name;

SQL> select  a.tablespace_name,

       round(a.bytes_alloc / 1024 / 1024, 2) m_alloc,

       round(nvl(b.bytes_free, 0) / 1024 / 1024, 2) m_free,

       round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024, 2) m_used,

       round(maxbytes/1048576,2) Max

from  (select  f.tablespace_name,

               sum(f.bytes) bytes_alloc,

               sum(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes

        from dba_data_files f

        group by tablespace_name) a,

      (select  f.tablespace_name,

               sum(f.bytes)  bytes_free

        from dba_free_space f

        group by tablespace_name) b

where a.tablespace_name = b.tablespace_name (+);

Вывести список табличных пространств и расположение dbf файлов:

SQL> SELECT  substr(tablespace_name, 1, 10) AS Tablespace_Name,

        substr(file_name, 1, 100) AS File_Name,

bytes/1024/1024 AS Size_Mb

FROM dba_data_files

ORDER BY tablespace_name, file_name;

select dba_data_files.file_name,

   dba_data_files.file_id,
   dba_data_files.tablespace_name,
   ceil((nvl(hwm, 1) * db_block_size) / 1024 / 1024) smallest,
   ceil(blocks * db_block_size / 1024 / 1024) currsize,
   ceil(blocks * db_block_size / 1024 / 1024) -
   ceil((nvl(hwm, 1) * db_block_size) / 1024 / 1024) savings
from   dba_data_files,
   (select file_id,
          max(block_id + blocks - 1) hwm
   from   dba_extents
   group  by file_id) b,
   (select value db_block_size from v$parameter where name = 'db_block_size') c
where  dba_data_files.file_id = b.file_id(+);

Увеличить размер ТП. Добавить dbf:

ALTER TABLESPACE '<Название_ТП>'

ADD DATAFILE '<Путь_к_файлу_.dbf>'

SIZE 1000M       -- Первоначальный размер

AUTOEXTEND ON    -- авторасширение

NEXT 10          -- шаг увеличения размера

MAXSIZE 1000M;   -- максимальный размер

Если не указать параметр MAXSIZE, то табличное пространство увеличивается до максимального размера датафайла = 32Гб, но не всегда, так как размер tablespace зависит от размера блока

Пример:

ALTER TABLESPACE USERS ADD DATAFILE '/mnt/r02tb/app/oracle/oradata/ORACL11B/users51.dbf' SIZE 1000M AUTOEXTEND ON;

Вывести объем, на который можно уменьшить ТП:

SQL> select dba_data_files.file_name,

   dba_data_files.file_id,
   dba_data_files.tablespace_name,
   ceil((nvl(hwm, 1) * db_block_size) / 1024 / 1024) smallest,
   ceil(blocks * db_block_size / 1024 / 1024) currsize,
   ceil(blocks * db_block_size / 1024 / 1024) -
   ceil((nvl(hwm, 1) * db_block_size) / 1024 / 1024) savings
from   dba_data_files,
   (select file_id,
          max(block_id + blocks - 1) hwm
   from   dba_extents
   group  by file_id) b,
   (select value db_block_size from v$parameter where name = 'db_block_size') c
where  dba_data_files.file_id = b.file_id(+);