ITPub博客

首页 > 数据库 > Oracle > show_space

show_space

Oracle 作者:wwjfeng 时间:2015-09-04 16:09:35 0 删除 编辑
exec LXY.show_space ('R_UNITSTATUS_HISTORY',  'T', 'AUTO','Y',p_owner_1=>'MCS3L');

CREATE OR REPLACE PROCEDURE LXY.show_space (
    p_segname_1     IN VARCHAR2,                     --段名
    p_type_1        IN VARCHAR2 DEFAULT 'TABLE',     --段类型
    p_space         IN VARCHAR2 DEFAULT'MANUAL',    --segment space management type
    p_analyzed      IN VARCHAR2 DEFAULT 'N',         --是否分析过
    p_partition_1   IN VARCHAR2 DEFAULT NULL,        --分区?
    p_owner_1       IN VARCHAR2 DEFAULT USER)       --拥有者
   AUTHID CURRENT_USER
 AS
    p_segname              VARCHAR2 (100);
    p_type                 VARCHAR2 (30);
    p_owner                VARCHAR2 (30);
    p_partition            VARCHAR2 (50);
    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);
    p_owner := UPPER (p_owner_1);
    p_type := p_type_1;
    p_partition := UPPER(p_partition_1);
  
    IF (p_type_1 = 'i' OR p_type_1 ='I')
    THEN
       p_type := 'INDEX';
    END IF;
 
   IF (p_type_1 = 't' OR p_type_1 ='T')
    THEN
       p_type := 'TABLE';
    END IF;
 
   IF (p_type_1 = 'tp' OR p_type_1 ='TP')
    THEN
       p_type := 'TABLE PARTITION';
    END IF;
 
   IF (p_type_1 = 'ip' OR p_type_1 = 'IP')
    THEN
       p_type := 'INDEX PARTITION';
    END IF;
 
   IF (p_type_1 = 'c' OR p_type_1 ='C')
    THEN
       p_type := 'CLUSTER';
    END IF;
 
   DBMS_SPACE.UNUSED_SPACE (
       segment_owner               => p_owner,
       segment_name                => p_segname,
       segment_type                => p_type,
       partition_name              => p_partition,
       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,
                              partition_name      =>p_partition,
                              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 p_analyzed = 'Y'
    THEN
       DBMS_SPACE.SPACE_USAGE(segment_owner        => p_owner,
                              segment_name         => p_segname,
                              segment_type         => p_type,
                              partition_name       =>p_partition,
                              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 ('Thesegment is analyzed');
       p ('0% -- 25% free spaceblocks', l_fs1_blocks);
       p ('0% -- 25% free spacebytes', l_fs1_bytes);
       p ('25% -- 50% free spaceblocks', l_fs2_blocks);
       p ('25% -- 50% free spacebytes', l_fs2_bytes);
       p ('50% -- 75% free spaceblocks', l_fs3_blocks);
       p ('50% -- 75% free spacebytes', l_fs3_bytes);
       p ('75% -- 100% free spaceblocks', l_fs4_blocks);
       p ('75% -- 100% free spacebytes', 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;
/

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15412087/viewspace-1790884/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 查询隐含参数
下一篇: 解决lock脚本
请登录后发表评论 登录
全部评论

注册时间:2010-03-27

  • 博文量
    146
  • 访问量
    94697