Check progress and status of alter Shrink Space command in Oracle

1. Check the Size of Segment on which its running:

select sum(bytes)/1024/1024/1024 from dba_segments where segment_name = ‘EMPLOYEE’;

2. Enable the row movement and shrink command:

-- Enable row movemement command
ALTER TABLE schema_name.table_name ENABLE ROW MOVEMENT;

--Enable the Shrink command
--With cascade option cover all indexes if you use table.
-- Recover space for the object and all dependent objects.
ALTER TABLE scott.emp SHRINK SPACE CASCADE;


3. Check the progress of Alter Shrink Space Command

-- Following command will search alter table command and give you output how much it covered according to table size in first command.
-- GB_read give you how much it covered yet.
select a.event, a.WAIT_TIME, c.SQL_TEXT,
c.PHYSICAL_READ_BYTES / 1024 / 1024 / 1024 "GB_READ",
c.PHYSICAL_WRITE_BYTES / 1024 / 1024 / 1024 "GB_WRITE"
from v$session_wait a , v$session b , v$sql c
where UPPER(c.SQL_TEXT) like UPPER('%ALTER TABLE%')
and a.sid = b.sid
and b.SQL_ID = c.SQL_ID;

--If you have session id of session from which command running then use following command:
select a.event, a.WAIT_TIME, c.SQL_TEXT,
c.PHYSICAL_READ_BYTES / 1024 / 1024 / 1024 "GB_READ",
c.PHYSICAL_WRITE_BYTES / 1024 / 1024 / 1024 "GB_WRITE"
from v$session_wait a , v$session b , v$sql c
where a.SID =
and a.sid = b.sid
and b.SQL_ID = c.SQL_ID;