Database Administration (MOSC)

MOSC Banner

ALTER TABLE SHRINK SPACE - transactions and undo usage?

edited August 2014 in Database Administration (MOSC) 11 commentsAnswered

Hi all,

I have a large segment (about 220GB) which needs online shrinking.

The problem is that a high amount of undo is being generated during the operation, causing the operation to fail with ORA-30036: unable to extend segment by 8 in undo tablespace <...>, thus causing risks for the application due to UNDO getting used up.

These are my findings so far:

+ 2 transactions are assiciated to the session, one is long running, the other one is short term and recursive. Both seem to use quite little amount of UNDO space.

"SES_ADDR" "INST_ID" "SID" "SERIAL#" "USERNAME" "LOGON_TIME" "RECURSIVE" "START_TIME" "TXN_DURATION" "USED_UBLK"

Howdy, Stranger!

Log In

To view full details, sign in.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center