###测试dbms_space.space_usage查看不同segment的空间使用情况,详细用法请查看官方手册
SQL> r
1 declare
2 unf number;
3 unfb number;
4 fs1 number;
5 fs1b number;
6 fs2 number;
7 fs2b number;
8 fs3 number;
9 fs3b number;
10 fs4 number;
11 fs4b number;
12 full number;
13 fullb number;
14 begin
15 dbms_space.space_usage('SCOTT','T_ONLY','TABLE',unf,unfb,fs1,fs1b,fs2,fs2b,fs3,fs3b,fs4,fs4b,full,fullb);
16 dbms_output.put_line('unformatted_blocks '||unf);
17 dbms_output.put_line('unformatted_bytes '||unfb);
18 dbms_output.put_line('Number of blocks that has at least 0 to 25% free space '||fs1);
19 dbms_output.put_line('Number of bytes that has at least 0 to 25% free space '||fs1b);
20 dbms_output.put_line(fs2||' '||fs3||' '||fs4||' '||full);
21* end;
unformatted_blocks 0
unformatted_bytes 0
Number of blocks that has at least 0 to 25% free space 0
Number of bytes that has at least 0 to 25% free space 0
0 0 0 11889
##这里的fs1是指具备0-25%以内的空闲空间未使用的块个数,其它fs同理,full是具备块完全使用的块个数
###下列输入列为dbms_space.unused_space包
total_blocks
Returns total number of blocks in the segment. ##segment的块数
total_bytes
Returns total number of blocks in the segment, in bytes.##segment的bytes数
unused_blocks
Returns number of blocks which are not used. ##未使用的block数
unused_bytes
Returns, in bytes, number of blocks which are not used.##对应上列
last_used_extent_ file_id
Returns the file ID of the last extent which contains data.##包括数据的最后一个extent所对应的file_id
last_used_extent_ block_id
Returns the starting block ID of the last extent which contains data.###包括数据的最后一个extent的开始block id
last_used_block
Returns the last block within this extent which contains data.###包括数据的extent的最后一个block
partition_name
Partition name of the segment to be analyzed.
This is only used for partitioned tables; the name of subpartition should be used when partitioning is compose.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9240380/viewspace-672047/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-672047/