show_space　procedure from asktom

上一篇 / 下一篇  2007-12-19 13:41:21 / 个人分类：Oracle

查看( 191 ) / 评论( 1 ) / 评分( 5 / 0 )

create or replace procedure show_space
A-j+S(gS0( p_segname in varchar2,
)u9Y-J,O eZ,^0  p_owner   in varchar2 default user,ITPUB个人空间uKcuVrum
  p_type    in varchar2 default 'TABLE',
%ltemU!_0A0  p_partition in varchar2 default NULL )
L*b0gd-i9Q0authid current_user
c M#E5Zpd0asITPUB个人空间 QI4S0Z:Tk
    l_free_blks                 number;ITPUB个人空间q[(Ejd
    l_total_blocks              number;
!q|+A'r IP0    l_total_bytes               number;
Q:XfB@&y\/{kw0    l_unused_blocks             number;
-H"y]5a L1@I0    l_unused_bytes              number;ITPUB个人空间:Y#i\ rP1X
    l_LastUsedExtFileId         number;
)u-N_+R$x(JqM0    l_LastUsedExtBlockId        number;
m]IZb~mV0    l_LAST_USED_BLOCK           number;
Z9M Na0aD$sq k)U"i0    l_segment_space_mgmt        varchar2(255);
p] mG6b9k i0    l_unformatted_blocks number;
5c(b.SU+HSQ3A1@*U0    l_unformatted_bytes number;
w!J?3Uk!b4z0    l_fs1_blocks number; l_fs1_bytes number;
@jC0z@hL ?Lp0    l_fs2_blocks number; l_fs2_bytes number;
L o2J#\b;b n+IM0    l_fs3_blocks number; l_fs3_bytes number;
-ZP"kPR$run0    l_fs4_blocks number; l_fs4_bytes number;ITPUB个人空间"B.wo?7GD&o
    l_full_blocks number; l_full_bytes number;

    procedure p( p_label in varchar2, p_num in number )
3l!S Z*e"[4F1SQ0    isITPUB个人空间,i6v?]CR|E+v+Ho
    beginITPUB个人空间xpSL)I%Iy)Gt
        dbms_output.put_line( rpad(p_label,40,'.') ||ITPUB个人空间s2M?mVT3[E2}6n
                              to_char(p_num,'999,999,999,999') );
k5OhVlC0    end;ITPUB个人空间"a @|W0Lt'EA
beginITPUB个人空间N:sZr gnK"?
  if p_partition is not null
-t yMPW1h0  thenITPUB个人空间kt(Q h&|HF@
    select ts.segment_space_managementITPUB个人空间Z]I:p&U#@+c
      into l_segment_space_mgmtITPUB个人空间 h9o o[]
      from dba_segments seg,  dba_tablespaces  ts
;[WK,Q2aKtva2F$H Z}0     where seg.segment_name    = p_segnameITPUB个人空间}`/~.gIPl
       and seg.partition_name  = p_partitionITPUB个人空间0?p E9j,\TN!qs
       and   seg.owner           = p_owner
r0jpD:d0       and   seg.tablespace_name = ts.tablespace_name;ITPUB个人空间g{"m WOCwK-M
  elseITPUB个人空间'L1FZU$b7Kv9fL1C
    select ts.segment_space_management
;uRn!B G+QnL0      into l_segment_space_mgmtITPUB个人空间]5]2T~)u$p
      from dba_segments seg,  dba_tablespaces  ts
c\ L7F!Y0     where seg.segment_name    = p_segnameITPUB个人空间 {0c6waAP Y3`;v
       and   seg.owner           = p_owner
