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

CUUG

更多oracle视频教程请点击：http://crm2.qq.com/page/portalpage/wpa.php?uin=800060152&f=1&ty=1&aty=0&a=&from=6