管理表（五）--表空间回收

 

1、原始表结构信息

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删除部分记录

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、利用move 释放空间

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、利用 shrink space 释放空间

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

*

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