1bx2K)t:Iy ]O2LZ*q0       and   seg.tablespace_name = ts.tablespace_name; 
c1|a5N jC t0  end if;
_v.`T dk-x8j0  if l_segment_space_mgmt = 'AUTO'
7W i4RDnRV0  thenITPUB个人空间 Fv+~J!TI!k
     dbms_space.space_usage (
,bd&O*As+d0       p_owner,
Q7b BWjcX0       p_segname,ITPUB个人空间!N ozn#n e?
       p_type,ITPUB个人空间[WMqE
       l_unformatted_blocks,ITPUB个人空间!e`TpdC1n`&a
       l_unformatted_bytes,
"u!k#yiC*UbJ0       l_fs1_blocks, l_fs1_bytes,ITPUB个人空间y+y3QD?gH]
       l_fs2_blocks, l_fs2_bytes,ITPUB个人空间^5_pxKsd
       l_fs3_blocks, l_fs3_bytes,
.vebYQ!W~ F${7W0       l_fs4_blocks, l_fs4_bytes,
2{ Xd#E\ iDR0       l_full_blocks, l_full_bytes,ITPUB个人空间5u(N#QP*bN7[8k3A/\b
       p_partition
7hM nWt+B'r0     );
yW7bbld!w0     --ITPUB个人空间T8Jv c_3I
     p( 'Unformatted Blocks ', l_unformatted_blocks );
vF1E"[;M#LF,v]`(n0     p( 'FS1 Blocks (0-25)  ', l_fs1_blocks );ITPUB个人空间*?m-~&D#s;m,r'T
     p( 'FS2 Blocks (25-50) ', l_fs2_blocks );
$g:H _,y+op0     p( 'FS3 Blocks (50-75) ', l_fs3_blocks );
2y/OOM P.m j C3Z.cb0     p( 'FS4 Blocks (75-100)', l_fs4_blocks );
&At,j?.C5d~| ~0     p( 'Full Blocks        ', l_full_blocks );ITPUB个人空间Q-~i;x0]n.Mu
  elseITPUB个人空间B+N.S/u7B8X
     dbms_space.free_blocks(ITPUB个人空间 x!s q4y3az*c
       segment_owner     => p_owner,
T&S*D H'jB&uV0       segment_name      => p_segname,
R,dD%mZ\#zK5I0       segment_type      => p_type,
f j?Q0[n ^J0       freelist_group_id => 0,
GR-TP1I'O|0       free_blks         => l_free_blks,
QqjJ4e#O$~0       partition_name    => p_partitionITPUB个人空间{Q5L[4C G
     );ITPUB个人空间@oo W2d!E S
     --
J:[:i$O;`y1S$b6o0     p( 'Free Blocks', l_free_blks );
^f| I%p3r'NN w0  end if;ITPUB个人空间~1Df~;[ j7sI$]
    dbms_space.unused_space
*h9}.MN0]XU0    ( segment_owner     => p_owner,ITPUB个人空间+nY%r2g#uw"S(V
      segment_name      => p_segname,
-fU(I&]~V0      segment_type      => p_type,ITPUB个人空间WEl5l C
      partition_name    => p_partition,ITPUB个人空间#}B#fG7~-l
      total_blocks      => l_total_blocks,
.n z6mK3n1B!b0      total_bytes       => l_total_bytes,ITPUB个人空间$zz!~1xKqV
      unused_blocks     => l_unused_blocks,
yl~3gw2p`1UD0      unused_bytes      => l_unused_bytes,ITPUB个人空间 O1V/S|$J3A(b;cV
      LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,ITPUB个人空间h\ r u%@n Lt
      LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
MR\.f` }W/E0      LAST_USED_BLOCK => l_LAST_USED_BLOCK     
y*UB'N aAN#b0      );

    p( 'Total Blocks', l_total_blocks );ITPUB个人空间J_5w1No5Y0H
    p( 'Total Bytes', l_total_bytes );
-JfR3n,`0    p( 'Total MBytes', trunc(l_total_bytes/1024/1024) );
:ZU^4~;{i#j0    p( 'Unused Blocks', l_unused_blocks );
"q4WNofR Qw,P YJ0    p( 'Unused Bytes', l_unused_bytes );
BXjC_0    p( 'Last Used Ext FileId', l_LastUsedExtFileId );
)C9]GI ^)|n t0    p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );ITPUB个人空间3q c!WiR0U`K&T B@
    p( 'Last Used Block', l_LAST_USED_BLOCK );
!h,T(`#P:@0end;
_6kRgg0/

对于一般表：

