SHOW_SPACE 函数

最新推荐文章于 2024-06-18 14:38:07 发布

原创最新推荐文章于 2024-06-18 14:38:07 发布 · 332 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#table #ext #file #user #c

本文介绍了一个名为SHOW_SPACE的过程，该过程用于查询指定段的存储空间使用情况，包括总块数、未使用块数等详细信息，并能根据不同参数提供额外的空间使用分析。

SHOW_SPACE 函数

CREATE OR REPLACE PROCEDURE SYS.SHOW_SPACE(p_segname_1 IN VARCHAR2,
                                       p_space     IN VARCHAR2 DEFAULT 'AUTO',
                                       p_type_1    IN VARCHAR2 DEFAULT 'TABLE',
                                       p_analyzed IN VARCHAR2 DEFAULT 'N',
                                       p_owner_1   IN VARCHAR2 DEFAULT USER) AS
p_segname VARCHAR2(100);
p_type    VARCHAR2(10);
p_owner   VARCHAR2(30);
l_unformatted_blocks NUMBER;
l_unformatted_bytes NUMBER;
l_fs1_blocks         NUMBER;
l_fs1_bytes          NUMBER;
l_fs2_blocks         NUMBER;
l_fs2_bytes          NUMBER;
l_fs3_blocks         NUMBER;
l_fs3_bytes          NUMBER;
l_fs4_blocks         NUMBER;
l_fs4_bytes          NUMBER;
l_full_blocks        NUMBER;
l_full_bytes         NUMBER;
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
p_segname := upper(p_segname_1); -- rainy changed
p_owner   := upper(p_owner_1);
p_type    := p_type_1;
IF (p_type_1 = 'i' OR p_type_1 = 'I') THEN
    --rainy changed
    p_type := 'INDEX';
END IF;
IF (p_type_1 = 't' OR p_type_1 = 'T') THEN
    --rainy changed
    p_type := 'TABLE';
END IF;
IF (p_type_1 = 'c' OR p_type_1 = 'C') THEN
    --rainy changed
    p_type := 'CLUSTER';
END IF;
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);
IF p_space = 'MANUAL' OR (p_space <> 'auto' AND p_space <> 'AUTO') THEN
    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);
    p('Free Blocks', l_free_blks);
END IF;
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);
/*IF the segment is analyzed */
IF p_analyzed = 'Y' THEN
    dbms_space.space_usage(segment_owner => p_owner, segment_name => p_segname, segment_type => p_type,
                           unformatted_blocks => l_unformatted_blocks, unformatted_bytes => l_unformatted_bytes,
                           fs1_blocks => l_fs1_blocks, fs1_bytes => l_fs1_bytes, fs2_blocks => l_fs2_blocks,
                           fs2_bytes => l_fs2_bytes, fs3_blocks => l_fs3_blocks, fs3_bytes => l_fs3_bytes,
                           fs4_blocks => l_fs4_blocks, fs4_bytes => l_fs4_bytes, full_blocks => l_full_blocks,
                           full_bytes => l_full_bytes);
    dbms_output.put_line(rpad(' ', 50, '*'));
    dbms_output.put_line('The segment is analyzed');
    p('0% -- 25% free space blocks', l_fs1_blocks);
    p('0% -- 25% free space bytes', l_fs1_bytes);
    p('25% -- 50% free space blocks', l_fs2_blocks);
    p('25% -- 50% free space bytes', l_fs2_bytes);
    p('50% -- 75% free space blocks', l_fs3_blocks);
    p('50% -- 75% free space bytes', l_fs3_bytes);
    p('75% -- 100% free space blocks', l_fs4_blocks);
    p('75% -- 100% free space bytes', l_fs4_bytes);
    p('Unused Blocks', l_unformatted_blocks);
    p('Unused Bytes', l_unformatted_bytes);
    p('Total Blocks', l_full_blocks);
    p('Total bytes', l_full_bytes);
END IF;
END;