Chinaunix首页 | 论坛 | 博客
  • 博客访问: 3620725
  • 博文数量: 738
  • 博客积分: 1860
  • 博客等级: 上尉
  • 技术积分: 8006
  • 用 户 组: 普通用户
  • 注册时间: 2008-04-07 08:51
个人简介

偶尔有空上来看看

文章分类

全部博文(738)

文章存档

2024年(24)

2023年(74)

2022年(134)

2021年(238)

2020年(115)

2019年(11)

2018年(9)

2017年(9)

2016年(17)

2015年(7)

2014年(4)

2013年(1)

2012年(11)

2011年(27)

2010年(35)

2009年(11)

2008年(11)

最近访客

分类: Oracle

2021-05-07 17:28:24


仅供参考

  1. CREATE OR REPLACE PROCEDURE show_space(p_segname_1 IN VARCHAR2,
  2.                                        p_type_1 IN VARCHAR2 DEFAULT 'TABLE',
  3.                                        p_space IN VARCHAR2 DEFAULT 'AUTO',
  4.                                        p_analyzed IN VARCHAR2 DEFAULT 'N',
  5.                                        p_partition_1 IN VARCHAR2 DEFAULT NULL,
  6.                                        p_owner_1 IN VARCHAR2 DEFAULT USER)
  7.   AUTHID CURRENT_USER AS
  8.   /*
  9.   -- grant execute on sys.DBMS_SPACE to scott;  -- 普通用户
  10.   -- exec dbms_stats.gather_table_stats('SCOTT','EMP')  -- 提前收集统计信息
  11.   -- exec show_space('EMP', 'TABLE', 'AUTO', 'Y', null, 'SCOTT')
  12.   */
  13.   p_segname VARCHAR2(100);
  14.   p_type VARCHAR2(30);
  15.   p_owner VARCHAR2(30);
  16.   p_partition VARCHAR2(50);

  17.   l_unformatted_blocks NUMBER;
  18.   l_unformatted_bytes NUMBER;
  19.   l_fs1_blocks NUMBER;
  20.   l_fs1_bytes NUMBER;
  21.   l_fs2_blocks NUMBER;
  22.   l_fs2_bytes NUMBER;
  23.   l_fs3_blocks NUMBER;
  24.   l_fs3_bytes NUMBER;
  25.   l_fs4_blocks NUMBER;
  26.   l_fs4_bytes NUMBER;
  27.   l_full_blocks NUMBER;
  28.   l_full_bytes NUMBER;
  29.   l_free_blks NUMBER;
  30.   l_total_blocks NUMBER;
  31.   l_total_bytes NUMBER;
  32.   l_unused_blocks NUMBER;
  33.   l_unused_bytes NUMBER;
  34.   l_LastUsedExtFileId NUMBER;
  35.   l_LastUsedExtBlockId NUMBER;
  36.   l_LAST_USED_BLOCK NUMBER;

  37.   PROCEDURE p(p_label IN VARCHAR2, p_num IN NUMBER) IS
  38.   BEGIN
  39.     DBMS_OUTPUT.put_line(RPAD(p_label, 40, '.') || p_num);
  40.   END;
  41. BEGIN
  42.   p_segname := UPPER(p_segname_1);
  43.   p_owner := UPPER(p_owner_1);
  44.   p_type := p_type_1;
  45.   p_partition := UPPER(p_partition_1);

  46.   IF (p_type_1 = 'i' OR p_type_1 = 'I') THEN
  47.     p_type := 'INDEX';
  48.   END IF;

  49.   IF (p_type_1 = 't' OR p_type_1 = 'T') THEN
  50.     p_type := 'TABLE';
  51.   END IF;

  52.   IF (p_type_1 = 'tp' OR p_type_1 = 'TP') THEN
  53.     p_type := 'TABLE PARTITION';
  54.   END IF;

  55.   IF (p_type_1 = 'ip' OR p_type_1 = 'IP') THEN
  56.     p_type := 'INDEX PARTITION';
  57.   END IF;

  58.   IF (p_type_1 = 'c' OR p_type_1 = 'C') THEN
  59.     p_type := 'CLUSTER';
  60.   END IF;

  61.   DBMS_SPACE.UNUSED_SPACE(segment_owner => p_owner,
  62.                           segment_name => p_segname,
  63.                           segment_type => p_type,
  64.                           partition_name => p_partition,
  65.                           total_blocks => l_total_blocks,
  66.                           total_bytes => l_total_bytes,
  67.                           unused_blocks => l_unused_blocks,
  68.                           unused_bytes => l_unused_bytes,
  69.                           LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
  70.                           LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
  71.                           LAST_USED_BLOCK => l_LAST_USED_BLOCK);

  72.   IF p_space = 'MANUAL' OR (p_space <> 'auto' AND p_space <> 'AUTO') THEN
  73.     DBMS_SPACE.FREE_BLOCKS(segment_owner => p_owner,
  74.                            segment_name => p_segname,
  75.                            segment_type => p_type,
  76.                            partition_name => p_partition,
  77.                            freelist_group_id => 0,
  78.                            free_blks => l_free_blks);
  79.   
  80.     p('Free Blocks', l_free_blks);
  81.   END IF;

  82.   p('Total Blocks', l_total_blocks);
  83.   p('Total Bytes(MB)', round(l_total_bytes/1024/1024,1));
  84.   p('Unused Blocks', l_unused_blocks);
  85.   p('Unused Bytes(MB)', round(l_unused_bytes/1024/1024,1));
  86.   p('Last Used Ext FileId', l_LastUsedExtFileId);
  87.   p('Last Used Ext BlockId', l_LastUsedExtBlockId);
  88.   p('Last Used Block', l_LAST_USED_BLOCK);

  89.   /*IF the segment is analyzed */
  90.   IF p_analyzed = 'Y' THEN
  91.     DBMS_SPACE.SPACE_USAGE(segment_owner => p_owner,
  92.                            segment_name => p_segname,
  93.                            segment_type => p_type,
  94.                            partition_name => p_partition,
  95.                            unformatted_blocks => l_unformatted_blocks,
  96.                            unformatted_bytes => l_unformatted_bytes,
  97.                            fs1_blocks => l_fs1_blocks,
  98.                            fs1_bytes => l_fs1_bytes,
  99.                            fs2_blocks => l_fs2_blocks,
  100.                            fs2_bytes => l_fs2_bytes,
  101.                            fs3_blocks => l_fs3_blocks,
  102.                            fs3_bytes => l_fs3_bytes,
  103.                            fs4_blocks => l_fs4_blocks,
  104.                            fs4_bytes => l_fs4_bytes,
  105.                            full_blocks => l_full_blocks,
  106.                            full_bytes => l_full_bytes);
  107.     DBMS_OUTPUT.put_line(RPAD('', 50, '*'));
  108.     DBMS_OUTPUT.put_line('The segment is analyzed');
  109.     p('0% -- 25% free space blocks', l_fs1_blocks);
  110.     p('0% -- 25% free space bytes(MB)', round(l_fs1_bytes/1024/1024,1));
  111.     p('25% -- 50% free space blocks', l_fs2_blocks);
  112.     p('25% -- 50% free space bytes(MB)', round(l_fs2_bytes/1024/1024,1));
  113.     p('50% -- 75% free space blocks', l_fs3_blocks);
  114.     p('50% -- 75% free space bytes(MB)', round(l_fs3_bytes/1024/1024,1));
  115.     p('75% -- 100% free space blocks', l_fs4_blocks);
  116.     p('75% -- 100% free space bytes(MB)', round(l_fs4_bytes/1024/1024,1));
  117.     p('Unused Blocks', l_unformatted_blocks);
  118.     p('Unused Bytes(MB)', round(l_unformatted_bytes/1024/1024,1));
  119.     p('Total Blocks', l_full_blocks);
  120.     p('Total bytes(MB)', round(l_full_bytes/1024/1024,1));
  121.   END IF;
  122. END;
  123. /


效果图


效果图2

阅读(872) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~