Saturday, March 15, 2014

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>

No comments: