sqlplus之 show_space

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

接口如下:

sys@ORCL>desc show_space;
PROCEDURE show_space
参数名称                       类型                    输入/输出默认值?
------------------------------ ----------------------- ------ --------
 P_SEGNAME                      VARCHAR2                IN
 P_OWNER                        VARCHAR2                IN     DEFAULT
 P_TYPE                         VARCHAR2                IN     DEFAULT
 P_PARTITION                    VARCHAR2                IN     DEFAULT

P_SEGNAME    段名(例如，表或者索引名)

P_OWNER    默认为当前用户，不过也可以使用这个例程查看另外某个模式

P_TYPE    默认为TABLE，表示查看哪种类型的对象(段).如下是合法的段类型：

sys@ORCL>select distinct segment_type from dba_segments;

SEGMENT_TYPE
------------------
LOBINDEX
INDEX PARTITION
NESTED TABLE
TABLE PARTITION
ROLLBACK
LOB PARTITION
LOBSEGMENT
TABLE
INDEX
CLUSTER
TYPE2 UNDO

已选择11行。

P_PARTITION    显示分区对象的空间时所用的分区名。SHOW_PACE一次只显示一个分区的空间利用率。

----------------------------------------------------------------------------------------------------------------------------------

1.–建立SHOW_PACE

sys@ORCL>CREATE OR REPLACE PROCEDURE show_space(p_segname   IN VARCHAR2,
  2                                         p_owner     IN VARCHAR2 DEFAULT USER,
  3                                         p_type      IN VARCHAR2 DEFAULT 'TABLE',
  4                                         p_partition IN VARCHAR2 DEFAULT NULL ) AS
  5    l_free_blks          NUMBER;
  6    l_total_blocks       NUMBER;
  7    l_total_bytes        NUMBER;
  8    l_unused_blocks      NUMBER;
  9    l_unused_bytes       NUMBER;
 10    l_lastusedextfileid  NUMBER;
 11    l_lastusedextblockid NUMBER;
 12    l_last_used_block    NUMBER;
 13    l_segment_space_mgmt VARCHAR2(255);
 14    l_unformatted_blocks NUMBER;
 15    l_unformatted_bytes  NUMBER;
 16    l_fs1_blocks         NUMBER;
 17    l_fs1_bytes          NUMBER;
 18    l_fs2_blocks         NUMBER;
 19    l_fs2_bytes          NUMBER;
 20    l_fs3_blocks         NUMBER;
 21    l_fs3_bytes          NUMBER;
 22    l_fs4_blocks         NUMBER;
 23    l_fs4_bytes          NUMBER;
 24    l_full_blocks        NUMBER;
 25    l_full_bytes         NUMBER;
 26
 27    PROCEDURE p(p_label IN VARCHAR2,
 28                p_num   IN NUMBER) IS
 29    BEGIN
 30      dbms_output.put_line(rpad(p_label, 40, '.') ||
 31                           to_char(p_num, '999,999,999,999'));
 32    END;
 33  BEGIN
 34    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'
 35      INTO l_segment_space_mgmt
 36      USING p_segname, p_partition, p_partition, p_owner;
 37
 38    --     exception
 39    --             when too_many_rows
 40    --             then
 41    --             dbms_output.put_line('This must be a partitioned table,use p_partition => ');
 42    --             return;
 43    --     end;
 44

 45    IF l_segment_space_mgmt = 'AUTO' THEN
 46      dbms_space.space_usage(p_owner, p_segname, p_type, l_unformatted_blocks,
 47                             l_unformatted_bytes, l_fs1_blocks, l_fs1_bytes,
 48                             l_fs2_blocks, l_fs2_bytes, l_fs3_blocks, l_fs3_bytes,
 49                             l_fs4_blocks, l_fs4_bytes, l_full_blocks,
 50                             l_full_bytes, p_partition);
 51
 52      p('Unformatted Blocks  ', l_unformatted_blocks);
 53      p('FS1 Blocks (0-25)   ', l_fs1_blocks);
 54      p('FS2 Blocks (25-50)  ', l_fs2_blocks);
 55      p('FS3 Blocks (50-75)  ', l_fs3_blocks);
 56      p('FS4 Blocks (75-100) ', l_fs4_blocks);
 57      p('Full Blocks         ', l_full_blocks);
 58    ELSE
 59      dbms_space.free_blocks(segment_owner => p_owner, segment_name => p_segname,
 60                             segment_type => p_type, freelist_group_id => 0,
 61                             free_blks => l_free_blks);
 62    END IF;
 63
 64    dbms_space.unused_space(segment_owner => p_owner, segment_name => p_segname,
 65                            segment_type => p_type, partition_name => p_partition,
 66                            total_blocks => l_total_blocks,
 67                            total_bytes => l_total_bytes,
 68                            unused_blocks => l_unused_blocks,
 69                            unused_bytes => l_unused_bytes,
 70                            last_used_extent_file_id => l_lastusedextfileid,
 71                            last_used_extent_block_id => l_lastusedextblockid,
 72                            last_used_block => l_last_used_block);
 73
 74    p('Total Blocks ', l_total_blocks);
 75    p('Total Bytes  ', l_total_bytes);
 76    p('Total MBytes ', trunc(l_total_bytes / 1024 / 1024));
 77    p('Unused Blocks', l_unused_blocks);
 78    p('Unused Bytes ', l_unused_bytes);
 79    p('Last Used Ext FileId', l_lastusedextfileid);
 80    p('Last Used Ext BlockId', l_lastusedextblockid);
 81    p('Last Used Block', l_last_used_block);
 82  END;
 83  /

