1. UNUSED_SPACE(返回对象table, index, or cluster未使用的空间)
语法:
DBMS_SPACE.UNUSED_SPACE ( segment_owner IN VARCHAR2, --对象的schema segment_name IN VARCHAR2, --对象的名称 segment_type IN VARCHAR2, --对象的类型(table,table_partition,index等) total_blocks OUT NUMBER, --总数据块数量 total_bytes OUT NUMBER, --总字节数量 unused_blocks OUT NUMBER, --未使用数据块数量 unused_bytes OUT NUMBER, --未使用字节数量 last_used_extent_file_id OUT NUMBER, --包含数据的最后区file id last_used_extent_block_id OUT NUMBER, --包含数据的最后区block id last_used_block OUT NUMBER, --最后使用的数据块 partition_name IN VARCHAR2 DEFAULT NULL);--分区名
用法举例:
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(segment_owner => 'ORACLE',segment_name => 'T1',segment_type => 'TABLE',
total_blocks => total_blocks,total_bytes => total_bytes,unused_blocks => unused_blocks,
unused_bytes => unused_bytes,last_used_extent_file_id => last_used_extent_file_id,
last_used_extent_block_id =>last_used_extent_block_id,
last_used_block => last_used_block,
partition_name => NULL );
dbms_output.put_line('total_blocks: '||total_blocks);
dbms_output.put_line('total_bytes: '||total_bytes);
dbms_output.put_line('unused_blocks: '||unused_blocks);
dbms_output.put_line('unused_bytes: '||unused_bytes);
dbms_output.put_line('last_used_extent_file_id: '||last_used_extent_file_id);
dbms_output.put_line('last_used_extent_block_id: '||last_used_extent_block_id);
dbms_output.put_line('last_used_block: '||last_used_block);
END;
2.SPACE_USAGE(返回表的数据块的使用情况,前提:表所属表空间是自动管理)
语法:
DBMS_SPACE.SPACE_USAGE( segment_owner IN VARCHAR2, ---对象schema segment_name IN VARCHAR2, ---对象名称 segment_type IN VARCHAR2, --对象类型,同上 unformatted_blocks OUT NUMBER, --没有格式化的块 unformatted_bytes OUT NUMBER, ---没有格式化的字节 fs1_blocks OUT NUMBER, --0-25%空闲空间的数据块数量 fs1_bytes OUT NUMBER, fs2_blocks OUT NUMBER, --25-50% fs2_bytes OUT NUMBER, fs3_blocks OUT NUMBER, --50%-75% fs3_bytes OUT NUMBER, fs4_blocks OUT NUMBER, --75%-100% fs4_bytes OUT NUMBER, full_blocks OUT NUMBER, --完全使用数据块的数量 full_bytes OUT NUMBER, partition_name IN VARCHAR2 DEFAULT NULL);
用法:
DECLARE unformatted_blocks NUMBER; unformatted_bytes NUMBER; fs1_blocks NUMBER; fs1_bytes NUMBER; fs2_blocks NUMBER; fs2_bytes NUMBER; fs3_blocks NUMBER; fs3_bytes NUMBER; fs4_blocks NUMBER; fs4_bytes NUMBER; full_blocks NUMBER; full_bytes NUMBER; BEGIN dbms_space.space_usage(segment_owner => 'ORACLE', segment_name => 'TEST1', segment_type => 'TABLE', unformatted_blocks => unformatted_blocks, unformatted_bytes => unformatted_bytes, fs1_blocks => fs1_blocks, fs1_bytes => fs1_bytes, fs2_blocks => fs2_blocks, fs2_bytes => fs2_bytes, fs3_blocks => fs3_blocks, fs3_bytes => fs3_bytes, fs4_blocks => fs4_blocks, fs4_bytes => fs4_bytes, full_blocks => full_blocks, full_bytes => full_bytes); dbms_output.put_line('unformatted_blocks: '||unformatted_blocks); dbms_output.put_line('unformatted_bytes: '||unformatted_bytes); dbms_output.put_line('0%-25% block: '||fs1_blocks); dbms_output.put_line('0%-25% bytes: '||fs1_blocks); dbms_output.put_line('25%-50% block: '||fs2_blocks); dbms_output.put_line('25%-50% bytes: '||fs2_blocks); dbms_output.put_line('50%-75% block: '||fs3_blocks); dbms_output.put_line('50%-75% bytes: '||fs3_blocks); dbms_output.put_line('75%-100% blocks: '||fs4_blocks); dbms_output.put_line('75%-100% bytes: '||fs4_blocks); dbms_output.put_line('full_blocks: '||full_blocks); dbms_output.put_line('full_bytes: '||full_bytes); END;