Администратор БД Oracle. Дефрагментация табличного пространства в Oracle 11g

Часто бывает так, что вы удаляете много данных из табличного пространства и хотите выполнить resize, но это невозможно сделать, так как данные в табличном пространстве сильно дефрагментированы.
Для того, чтобы выполнить процедуру resize необходимо переместить таблицы и лоб сегменты в это же табличное пространство, перестроить индексы.
Для начала нам необходимо выяснить как распределены данные:

select file_id, block_id first_block, block_id+blocks-1 last_block, 

segment_name,

case segment_type

  when 'TABLE' then

       'ALTER '|| segment_type || ' ' || owner || '.' ||segment_name || ' move parallel 16 nologging;'

  when 'INDEX' then

       'ALTER INDEX '|| owner || '.' || segment_name ||' REBUILD ONLINE PARALLEL 16 tablespace <tablespace_name>;'

    end, segment_type

      from dba_extents

     where tablespace_name = '<tablespace_name>' 

     union all

   select file_id, block_id, block_id+blocks-1, 'free', 'sql','segment'

      from dba_free_space

     where tablespace_name = '<tablespace_name>' 

    order by file_id, first_block desc

В 5 столбце у нас готовые скрипты для запуска, запуская скрипты, вы устраняете дефрагментацию табличного пространства.

В том случае, если тип сегмента LOB, нам необходимо определить какой таблице он принадлежит:

select table_name, column_name from dba_lobs where segment_name =  '<segment_name>'; 

полученное имя таблицы подставляем в скрипт, не забываем при этом изменить имя табличного пространства на нужное вам:

select 'ALTER TABLE '|| owner || '.' || table_name ||' MOVE LOB ('|| COLUMN_NAME ||') STORE AS (TABLESPACE <tablespace_name>) PARALLEL 16 nologging' from dba_lobs where table_name = '<table_name>';

Таким образом перемещая данные у вас будет появляться больше свободного пространства, постепенно вы можете выполнять команду resize, но придется посчитать место по блокам. Если у вас стандартный размер блока 8К, то поступаем так:

берем первый блок и отнимаем последний 303792135 - 297029638 = 6762497
полученную разницу умножаем на 8 и получаем размер свободного пространства в Кб на которое мы можем уменьшить наше табличное пространство, у нас получается 54099976 Кб

--alter database datafile '+DATA/test/data8k.dbf' resize 9740932140236,8