show_space过程内容：
create or replace procedure show_space
( p_segname in varchar2,
p_owner in varchar2 default user,
p_type in varchar2 default 'TABLE' )
as
l_free_blks number;
l_total_blocks number;
l_total_bytes number;
l_unused_blocks number;
l_unused_bytes number;
l_LastUsedExtFileId number;
l_LastUsedExtBlockId number;
l_LAST_USED_BLOCK number;
procedure p( p_label in varchar2, p_num in number )
is
begin
dbms_output.put_line( rpad(p_label,40,'.') ||
p_num );
end;
begin
dbms_space.free_blocks
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
freelist_group_id => 0,
free_blks => l_free_blks );
dbms_space.unused_space
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
total_blocks => l_total_blocks,
total_bytes => l_total_bytes,
unused_blocks => l_unused_blocks,
unused_bytes => l_unused_bytes,
LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
LAST_USED_BLOCK => l_LAST_USED_BLOCK );
p( 'Free Blocks', l_free_blks );
p( 'Total Blocks', l_total_blocks );
p( 'Total Bytes', l_total_bytes );
p( 'Unused Blocks', l_unused_blocks );
p( 'Unused Bytes', l_unused_bytes );
p( 'Last Used Ext FileId', l_LastUsedExtFileId );
p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
p( 'Last Used Block', l_LAST_USED_BLOCK );
end;
create table bigcol(y varchar2(2048)) nologging;
执行show_space报错：
call show_space('BIGCOL','LYON')
*
第 1 行出现错误:
ORA-10618: Operation not allowed on this segment
ORA-06512: 在 "SYS.DBMS_SPACE", line 152
ORA-06512: 在 "SYS.SHOW_SPACE", line 21
ORA-06512: 在 line 1
发现这是由于表空间的ASSM方式引起的。
要能够执行该脚本，则需要在手动段空间管理模式下。
创建一个MSSM表空间：
SQL> create tablespace mantbs datafile 'E:\oracle\oradata\lyon\mantbs01.dbf' size 10m uniform. size 1m segment space management manual;
表空间已创建。
将用户在mantbs上的空间配额修改为不限制：
SQL> alter user lyon quota unlimited on mantbs;
用户已更改。
将表移动到该表空间下：
SQL> alter table bigcol move tablespace mantbs
2 /
表已更改。
SQL> call show_space('BIGCOL',user);
调用完成。
SQL> show message;
SP2-0158: 未知的 SHOW 选项 "message"
SQL> set serveroutput on;
SQL> /
Free Blocks.............................0
Total Blocks............................128
Total Bytes.............................1048576
Unused Blocks...........................127
Unused Bytes............................1040384
Last Used Ext FileId....................23
Last Used Ext BlockId...................137
Last Used Block.........................1
调用完成。
即可查看该表占用空间情况。
往bigcol表插入10000条数据：
SQL> insert into bigcol
2 select dbms_random.string('X', 2048) from dual connect by rownum <= 10000;
insert into bigcol
*
第 1 行出现错误:
ORA-01653: 表 LYON.BIGCOL 无法通过 128 (在表空间 MANTBS 中) 扩展
出现空间不够问题
修改数据文件大小，扩展表空间到100m：
SQL> alter database datafile 'E:\oracle\oradata\lyon\mantbs01.dbf' resize 100m;
数据库已更改。
SQL> insert into bigcol
2 select dbms_random.string('X', 2048) from dual connect by rownum <= 10000;
已创建10000行。
SQL> commit;
提交完成。
查看表使用空间情况：
SQL> call show_space('BIGCOL',user);
Free Blocks.............................4
Total Blocks............................3456
Total Bytes.............................28311552
Unused Blocks...........................118
Unused Bytes............................966656
Last Used Ext FileId....................23
Last Used Ext BlockId...................3337
Last Used Block.........................10
调用完成。
总计使用block 3456，未使用118，则已使用：3456-118 = 3338，即hwm为3338
用delete删除所有记录：
SQL> delete from bigcol;
已删除10000行。
SQL> commit;
提交完成。
SQL> call show_space('BIGCOL',user);
Free Blocks.............................3337
Total Blocks............................3456
Total Bytes.............................28311552
Unused Blocks...........................118
Unused Bytes............................966656
Last Used Ext FileId....................23
Last Used Ext BlockId...................3337
Last Used Block.........................10
调用完成。
发现free blocks由原来的4个变为现在的3337。但是已使用的blocks：3456 - 118 = 3338，即hwm仍为3338。
查看执行时间：
10:24:19 SQL> select count(*) from bigcol;
COUNT(*)
----------
0
已用时间: 00: 00: 00.84
截断表：
10:24:25 SQL> truncate table bigcol;
表被截断。
已用时间: 00: 00: 02.81
10:24:50 SQL> call show_space('BIGCOL',user);
Free Blocks.............................0
Total Blocks............................128
Total Bytes.............................1048576
Unused Blocks...........................127
Unused Bytes............................1040384
Last Used Ext FileId....................23
Last Used Ext BlockId...................9
Last Used Block.........................1
调用完成。
发现已使用的blocks：128 - 127 = 1，即hwm收缩为1。
再次执行count查询：
10:26:28 SQL> select count(*) from bigcol;
COUNT(*)
----------
0
已用时间: 00: 00: 00.01
在每次执行count之前都做了buffer cache的清理，已保证每次都是从disk读取的。
发现两次查询的时间发生了明显的变化，由原来的0.84s缩小为现在的0.01s。
参考了：
http://tolywang.itpub.net/post/48/307529
引用里面对此的总结：
在9I中:
(1)如果MINEXTENT 可以使ALTER TABLE TABLENAME DEALLOCATE UNUSED将HWM以上所有没使用的空间释放
(2)如果MINEXTENT >HWM 则释放MINEXTENTS 以上的空间。如果要释放HWM以上的空间则使用KEEP 0。
ALTER TABLE TABLESNAME DEALLOCATE UNUSED KEEP 0;
(3) TRUNCATE TABLE DROP STORAGE(缺省值)命令可以将MINEXTENT 之上的空间完全释放(交还给操作系统),并且重置HWM。
(4)如果仅是要移动HWM,而不想让表长时间锁住,可以用TRUNCATE TABLE REUSE STORAGE,仅将HWM重置。
(5)ALTER TABLE MOVE会将HWM移动,但在MOVE时需要双倍的表空间,而且如果表上有索引的话,需要重构索引
(6)DELETE表不会重置HWM,也不会释放自由的空间(也就是说DELETE空出来的空间只能给对象本身将来的INSERT/UPDATE使用,不能给其它的对象使用)
在ORACLE 10G:
(1)可以使用ALTER TABLE TEST_TAB SHRINK SPACE命令来联机移动HWM,
(2)如果要同时压缩表的索引,可以发布:ALTER TABLE TEST_TAB SHRINK SPACE CASCADE