过程已创建。

2.用法演示

sys@ORCL>create table test_space
  2  AS
  3  select * from dba_tables;

表已创建。

sys@ORCL>exec show_space('TEST_SPACE');
Total Blocks ...........................             104
Total Bytes  ...........................         851,968
Total MBytes ...........................               0
Unused Blocks...........................               3
Unused Bytes ...........................          24,576
Last Used Ext FileId....................               1
Last Used Ext BlockId...................          87,912
Last Used Block.........................               5

PL/SQL 过程已成功完成。

Total Blocks、Total Bytes、Total MBytes     为所查看的段分配的总空间量，单位分别是数据库块、字节、兆字节

Unused Blocks、Unused Bytes                    表示未用空间所占的比例(未用空间量)，

                                                                    这些块已经分配给所查看的段，但目前在段的HWM之上

Last Used Ext FileId                                    最后使用的文件的文件ID，该文件包含最后一个含数据的区段(extent)

Last Used Ext BlockId                                最后一个区段开始处的块ID；这是最后使用的文件中的块ID

Last Used Block                                        最后一个区段中最后一个块的偏移量

sys@ORCL>delete from test_space;
已删除2859行。

sys@ORCL>commit;
提交完成。

sys@ORCL>exec show_space('TEST_SPACE');
Total Blocks ...........................             104
Total Bytes  ...........................         851,968
Total MBytes ...........................               0
Unused Blocks...........................               3
Unused Bytes ...........................          24,576
Last Used Ext FileId....................               1
Last Used Ext BlockId...................          87,912
Last Used Block.........................               5

PL/SQL 过程已成功完成。

sys@ORCL>truncate table test_space;
表被截断。

sys@ORCL>exec show_space('TEST_SPACE');
Total Blocks ...........................               8
Total Bytes  ...........................          65,536
Total MBytes ...........................               0
Unused Blocks...........................               7
Unused Bytes ...........................          57,344
Last Used Ext FileId....................               1
Last Used Ext BlockId...................          87,816
Last Used Block.........................               1

PL/SQL 过程已成功完成。

sys@ORCL>drop table test_space;
表已删除。