alter table … shrink space – and its progress

well, there are a lot of good tutorials regarding when and how to use the “alter table … shrink space” command – and don’t panic, I won’t do another one 😉

just a few words to mechanism behind: the shrink is done in 2 steps

1. the rows from the “end” of a table will be moved to the “beginning”. Internally, this is done by “select – insert – delete” statements, row by row. So there are only locks on row level and a lot of small transactions. Therefore, this part is completely online and has just a small performance impact on running transactions (I’ve seen somewhat about 2% and 10%, depending on your application)
2. The “high water mark” will be reset to the latest block of the table. Oracle has to lock the table exclusively – so it’s not really online.

and a short summary about the different “shrink space” commands (because I always forget them myself)

• alter table … shrinc space:
  • do both steps successive
• alter table … shrink space cascade:
  • do both steps successive for the table and all related objects like indexes and lobs
  • if you want to see all related objects, just execute this:

    select * from (table(DBMS_SPACE.OBJECT_DEPENDENT_SEGMENTS('<OWNER>','<TABLE>',null, 1)));
    

• alter table … shrink space compact:
  • do only step 1
• alter table … modify lob (…) (shrink space):
  • do step 1 & 2 for lob column

And now: the tricky part… how to minitor progress… and just the short answer at the beginning: it’s not possible. If you think of using v$session_longops – as I mentioned above, there many short transactions…

but there are some other indications you can use to check progress, although you can’t calculate the end time properly… nevertheless it’s enough to see the process is ongoing 😉

select
   b.sid
  ,a.event
  ,a.WAIT_TIME
  ,round(d.read_value/1024/1024/1024,2) read_gb
  ,round(d.write_value/1024/1024/1024,2) write_gb
  ,round(d.undo_value/1024/1024/1024,2) undo_gb
  ,e.current_undo
  ,c.SQL_TEXT
from v$session_wait a
join v$session b on a.sid = b.sid
join v$sqlarea c on b.SQL_ID = c.SQL_ID
join (select * from (select s.sid, t.name, s.value from v$sesstat s inner join v$statname t on s.statistic#=t.statistic#)
              pivot (sum(value) as value for name in (
                 'undo change vector size' UNDO
                ,'physical read total bytes' READ
                ,'physical write total bytes' WRITE
))) d on a.sid=d.sid
join (select b.sid, sum(a.USED_UBLK) current_undo from V$TRANSACTION a join v$session b on a.SES_ADDR=b.saddr group by b.sid) e on e.sid=a.sid
where upper(c.sql_text) like 'ALTER TABLE%SHRINK SPACE%'
and b.sid != (select sys_context('USERENV','SID') from dual)
;

Just to explain the output:

• SID: the user SID executing the shrink command
• EVENT: the current wait event… until its “direct path read” or “db file … read” you are in step 1
• WAIT_TIME: should be nearly 0, since we have just small transactions.
• READ_GB: amount of data the session has read vom disk
• WRITE_GB: amount of data the session has written to disk
• UNDO_GB: amount of data the session has written to undo
• CURRENT_UNDO: number of undo blocks currently used… should be very low (<20) – see “small transactions”
• SQL_TEXT: the SQL text we are monitoring…