How to reclaim table space in Oracle

1. Original table structure information

06:49:50 sql> Analyze table EMP1 compute statistics;

Table analyzed.

06:50:00 sql> Select Table_name,blocks,empty_blocks,num_rows from User_tables

06:50:18 2 where table_name= ' EMP1 ';

TABLE_NAME BLOCKS empty_blocks Num_rows

---------- ---------- ------------ ----------

EMP1 91 5 14336

2, delete deletes some records

06:50:48 sql> DELETE from Emp1 where deptno=30;

6144 rows deleted.

06:50:53 sql> Select COUNT (*) from EMP1;

COUNT (*)

----------

8192

06:51:03 sql> Analyze table EMP1 compute statistics;

Table analyzed.

06:51:08 sql> Select Table_name,blocks,empty_blocks,num_rows from User_tables

06:51:11 2 where table_name= ' EMP1 ';

TABLE_NAME BLOCKS empty_blocks Num_rows

---------- ---------- ------------ ----------

EMP1 91 5 8192

3. Use Move to free space

06:51:49 sql> ALTER TABLE EMP1 move;

Table altered.

06:51:53 sql> Analyze table EMP1 compute statistics;

Table analyzed.

06:51:57 sql> Select Table_name,blocks,empty_blocks,num_rows from User_tables

06:52:01 2 where table_name= ' EMP1 ';

TABLE_NAME BLOCKS empty_blocks Num_rows

---------- ---------- ------------ ----------

EMP1 53 3 8192

4. Free space with shrink

06:52:03 sql> DELETE from Emp1 where deptno=10;

4096 rows deleted.

06:52:28 sql> Select COUNT (*) from EMP1;

COUNT (*)

----------

4096

06:52:33 sql> Analyze table EMP1 compute statistics;

Table analyzed.

06:52:36 sql> Select Table_name,blocks,empty_blocks,num_rows from User_tables

06:52:39 2 where table_name= ' EMP1 ';

TABLE_NAME BLOCKS empty_blocks Num_rows

---------- ---------- ------------ ----------

EMP1 53 3 4096

06:53:36 sql> ALTER TABLE EMP1 shrink space;

ALTER TABLE EMP1 shrink space

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

*

ERROR at line 1:

Ora-10636:row MOVEMENT is not enabled

06:53:56 sql> ALTER TABLE EMP1 enable row movement;

Table altered.

06:54:15 sql> ALTER TABLE EMP1 shrink space;

Table altered.

06:54:42 sql> Select Table_name,blocks,empty_blocks,num_rows from User_tables

06:54:46 2 where table_name= ' EMP1 ';

TABLE_NAME BLOCKS empty_blocks Num_rows

---------- ---------- ------------ ----------

EMP1 53 3 4096

06:54:48 sql> Analyze table EMP1 COMPUTE statistics

06:54:57 2;

Table analyzed.

06:54:58 sql> Select Table_name,blocks,empty_blocks,num_rows from User_tables

06:55:01 2 where table_name= ' EMP1 ';

TABLE_NAME BLOCKS empty_blocks Num_rows

---------- ---------- ------------ ----------

EMP1 22 10 4096

Cuug