Как уменьшить табличное пространство отката (undo tablespace)

ENG: How to decrease undo tablespace

С увеличение табличного пространства UNDO обычно нет проблем. Просто увеличиваем файл табличного пространства или добавляем еще один и всё это выполняется прямо на работающей базе без проблем.
А вот с уменьшением размера обычно возникают проблемы.
Можно попробовать просто уменьшить файл табличного пространства, но обычно, на рабочей базе, UNDO постоянно используется. Поэтому, скорее всего придется уменьшать путем создания нового UNDO меньшего размера.


-- Создаем новое табличное пространство UNDO_NEW
CREATE UNDO TABLESPACE undo_new DATAFILE 'D:DATABASESDB4UNDOTBS01.DBF' SIZE 1300M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
-- Для ASM создание может быть таким
CREATE UNDO TABLESPACE undo_new DATAFILE '+DATA(datafile)' SIZE 1300M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

-- Делаем его используемым по умолчанию
ALTER SYSTEM SET undo_tablespace='UNDO_NEW';

-- Удаляем старое табличное пространство
DROP TABLESPACE undo_old INCLUDING CONTENTS AND DATAFILES;

-- Если название табличного пространства принципиально, то можно переименовать UNDO_NEW в UNDO_OLD
ALTER TABLESPACE undo_new RENAME TO undo_old;

©Bobrovsky Dmitry
Старое табличное пространство UNDO может не удалиться сразу, т.к. его всё еще могут использовать какие-то сессии для отката транзакций. В этом случае можно попробовать удалить через некоторое время или с помощью запроса посмотреть какие сессии используют UNDO и прекратить их.
©Bobrovsky Dmitry


-- Определение активных сессий(транзакций) которые держат UNDO

-- Здесь просто посмотреть какие сегменты UNDO используются и время начала использования (насколько давно)
SELECT t.status, t.start_time, rbs.segment_name, rbs.tablespace_name
FROM v$transaction t, dba_rollback_segs rbs
WHERE t.xidusn = rbs.segment_id AND rbs.tablespace_name IN ('UNDO_OLD');

-- Более информативный запрос, показывает сессии которые используют UNDO
SELECT *
FROM v$session
WHERE saddr IN (SELECT t.ses_addr
FROM v$transaction t, dba_rollback_segs rbs
WHERE t.xidusn = rbs.segment_id AND rbs.tablespace_name IN ('UNDO_OLD'));

-- Прекращаем эти сессии
ALTER SYSTEM KILL SESSION '2631, 14103';

Dmitry Bobrovsky
Для RAC с ASM у каждого экземпляра своё табличное пространство UNDO.
Dmitry Bobrovsky


-- Посмотреть табличные пространства отката для каждого экземпляра
SELECT *
FROM gv$parameter
WHERE name = 'undo_tablespace'
ORDER BY inst_id, name;

-- Создаем новые табличные пространства UNDO_NEWх
CREATE UNDO TABLESPACE undo_new1 DATAFILE '+DATA(datafile)' SIZE 1300M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
CREATE UNDO TABLESPACE undo_new2 DATAFILE '+DATA(datafile)' SIZE 1300M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

-- Делаем их используемыми по умолчанию для каждого экземпляра своё
ALTER SYSTEM SET undo_tablespace =undo_new1 SID='node1';
ALTER SYSTEM SET undo_tablespace =undo_new2 SID='node2';

-- Удаляем старые табличные пространства
DROP TABLESPACE undo_old1 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
DROP TABLESPACE undo_old2 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

Запись Как уменьшить табличное пространство отката (undo tablespace) впервые появилась Dmitry Bobrovsky Blog

— Author: Dmitry Bobrovsky Google

Запись опубликована в рубрике !RUS, Admin, ORACLE, Support с метками . Добавьте в закладки постоянную ссылку.