show_spce存储过程的使用
一、实验说明：
操作系统：rhel 5.4 x32
数据库：oracle 11g r2
说明：在看谭大师《让Oralce跑得更快2》中的数据压缩篇时提到一个来自asktom.oracle.com网站的show_space()存储过程，在这里做一下对于该过程的使用演示。
二、实验操作：
----创建存储过程-----------
1 SQL> show_space.sql 2 3 Procedure created. 4 ----查看一下该存储过程涉及到的参数----------------------------
5 SQL> desc show_space 6 PROCEDURE show_space 7 Argument Name Type In/Out Default? 8 --------------- ------------------ ------ -------- 9 P_SEGNAME VARCHAR2 IN 10 P_OWNER VARCHAR2 IN DEFAULT 11 P_TYPE VARCHAR2 IN DEFAULT 12 P_PARTITION VARCHAR2 IN DEFAULT 13 ----打开输出功能------------------
14 SQL> set serveroutput on; 15 ----使用show_space存储过程显示big_tables的存储信息--------------------
16 SQL> exec show_space('BIG_TABLES'); 17 Unformatted Blocks ..................... 752 18 FS1 Blocks (0-25) ...................... 1 19 FS2 Blocks (25-50) ..................... 0 20 FS3 Blocks (50-75) ..................... 0 21 FS4 Blocks (75-100)..................... 143 22 Full Blocks ..................... 8,181 23 Total Blocks............................ 9,216 24 Total Bytes............................. 75,497,472 25 Total MBytes............................ 72 26 Unused Blocks........................... 0 27 Unused Bytes............................ 0 28 Last Used Ext FileId.................... 5 29 Last Used Ext BlockId................... 13,056 30 Last Used Block......................... 1,024 31 32 PL/SQL procedure successfully completed. 33 34 ----在这里贴上show.space.sql脚本的内容--------------------
35 [oracle@yft ~]$ cat show_space.sql 36 create or replace procedure show_space 37 ( p_segname in varchar2, 38 p_owner in varchar2 default user, 39 p_type in varchar2 default 'TABLE', 40 p_partition in varchar2 default NULL ) 41 -- this procedure uses authid current user so it can query DBA_* 42 -- views using privileges from a ROLE and so it can be installed 43 -- once per database, instead of once per user that wanted to use it 44 authid current_user 45 as 46 l_free_blks number; 47 l_total_blocks number; 48 l_total_bytes number; 49 l_unused_blocks number; 50 l_unused_bytes number; 51 l_LastUsedExtFileId number; 52 l_LastUsedExtBlockId number; 53 l_LAST_USED_BLOCK number; 54 l_segment_space_mgmt varchar2(255); 55 l_unformatted_blocks number; 56 l_unformatted_bytes number; 57 l_fs1_blocks number; l_fs1_bytes number; 58 l_fs2_blocks number; l_fs2_bytes number; 59 l_fs3_blocks number; l_fs3_bytes number; 60 l_fs4_blocks number; l_fs4_bytes number; 61 l_full_blocks number; l_full_bytes number; 62 63 -- inline procedure to print out numbers nicely formatted 64 -- with a simple label 65 procedure p( p_label in varchar2, p_num in number ) 66 is 67 begin 68 dbms_output.put_line( rpad(p_label,40,'.') || 69 to_char(p_num,'999,999,999,999') ); 70 end; 71 begin 72 -- this query is executed dynamically in order to allow this procedure 73 -- to be created by a user who has access to DBA_SEGMENTS/TABLESPACES 74 -- via a role as is customary. 75 -- NOTE: at runtime, the invoker MUST have access to these two 76 -- views! 77 -- this query determines if the object is a ASSM object or not 78 begin 79 execute immediate 80 'select ts.segment_space_management 81 from dba_segments seg, dba_tablespaces ts 82 where seg.segment_name = :p_segname 83 and (:p_partition is null or 84 seg.partition_name = :p_partition) 85 and seg.owner = :p_owner 86 and seg.tablespace_name = ts.tablespace_name' 87 into l_segment_space_mgmt 88 using p_segname, p_partition, p_partition, p_owner; 89 exception 90 when too_many_rows then 91 dbms_output.put_line 92 ( 'This must be a partitioned table, use p_partition => '); 93 return; 94 end; 95 96 97 -- if the object is in an ASSM tablespace, we must use this API 98 -- call to get space information, else we use the FREE_BLOCKS 99 -- API for the user managed segments 100 if l_segment_space_mgmt = 'AUTO' 101 then 102 dbms_space.space_usage 103 ( p_owner, p_segname, p_type, l_unformatted_blocks, 104 l_unformatted_bytes, l_fs1_blocks, l_fs1_bytes, 105 l_fs2_blocks, l_fs2_bytes, l_fs3_blocks, l_fs3_bytes, 106 l_fs4_blocks, l_fs4_bytes, l_full_blocks, l_full_bytes, p_partition); 107 108 p( 'Unformatted Blocks ', l_unformatted_blocks ); 109 p( 'FS1 Blocks (0-25) ', l_fs1_blocks ); 110 p( 'FS2 Blocks (25-50) ', l_fs2_blocks ); 111 p( 'FS3 Blocks (50-75) ', l_fs3_blocks ); 112 p( 'FS4 Blocks (75-100)', l_fs4_blocks ); 113 p( 'Full Blocks ', l_full_blocks ); 114 else 115 dbms_space.free_blocks( 116 segment_owner => p_owner, 117 segment_name => p_segname, 118 segment_type => p_type, 119 freelist_group_id => 0, 120 free_blks => l_free_blks); 121 122 p( 'Free Blocks', l_free_blks ); 123 end if; 124 125 -- and then the unused space API call to get the rest of the 126 -- information 127 dbms_space.unused_space 128 ( segment_owner => p_owner, 129 segment_name => p_segname, 130 segment_type => p_type, 131 partition_name => p_partition, 132 total_blocks => l_total_blocks, 133 total_bytes => l_total_bytes, 134 unused_blocks => l_unused_blocks, 135 unused_bytes => l_unused_bytes, 136 LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId, 137 LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId, 138 LAST_USED_BLOCK => l_LAST_USED_BLOCK ); 139 140 p( 'Total Blocks', l_total_blocks ); 141 p( 'Total Bytes', l_total_bytes ); 142 p( 'Total MBytes', trunc(l_total_bytes/1024/1024) ); 143 p( 'Unused Blocks', l_unused_blocks ); 144 p( 'Unused Bytes', l_unused_bytes ); 145 p( 'Last Used Ext FileId', l_LastUsedExtFileId ); 146 p( 'Last Used Ext BlockId', l_LastUsedExtBlockId ); 147 p( 'Last Used Block', l_LAST_USED_BLOCK ); 148 end; 149 /
