Advertisement
tmmdv

Tracking progress of a SHRINK SPACE

Jan 23rd, 2015
377
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 0.75 KB | None | 0 0
  1. SELECT
  2.   round((extent_cnt - extent#) * 100 / extent_cnt, 2) percent_completed
  3. FROM
  4.   (
  5.     SELECT
  6.       extent_id,
  7.       file_id,
  8.       block_id,
  9.       blocks,
  10.       COUNT(*) OVER () extent_cnt,
  11.       ROW_NUMBER() OVER (ORDER BY extent_id) extent#
  12.     FROM
  13.       dba_extents
  14.     WHERE
  15.       (owner, segment_name) =
  16.         (SELECT
  17.           o.owner, o.object_name
  18.          FROM
  19.            dba_objects o, v$session s
  20.          WHERE
  21.            s.sid = &sid AND
  22.            s.row_wait_obj# = o.object_id AND
  23.            o.object_type = 'TABLE')
  24.   ) e,
  25.   v$session_wait w
  26. WHERE
  27.   w.sid = &sid AND
  28.   w.event = 'db file sequential read' AND
  29.   w.state = 'WAITING' AND
  30.   w.p1 = e.file_id AND
  31.   w.p2 BETWEEN e.block_id AND e.block_id + blocks
  32. ;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement