ORA-10636

I tested this in an Oracle 11.2.0.1.0 database. I created a table with lots of data:

 

SQL> create table andrews_table

  2  as select * from dba_tables

  3  /

 

Table created.

 

SQL> begin

  2  for a in 1..5 loop

  3  insert into andrews_table select * from andrews_table;

  4  end loop;

  5  end;

  6  /

 

PL/SQL procedure successfully completed.

 

SQL>

 

I deleted 30% of the rows. This left the table with some empty space:

 

SQL> select count(*) from andrews_table

  2  /

 

  COUNT(*)

----------

     99328

 

SQL> delete andrews_table where owner = 'SYS'

  2  /

 

30624 rows deleted.

 

SQL>

 

I checked the amount of space occupied by the table:

 

SQL> select bytes from dba_segments

  2  where segment_name = 'ANDREWS_TABLE'

  3  /

 

     BYTES

----------

  29360128

 

SQL>

 

I tried to recoup the empty space but this failed as row movement was not enabled:

 

SQL> alter table andrews_table shrink space

  2  /

alter table andrews_table shrink space

*

ERROR at line 1:

ORA-10636: ROW MOVEMENT is not enabled

 

SQL>

 

I enabled row movement…

 

SQL> alter table andrews_table enable row movement

  2  /

 

Table altered.

 

SQL>

 

…then I was able to reclaim the empty space…

 

SQL> alter table andrews_table shrink space

  2  /

 

Table altered.

 

SQL>

 

…and the table had gone down from 29M to 20M:

 

SQL> select bytes from dba_segments

  2  where segment_name = 'ANDREWS_TABLE'

  3  /

 

     BYTES

----------

  20185088

 

SQL>