1. unused_space

    该过程用于返回对象（表、索引等）的未用空间。举例如下：

编辑space.sql，内容如下：

declare
total_blocks number;
total_bytes number;
unused_blocks number;
unused_bytes number;
last_used_extent_file_id number;
last_used_extent_block_id number;
last_used_block number;
begin
dbms_space.unused_space('SCOTT','TEST1','TABLE',total_blocks,total_bytes,unused_blocks,unused_bytes,last_used_extent_file_id,
last_used_extent_block_id,last_used_block);
dbms_output.put_line('Total blocks is '||to_char(total_blocks));
dbms_output.put_line('Total bytes is '||to_char(total_bytes));
dbms_output.put_line('Total unused blocks is '||to_char(unused_blocks));
dbms_output.put_line('Total unused bytes is '||to_char(unused_bytes));
dbms_output.put_line('HWM= '||to_char(total_blocks-unused_blocks-1));
end;
/

登录数据库，执行此脚本：

SQL> @space.sql
Total blocks is 40936
Total bytes is 335347712
Total unused blocks is 0
Total unused bytes is 0
HWM= 40935
 
PL/SQL procedure successfully completed.
 
SQL> delete from test1 where rownum<300000;
 
299999 rows deleted.
 
SQL> commit;
 
Commit complete.
 
SQL> delete from test1 where rownum<300000;
 
299999 rows deleted.
 
SQL> commit;
 
Commit complete.
 
SQL> set timing on
SQL> alter table test1 move nologging parallel;
 
Table altered.
 
Elapsed: 00:00:51.45
SQL> @space.sql
Total blocks is 34416
Total bytes is 281935872
Total unused blocks is 0
Total unused bytes is 0
HWM= 34415
 
PL/SQL procedure successfully completed.

可见HWM已经降低，另外，注意到Total unused blocks 为0，这说明表重组后，已经没有空闲的块，此时，再执行insert操作。

SQL>  insert into test1 select * from test1 where rownum<100000;
 
99999 rows created.
 
Elapsed: 00:00:10.31
SQL> commit;
 
Commit complete.
 
Elapsed: 00:00:00.00
SQL> @space.sql
Total blocks is 36464
Total bytes is 298713088
Total unused blocks is 989
Total unused bytes is 8101888
HWM= 35474
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.00

再次执行数据插入后，出现了unsed block，但为什么上一次的unused block 为0呢

2. free_blocks

该过程用于返回对象的空闲块信息，举例如下：

SQL> declare
free_blocks number;
begin
dbms_space.free_blocks('SCOTT','TEST1','TABLE',0,free_blocks);
dbms_output.put_line('Free list count is '||free_blocks);
end;
/  2    3    4    5    6    7 
Free list count is 0
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.02

此时，该段上的空闲块为0，也就是说所有的块都得到了使用。接下来，执行delete操作，删除一部分记录。

可见删除部分记录后，freelist列表里的空闲块增加到3261个。