Еще раз про большой UNDO

Animal
Дата: 30.09.2004 04:21:37
Трабла в том, что при глобальном изменении большой таблицы один УНДО-сегмент вырос свыше 5 ГБ. Теперь соответственно такой объем сегмента явно никому не нужен. Как все таки вернуть ему первоначальный размер?

Прочитал много топиков в этом форуме по поводу как уменьшить UNDO.
Так и не получил ответа, потому что пересоздание всего табличного пространства как-то не прикалывает.
Вячеслав Любомудров
Дата: 30.09.2004 04:45:59
Если UNDO_MANAGEMENT=AUTO попробуй установить UNDO_RETENTION в небольшое значение (чтоб следущие транзакции не задерживались в конце таблспейса надолго) и через некоторое время (предыдущее значение UNDO_RETENTION) эти здоровые роллбэки шринкнутся автоматом. После этого alter database datafile ... resize;
Вячеслав Любомудров
Дата: 30.09.2004 04:51:40
Имелось ввиду, конечно, что сейчас при попытке alter database datafile resize выдается ORA-03297
Animal
Дата: 30.09.2004 04:52:28
UNDO_RETENTION = 10800 (DEFAULT) соответственно 3 часа. Уже прошло трое суток и здоровый ундо-сегмент и не собирается "шринкнутся автоматом".

вот я и не знаю что делать.
Вячеслав Любомудров
Дата: 30.09.2004 05:10:55
Ты не путаешь сегмент с табличным пространством?
Попробуй посмотреть
column name format a12 head "Name"
column extents format 999 head "Exts"
column rssize format 999,999,999,999 head "Size"
column writes format 999,999,999,999 head "Writes"
column hwmsize format 999,999,999,999 head "HW-Mark"
column aveshrink format 999,999,999,999 head "Ave Shrink"
column xacts format 999 head "Trans"

select name, extents, rssize, writes, hwmsize, aveshrink, xacts
from v$rollstat s, v$rollname n
where s.usn=n.usn
/
clear column
В любом случае поменяй UNDO_RETENTION и подожди немного
Возможно нужно поназапускать каких-нибудь коротких транзакций (но много)
Мне такой вариант помогал неоднократно и никогда не было никаких проблем

До еще по той большой таблице сделай какой-нибудь селект с фулл-сканом, возможно том проблема с отложенной очисткой
Animal
Дата: 30.09.2004 05:25:05
Name Exts Size Writes HW-Mark Ave Shrink Trans
------------ ---- ---------------- ---------------- ---------------- ---------------- -----
SYSTEM 6 385,024 7,598 385,024 0 0
_SYSSMU1$ 273 1,342,169,088 747,388,162 4,294,959,104 8,388,608 1
_SYSSMU2$ 24 9,428,992 8,830,244 9,428,992 0 0
_SYSSMU3$ 5 201,449,472 3,077,980,172 3,158,433,792 84,485,266 0
_SYSSMU4$ 24 9,428,992 8,926,454 84,795,392 6,048,036 0
_SYSSMU5$ 24 9,428,992 9,010,874 3,006,390,272 21,022,848 0
_SYSSMU6$ 24 9,428,992 8,807,136 9,428,992 0 0
_SYSSMU7$ 24 9,428,992 8,817,458 9,428,992 0 0
_SYSSMU8$ 24 9,428,992 8,908,902 9,428,992 0 0
_SYSSMU9$ 24 9,428,992 8,863,700 9,428,992 0 0
_SYSSMU10$ 5 201,449,472 3,671,024,588 3,690,979,328 94,339,957 0




А TOAD показывает, что сегмент вообще 5 Гиг.

В самом таблеспэйсе (7 Гиг) свободно показывает всего 1 Гиг.


Чё-то я запутался. УНДО-сегмент может уменьшиться меньше HW-Mark???
Вячеслав Любомудров
Дата: 30.09.2004 05:40:16
Это немного не тот HWM ;-)
Т.е. он, конечно, показывает когда-либо бывший максимальный размер сегмента, но после этого он у тебя много-много раз шринкался (Ave Shrink) и текущий размер меньше HWM.
Насколько я понял из твоего неформатированного текста (блин, ну есть же соответствующие теги) - у тебя только один большой сегмент, который кстати сейчас держит какая-то транзакция (возможно это та, в который ты выполняешь этот запрос, но проверь на всякий случай).

И еще вопрос: ты alter database datafile ... resize делал?
Animal
Дата: 30.09.2004 06:10:14
Это немного не тот HWM ;-)
Т.е. он, конечно, показывает когда-либо бывший максимальный размер сегмента, но после этого он у тебя много-много раз шринкался (Ave Shrink) и текущий размер меньше HWM.


А почему TOAD показывает, что сегмент 5 Гиг???

Насколько я понял из твоего неформатированного текста (блин, ну есть же соответствующие теги)
Прошу прощения. (какие именно теги?)

- у тебя только один большой сегмент, который кстати сейчас держит какая-то транзакция (возможно это та, в который ты выполняешь этот запрос, но проверь на всякий случай).
Нет не этой сессией.

И еще вопрос: ты alter database datafile ... resize делал?
А как я его ресайзну? Если в нем свободно только 1 Гиг??? А мне надо хотябы Гиг до 4 уменьшить.
(Даже на гиг уменьшить не дает, выдает ошибку ORA-03297)
Вячеслав Любомудров
Дата: 30.09.2004 06:38:21
Похоже TOAD берет HWMSIZE вместо RSSIZE
3-116 Oracle9 i Database Reference
...
  • EXTENTS NUMBER Number of extents in the rollback segment
  • RSSIZE NUMBER Size (in bytes) of the rollback segment. This value differs by the number
    of bytes in one database block from the value of the BYTES column of
    the ALL/DBA/USER_SEGMENTS views.
    See Also: Oracle9i Database Administrator's Guide.
    ...
  • HWMSIZE NUMBER High water mark of rollback segment size
  • SHRINKS NUMBER Number of times the size of a rollback segment decreases
    ...
  • AVESHRINK NUMBER Average shrink size...
  • Кстати я попутал shrinks и aveshrink в скрипте
    А не можеш освободить видимо все-таки из-за дефрагментации (один из сегментов где-то в конце файла лежит)

    Есть еще вариант - ALTER SYSTEM SET UNDO_TABLESPACE=''; (или создать новое UNDO ТП и переключиться на него), затем вернуться обратно и попробовать ресайзнуть

    А насчет тегов - ты когда сообщения набираешь видишь сверху кнопочки SRC FIX QUOTE ? Попробуй понажимать или FAQ почитать
    Animal
    Дата: 30.09.2004 06:53:52
    Есть еще вариант - ALTER SYSTEM SET UNDO_TABLESPACE=''; (или создать новое UNDO ТП и переключиться на него), затем вернуться обратно и попробовать ресайзнуть
    Ну это тоже самое, что создать новый, а старый дропнуть.