一:含义
This procedure shows the space usage of data blocks under the segment High Water Mark. The bitmap blocks, segment header, and extent map blocks are not accounted for by this procedure. This procedure can only be used on tablespaces that are created with auto segment space management.
语法:
DBMS_SPACE.SPACE_USAGE(
segment_owner IN VARCHAR2,
segment_name IN VARCHAR2,
segment_type IN VARCHAR2,
unformatted_blocks OUTNUMBER,
unformatted_bytes OUTNUMBER,
fs1_blocks OUTNUMBER,
fs1_bytes OUTNUMBER,
fs2_blocks OUTNUMBER,
fs2_bytes OUTNUMBER,
fs3_blocks OUTNUMBER,
fs3_bytes OUTNUMBER,
fs4_blocks OUTNUMBER,
fs4_bytes OUTNUMBER,
full_blocks OUTNUMBER,
full_bytes OUTNUMBER,
partition_name IN VARCHAR2 DEFAULT NULL);
或者:
DBMS_SPACE.SPACE_USAGE(
segment_owner IN VARCHAR2,
segment_name IN VARCHAR2,
segment_type IN VARCHAR2,
segment_size_blocks OUT NUMBER,
segment_size_bytes OUT NUMBER,
used_blocks OUT NUMBER,
used_bytes OUT NUMBER,
expired_blocks OUT NUMBER,
expired_bytes OUT NUMBER,
unexpired_blocks OUT NUMBER,
unexpired_bytes OUT NUMBER,
partition_name IN VARCHAR2 DEFAULT NULL);
参数说明:
Parameter | Description |
segment_owner | Schema name of the segment to be analyzed |
segment_name | Name of the segment to be analyzed |
partition_name | Partition name of the segment to be analyzed |
segment_type | Type of the segment to be analyzed (TABLE, INDEX, or CLUSTER): TABLE TABLE PARTITION TABLE SUBPARTITION INDEX INDEX PARTITION INDEX SUBPARTITION CLUSTER LOB LOB PARTITION LOB SUBPARTITION |
unformatted_blocks | Total number of blocks unformatted |
unformatted bytes | Total number of bytes unformatted |
fs1_blocks | Number of blocks having at least 0 to 25% free space |
fs1_bytes | Number of bytes having at least 0 to 25% free space |
fs2_blocks | Number of blocks having at least 25 to 50% free space |
fs2_bytes | Number of bytes having at least 25 to 50% free space |
fs3_blocks | Number of blocks having at least 50 to 75% free space |
fs3_bytes | Number of bytes having at least 50 to 75% free space |
fs4_blocks | Number of blocks having at least 75 to 100% free space |
fs4_bytes | Number of bytes having at least 75 to 100% free space |
ful1_blocks | Total number of blocks full in the segment |
full_bytes | Total number of bytes full in the segment |
segment_size_blocks | Number of blocks allocated to the segment |
segment_size_bytes | Number of bytes allocated to the segment |
used_blocks | Number blocks allocated to the LOB that contains active data |
used_bytes | Number bytes allocated to the LOB that contains active data |
expired_blocks | Number of expired blocks used by the LOB to keep version data |
expired_bytes | Number of expired bytes used by the LOB to keep version data |
unexpired_blocks | Number of unexpired blocks used by the LOB to keep version data |
unexpired_bytes | Number of unexpired bytes used by the LOB to keep version data |
partition_name | Name of the partition (NULL if not a partition) |
二:示例
SQL> delete from t2;
6 rows deleted.
SQL> commit;
Commit complete.
SQL> select blocks from user_tables where table_name='T2';
BLOCKS
----------
5
--该表共包含5个数据块
SQL> select count(distinct(dbms_rowid.rowid_block_number(rowid))) cnt from t2;
CNT
----------
0
--包含数据的数据块的个数为0,因为已经被delete掉了。
[oracle@target_pc ~]$ cat space_usage.sql
DECLARE
l_tabname varchar(30):='&1';
l_fs1_bytes number;
l_fs2_bytes number;
l_fs3_bytes number;
l_fs4_bytes number;
l_fs1_blocks number;
l_fs2_blocks number;
l_fs3_blocks number;
l_fs4_blocks number;
l_full_bytes number;
l_full_blocks number;
l_unformatted_bytes number;
l_unformatted_blocks number;
BEGIN
dbms_space.space_usage(
segment_owner => user,
segment_name => l_tabname,
segment_type => 'TABLE',
fs1_bytes => l_fs1_bytes,
fs1_blocks => l_fs1_blocks,
fs2_bytes => l_fs2_bytes,
fs2_blocks => l_fs2_blocks,
fs3_bytes => l_fs3_bytes,
fs3_blocks => l_fs3_blocks,
fs4_bytes => l_fs4_bytes,
fs4_blocks => l_fs4_blocks,
full_bytes => l_full_bytes,
full_blocks => l_full_blocks,
unformatted_blocks => l_unformatted_blocks,
unformatted_bytes => l_unformatted_bytes
);
dbms_output.put_line('0-25% FREE = '||l_fs1_blocks||' and Bytes = '||l_fs1_bytes);
dbms_output.put_line('25-50% FREE = '||l_fs2_blocks||' and Bytes = '||l_fs2_bytes);
dbms_output.put_line('50-75% FREE = '||l_fs3_blocks||' and Bytes = '||l_fs3_bytes);
dbms_output.put_line('75-100% FREE = '||l_fs4_blocks||' and Bytes = '||l_fs4_bytes);
dbms_output.put_line(' Full Blocks = '||l_full_blocks||' and Bytes = '||l_full_bytes);
END;
SQL> set serveroutput on;
SQL> @space_usage T2
41 /
old 2: l_tabname varchar(30):='&1';
new 2: l_tabname varchar(30):='T2';
0-25% FREE = 0 and Bytes = 0
25-50% FREE = 0 and Bytes = 0
50-75% FREE = 0 and Bytes = 0
75-100% FREE = 5 and Bytes = 40960
Full Blocks = 0 and Bytes = 0
PL/SQL procedure successfully completed.
--说明空闲空间在75-100%的数据块有5个,其余都为0个。
--综上说明,数据虽然被delete,但是高水位下的数据块并没有被释放。
--本篇文章参考自:
Oracle Show_space过程使用示例与注释,Oracle DBMS_SPACE,Oracle Sql高级编程