Как освободить место в табличном пространстве за счет LOB сегмента.

В статье описано как освободить место в табличном пространстве за счет LOB сегмент

Можно переместить LOB сегмент в табличное пространство, которое располагаются на более медленных устройствах хранения, но больших по объему и относительно дешевых.

Чтобы освободить место в табличном пространстве за счет LOB сегмента, можно использовать три способа:
A) Освободить неиспользуемое место выше High Water Mark (HWM).
B) Сделать SHRINK. В сегменте освободиться неиспользуемое место и место под удаленными данными.
C) Физически переместить LOB сегмент в другое табличное пространство.

ВНИМАНИЕ: Во время выполнения запросов из способов B) и C) LOB сегмент блокируется. Если LOB сегмент большой, то это может занять значительное время. Поэтому эти операции рекомендуется проводить во вне рабочее время.

Сначала определяем, с какими сегментами мы будем работать
0) Определяем размер LOB сегментов, чтобы понять какие из них самые большие

SELECT l.table_name,
L.COLUMN_NAME,
s.segment_name,
S.TABLESPACE_NAME,
l.owner,
s.bytes / 1024 / 1024 AS "MB"
FROM dba_lobs l, dba_segments s
WHERE     s.segment_name = l.segment_name
AND s.owner = l.owner
AND s.segment_type = 'LOBSEGMENT'
ORDER BY bytes DESC;

1) Сегмент может занимать больше места, чем в нем содержится данных. Этим запросом можно определить, сколько места занимают реальные данные конкретного сегмента.
Если место занимаемое данными намного меньше самого сегмента, значит есть свободное место в самом сегменте и его можно попробовать освободить способами A) и B)
Если данные занимают весь сегмент — значит чтобы освободить место в табличном пространстве его нужно переносить в другое табличное пространство.
Вместо переменных подставить:
<TABLE_NAME> — поле table_name из запроса 0
<SEGMENT_NAME> — поле segment_name из запроса 0

SELECT SUM (DBMS_LOB.getlength ('&SEGMENT_NAME')) / 1024 / 1024 AS "MB"
FROM &TABLE_NAME;

A)

A.1) Процедура показывает, сколько места можно освободить командой ALTER TABLE … DEALLOCATE UNUSED.
Если такого места много и вы хотите освободить его — выполните A.2.
Если такого места мало тогда место используйте способы B) или С).

DECLARE
TOTAL_BLOCKS                NUMBER;
TOTAL_BYTES                 NUMBER;
UNUSED_BLOCKS               NUMBER;
UNUSED_BYTES                NUMBER;
LAST_USED_EXTENT_FILE_ID    NUMBER;
LAST_USED_EXTENT_BLOCK_ID   NUMBER;
LAST_USED_BLOCK             NUMBER;
BEGIN
DBMS_SPACE.unused_space ('&OWNER',
'&SEGMENT_NAME',
'LOB',
TOTAL_BLOCKS,
TOTAL_BYTES,
UNUSED_BLOCKS,
UNUSED_BYTES,
LAST_USED_EXTENT_FILE_ID,
LAST_USED_EXTENT_BLOCK_ID,
LAST_USED_BLOCK);

DBMS_OUTPUT.put_line ('SEGMENT_NAME = <LOB SEGMENT NAME>');
DBMS_OUTPUT.put_line ('-');
DBMS_OUTPUT.put_line ('TOTAL_BLOCKS = ' || TOTAL_BLOCKS);
DBMS_OUTPUT.put_line ('TOTAL_BYTES = ' || TOTAL_BYTES);
DBMS_OUTPUT.put_line ('TOTAL_MB = ' || TOTAL_BYTES / 1024 / 1024);
DBMS_OUTPUT.put_line ('UNUSED_BLOCKS = ' || UNUSED_BLOCKS);
DBMS_OUTPUT.put_line ('UNUSED BYTES = ' || UNUSED_BYTES);
DBMS_OUTPUT.put_line ('UNUSED MB = ' || UNUSED_BYTES / 1024 / 1024);
DBMS_OUTPUT.put_line (
'LAST_USED_EXTENT_FILE_ID = ' || LAST_USED_EXTENT_FILE_ID);
DBMS_OUTPUT.put_line (
'LAST_USED_EXTENT_BLOCK_ID = ' || LAST_USED_EXTENT_BLOCK_ID);
DBMS_OUTPUT.put_line ('LAST_USED_BLOCK = ' || LAST_USED_BLOCK);
END;
/

A.2) Если такого места много его можно освободить командой
<TABLE_NAME> — поле table_name из запроса 0
<COLUMN_NAME> — поле COLUMN_NAME из запроса 0
alter table ELA_DOCUMENT modify lob (ELADO_BYTES) (deallocate unused);

ALTER TABLE &TABLE_NAME MODIFY LOB (&COLUMN_NAME) (DEALLOCATE UNUSED);

B)

B.1) Процедура показывает, сколько места можно освободить командой ALTER TABLE … DEALLOCATE UNUSED.
Если такого места много и вы хотите освободить его — выполните A.2.
Если такого места мало тогда место используте способы B) или С).
<TABLE_NAME> — поле table_name из запроса 0
<COLUMN_NAME> — поле COLUMN_NAME из запроса 0
alter table ELA_DOCUMENT modify lob(ELADO_BYTES) (shrink space);
alter table ELA_DOCUMENT modify lob(ELADO_BYTES) (shrink space cascade);
Опция cascade — применяет SHRINK не только к LOB сегменту но и к связанным с ним LOB индексам

ALTER TABLE &TABLE_NAME MODIFY LOB(&COLUMN_NAME) (SHRINK SPACE CASCADE);

C)

C.1) Запрос которым LOB сегмент переноситься в другое табличное пространство
Вместо переменных подставить:
<TABLE_NAME> — поле table_name из запроса 0
<COLUMN_NAME> — поле COLUMN_NAME из запроса 0
<NEW_TABLESPACE> — имя табличного пространства в которое вы хотите перенести LOB сегмент
ALTER TABLE ELA_DOCUMENT MOVE LOB(ELADO_BYTES) STORE AS (TABLESPACE DATA2);

ALTER TABLE &TABLE_NAME MOVE LOB(&SEGMENT_NAME) STORE AS (TABLESPACE &NEW_TABLESPACE);