配置环境 - SHOW_SPACE

SHOW_SPACE例程用于打印数据库段空间利用率信息。

参数如下：

• p_SegName：段名。
• p_Owner：默认当前用户，不过也可以使用这个例程查看另外某个例程。
• p_Type：默认为TABLE，这个参数表示查看哪种类型的对象（段）。
• p_Partition：先是分区对象的空间时所用的分区名。一次只能显示一个分区的空间利用率。

CREATE OR REPLACE PROCEDURE Show_Space
(
  p_SegName   IN VARCHAR2,
  p_Owner     IN VARCHAR2 DEFAULT USER,
  p_Type      IN VARCHAR2 DEFAULT 'TABLE',
  p_Partition IN VARCHAR2 DEFAULT NULL
)
  -- This procedure uses authid current user so it can query DBA_*
  -- views using privileges from a ROLE, and so it can be installed
  -- once per database, instead of once per user who wanted to use it
    AuthID CURRENT_USER
AS
  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;
  l_Segment_Space_Mgmt VARCHAR2(255);
  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;

  -- Inline procedure to print out numbers nicely formatted
  -- with a simple label
  PROCEDURE p
  (
    p_Label IN VARCHAR2,
    p_Num  IN NUMBER
  )
  IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE(RPAD(p_Label, 40, '.') || TO_CHAR(p_Num, '999999999999'));
  END p;
BEGIN
  -- This query is executed dynamically in order to allow this procedure
  -- to be created by a user who has access to DBMS_SEGMENTS/TABLESPACES
  -- via a role as is customary.
  -- NOTE: at runtime, this invoker MUST have access to these two
  -- views!
  -- This query determines if the object is an ASSM object or not
  BEGIN
    EXECUTE IMMEDIATE
    'SELECT ts.Segment_Space_Management
       FROM DBA_Segments seg,
            DBA_Tablespaces ts
      WHERE seg.Segment_Name = :p_SegName
        AND (:p_Partition IS NULL
          OR seg.Partition_Name = :p_Partition)
        AND seg.Owner = :p_Owner
        AND seg.Tablespace_Name = ts.Tablespace_Name'
       INTO l_Segment_Space_Mgmt
      USING p_SegName, p_Partition, p_Partition, p_Owner;

  EXCEPTION
    WHEN TOO_MANY_ROWS THEN
      DBMS_OUTPUT.PUT_LINE('This must be a partitioned table, use p_Partition => ');
      RETURN;
  END;

  -- If the object is in an ASSM tablespace, we must use this API
  -- call to get space information, otherwise we use the FREE_BLOCKS
  -- API for the user-managed segments
  IF l_Segment_Space_Mgmt = 'AUTO'
  THEN
    DBMS_SPACE.SPACE_USAGE(p_Owner, p_SegName, p_Type, l_Unformatted_Blocks,
                           l_Unformatted_Bytes, l_FS1_Blocks, l_FS1_Bytes,
                           l_FS2_Blocks, l_FS2_Bytes, l_FS3_Blocks, l_FS3_Bytes,
                           l_FS4_Blocks, l_FS4_Bytes, l_Full_Blocks, l_Full_Bytes, p_Partition);

    p('Unformatted Blocks ', l_Unformatted_Blocks);
    p('FS1 Blocks(0-25)   ', l_FS1_Blocks);
    p('FS2 Blocks(25-50)  ', l_FS2_Blocks);
    p('FS3 Blocks(50-75)  ', l_FS3_Blocks);
    p('FS4 Blocks(75-100) ', l_FS4_Blocks);
    p('Full Blocks        ', l_Full_Blocks);
  ELSE
    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;

  -- and then the unused space API call to get the rest of the
  -- information
  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);

  p('Total Blocks', l_Total_Blocks);
  p('Total Bytes', l_Total_Bytes);
  p('Total MBytes', l_Total_Bytes / 1024 / 1024);
  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);
END Show_Space;

eg.下面的历程输入如下，这是一个位于自动段空间管理（Automatic Segment Space Management，ASSM）的标空间中的段：

MUZIYU@MYDB> EXEC Show_Space('BIG_TABLE');
Unformatted Blocks......................           0
FS1 Blocks(0-25)........................           0
FS2 Blocks(25-50).......................           0
FS3 Blocks(50-75).......................           0
FS4 Blocks(75-100)......................           0
Full Blocks.............................       14346
Total Blocks............................       15360
Total Bytes.............................   125829120
Total MBytes............................         120
Unused Blocks...........................         851
Unused Bytes............................     6971392
Last Used Ext FileID....................           9
Last Used Ext BlockID...................       26248
Last Used Block.........................         173

PL/SQL 过程已成功完成。

说明：

• Unformatted Blocks：为表分配的位于高水位线（High-Water mark，HWM）之下但未使用的块数。未格式化的块+未用的块=为表分配但从未用于保存ASSM对象数据的总块数。
• FS1 Blocks-FS4 Blocks：包含数据的格式化块。项名后的数字区间表示各块的“空闲度”。
• Full Blocks：已满的块数。
• Total Blocks、Total Bytes、Total MBytes：所查看的段分配的总空间量，单位分别为数据库块、字节和兆字节。
• Unused Blocks、Unused Bytes：未用空间所占的比例。
• Last Used Ext FileID：最后使用的文件的文件ID。
• Last Used Ext BlockID：最后一个区段开始处的块ID。
• Last Used Block：最后一个区段中最后一个块的块的偏移量。

注：如果对象在用户空间管理的表空间中，输入的唯一区别是报告最前面的Free Blocks项。

软件环境：Windows XP SP3+Oracle 9i R2，[ORACLE_HOME]=C:/Oracle/Ora92