Уменьшение размера tablespace в Oracle.

После чистки таблиц в базе, а затем выполнения их дефрагментации, высвободилось достаточно много свободного места в табличном пространстве. В итоге 24 datafile's размером по 32Гб каждый получились заняты процентов по 20-25%. Решено было уменьшить размеры самих datafile's, чтобы освободить место на файловой системе.

Размеры tablespace's можно посмотреть так:

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 (+);

Результат выглядит примерно так:

Здесь видно, что USERS занимает 618Gb, а использует 129Gb. Посмотрим до какого размера можно сжать tablespace.

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(+);

Результат:

Там, где есть возможность уменьшить размер datafale's - уменьшаем. Т.е. datafile
E:\ORACLE\DATA\DSAUDIT\USERS19.DBF можно уменьшить до 6200Мб.

Пример:

SQL>alter database datafile 'E:\ORACLE\DATA\DSAUDIT\USERS19.DBF' resize 6200M;

Остальные datafile's уменьшить не получается, т.к. в них данные раскиданы по всему пространству. А это значит, что таблички нужно переносить в другой tablespace. Можно перенести все сразу таблички, уменьшить размер, и вернуть таблички на место. А можно найти объекты, которые раскиданы по tablespace, и переместить только их.

select dba_extents.owner,
   dba_extents.segment_name,
   dba_extents.segment_type,
   dba_extents.tablespace_name,
   dba_extents.file_id,
   dba_extents.block_id
from   dba_extents,
   (select file_id,
          max(block_id) max_block_id
   from   dba_extents
   group  by file_id) b
where  dba_extents.file_id = b.file_id and
   dba_extents.block_id = b.max_block_id;

Результат:





Для переноса таблицы в другой tablespace:

SQL>alter table PINECONE.QUERY_TABLE_TBL move tablespace TEST;

Затем в перенесённой таблице необходимо сделать rebuild индексов.
Посмотрим какие индексы есть в таблице

SQL>SELECT STATUS, INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME = 'QUERY_TABLE_TBL';

STATUS INDEX_NAME
-------- ------------------------------
UNUSABLE QTT_OWNER_NAME

И делаем непосредственно ребилд

SQL> ALTER INDEX SYS_C005185 REBUILD;

Проверяем

SQL>SELECT STATUS, INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME = 'QUERY_TABLE_TBL';

STATUS INDEX_NAME
-------- ------------------------------
VALID QTT_OWNER_NAME

Для переноса индексов в другой tablespace:

SQL>alter index PINECONE.SYS_C005180 rebuild tablespace TEST;

Также определяем и переносим другие таблички или индексы,и пробуем снова уменьшить tablespace.

Определяем объекты, находящиеся в корзине:

select decode(partition_name, null,
     segment_name,
     segment_name || ':' || partition_name) objectname,
 segment_type object_type,
 owner,
 tablespace_name,
 header_block
from   dba_segments
where  tablespace_name = 'PINECONE' and
 segment_name like 'BIN$%';

И удаляем их, например так:

SQL>purge table PINECONE."BIN$0IlaH5/6SyGv+h8B8BzJzQ==$0";

Уменьшаем tablespace.

SQL>alter database datafile 'F:\ORACLE\DATA\DSAUDIT\USERS23.DBF' resize 10M;
...
...
...
В итоге получилось так:
Теперь переносим таблицы и индексы обратно.

Составлено на основе материалов http://all-oracle.ru, http://ora-20000.blogspot.com/2009/04/tablespace.html