SQL> set serverout onITPUB个人空间I6H/M*Qb
SQL> exec show_space('T_CLM_MAIN','SCOTT');
e-Y-^l d)pe+iX0Unformatted Blocks .....................               0
#G D6{#Am0FS1 Blocks (0-25)  .....................          35,457
c?8n?&z\ `1N0FS2 Blocks (25-50) .....................               1ITPUB个人空间A(G rjO(e*w
FS3 Blocks (50-75) .....................               0ITPUB个人空间&U7U2\ ~X~TS
FS4 Blocks (75-100).....................              85ITPUB个人空间:^ZezH)ia4\&Y
Full Blocks        .....................          43,664
2T5Fvjc0Total Blocks............................          79,616ITPUB个人空间6};Q n}3x3c3j"M1`o
Total Bytes.............................     652,214,272
0w!t&zJ;d.a5K0Total MBytes............................             622
nt]*f*U([#_/_ F%J5f0Unused Blocks...........................               0ITPUB个人空间4Z*e&Z3@C
Unused Bytes............................               0ITPUB个人空间W7vB:Tv
Last Used Ext FileId....................               2ITPUB个人空间+Wu _2H.M
Last Used Ext BlockId...................          80,264ITPUB个人空间u-j`r+IeD a ]c0b
Last Used Block.........................             128

PL/SQL procedure successfully completed.

对于分区表：

SQL> exec show_space('PART_CLM_MAIN','SCOTT','TABLE PARTITION','DATA_PART2');ITPUB个人空间.x2X!r?n l z9e
Unformatted Blocks .....................               0ITPUB个人空间9Oo#o ww,M
FS1 Blocks (0-25)  .....................               0ITPUB个人空间*X,q;t p?
FS2 Blocks (25-50) .....................               0
:u _,Jl/P}\-w0FS3 Blocks (50-75) .....................               0
,H-M9@,N q4K+J _0FS4 Blocks (75-100).....................               0ITPUB个人空间uO*B o@ }?
Full Blocks        .....................          80,140
g)r"W%_3O&i0Total Blocks............................          80,640ITPUB个人空间}JWz,I i*Y
Total Bytes.............................     660,602,880
zw,`z.S-f jU|Qr;H2w0Total MBytes............................             630
+DyRy(_jB0Unused Blocks...........................              87ITPUB个人空间)n7U \)j(k
Unused Bytes............................         712,704
Nr!^/Il'c\fq0Last Used Ext FileId....................               7
jR#TxIl0Last Used Ext BlockId...................          80,520ITPUB个人空间rGxjzY%|P$r ~&W&Qi
Last Used Block.........................              41

PL/SQL procedure successfully completed.

对于分区索引：

SQL> exec show_space('LIDX_CLM_MAIN','SCOTT','INDEX PARTITION','LIDX_PART1');ITPUB个人空间na^-d4zy$e gD\,a
Unformatted Blocks .....................               0
;eC$t~Lk0FS1 Blocks (0-25)  .....................               0ITPUB个人空间(pk7B8XW ~w
FS2 Blocks (25-50) .....................               1ITPUB个人空间 A RuU ~7E
FS3 Blocks (50-75) .....................               0ITPUB个人空间W"EM(Z VsPq
FS4 Blocks (75-100).....................               0
9OxMG/u KY*PQ0Full Blocks        .....................             108
0r8^6hvo S7G0Total Blocks............................             128
@Qri/~0Total Bytes.............................       1,048,576ITPUB个人空间1LVv3Z(F
Total MBytes............................               1
c0Lk*M9Qt/^2K6^0Unused Blocks...........................              14ITPUB个人空间_(i4`5BY;s%`
Unused Bytes............................         114,688ITPUB个人空间"ds~GE;U
Last Used Ext FileId....................              12ITPUB个人空间zYg Y]5X
Last Used Ext BlockId...................               8ITPUB个人空间3@x @y4I6jY_A
Last Used Block.........................             114

PL/SQL procedure successfully completed.

 

 

 


导入论坛 引用链接 收藏 分享给好友 推荐到圈子 管理 举报

TAG: oracle

与青春有关的日子 引用 删除 yellowlee   /   2009-12-19 22:03:31
评 5 分
 

评分：0

我来说两句

显示全部

:loveliness: :handshake :victory: :funk: :time: :kiss: :call: :hug: :lol :'( :Q :L ;P :$ :P :o :@ :D :( :)

Open Toolbar