7 Ocak 2015 Çarşamba

shrinking tables to gain space in database (Oracle Sql Developer)

When you delete rows, the rows may not be physically deleted and you may not get the used space back.  The case is explained in the post: http://laurenthinoul.com/how-to-fix-ora-01654-unable-to-extend-index-in-tablespace/

You can run the following sql to compute statistics :

analyze table   compute statistics;


You can view the statistics as in the picture:



try this sql to shrink:

alter table  enable row movement;
alter table  shrink space;
alter table  disable row movement;

analyze table  compute statistics;
commit;


however, this may give the following error due to the reasons here: http://agstamy.blogspot.it/2009/02/ora-10631-when-trying-to-shrink-table.html

SQL Error: ORA-10631: SHRINK clause should not be specified for this object
 "SHRINK clause should not be specified for this object"
*Cause:    It is incorrect to issue shrink on the object
*Action:   Verify the object name and type and reissue the command

in this case, you may try by removing the indexes, shrinking the table and recreating the indexes.

btw, truncating the table gives you the space back!