Tuesday, October 7, 2014

Shrink tablespace from removed data

Some times we wanted remove the data due to less space on tablespace, in that case we need to shrink the tables which we removed and it can be done like below:
Here i am removing data from a table:
SQL> delete from emp where emp_id>100;
100000 rows deleted
now enable the row movement on the particular table.
SQL> alter table emp enable row movement;
table altered
Now shrink the particular table
SQL> alter table emp shrink space;
table altered
disable the row movement
SQL> alter table emp disable row movement;
table altered
Now issue the free_space sql on tablespace and check the space.
Thats it!
Thanks!

No comments:

Post a Comment