Category Archives: Oracle

In this we are handling Oracle Database Administration and development task. If provide solution of ORA Errors and configuration Steps for setup in Oracle.

Check progress and status of alter Shrink Space command

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;

Alter table scott.EMPLOYEE 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;

Advertisements