oracle实用sql(8)--segment show space

点击(此处)折叠或打开

1. declare
   
2.   --输入段信息
   
3.   p_owner varchar2(255):='SCOTT';
   
4.   p_seg_name varchar2(255):='T_TEST01';
   
5.   p_type varchar2(255):='TABLE';
   
6.   p_partition varchar2(255);
   
7.   
   
8.   v_free_blocks number;
   
9.   
   
10.   v_total_blocks number;
    
11.   v_total_bytes number;
    
12.   v_unused_blocks number;
    
13.   v_unused_bytes number;
    
14.   v_last_used_ext_file_id number;
    
15.   v_last_used_ext_block_id number;
    
16.   v_last_used_block number;
    
17.   
    
18.   v_segment_space_mgmt varchar2(255);
    
19.   
    
20.   v_unformatted_blocks number;
    
21.   v_unformatted_bytes number;
    
22.   v_fs1_blocks number;
    
23.   v_fs1_bytes number;
    
24.   v_fs2_blocks number;
    
25.   v_fs2_bytes number;
    
26.   v_fs3_blocks number;
    
27.   v_fs3_bytes number;
    
28.   v_fs4_blocks number;
    
29.   v_fs4_bytes number;
    
30.   v_full_blocks number;
    
31.   v_full_bytes number;
    
32. begin
    
33.    --判断是段空间管理是auto还是manual
    
34.    select ts.segment_space_management into v_segment_space_mgmt
    
35.    from dba_segments seg,dba_tablespaces ts
    
36.    where seg.tablespace_name=ts.tablespace_name
    
37.    and seg.segment_name=p_seg_name
    
38.    and seg.owner=p_owner
    
39.    and (p_partition is null or seg.partition_name=p_partition);
    
40.    
    
41.    if v_segment_space_mgmt='AUTO' then
    
42.      --dbms_space.space_usage
    
43.      dbms_space.space_usage(segment_owner =>p_owner,
    
44.                          segment_name =>p_seg_name,
    
45.                          segment_type =>p_type,
    
46.                          unformatted_blocks =>v_unformatted_blocks,
    
47.                          unformatted_bytes =>v_unformatted_bytes,
    
48.                          fs1_blocks => v_fs1_blocks,
    
49.                          fs1_bytes => v_fs1_bytes,
    
50.                          fs2_blocks => v_fs2_blocks,
    
51.                          fs2_bytes => v_fs2_bytes,
    
52.                          fs3_blocks => v_fs3_blocks,
    
53.                          fs3_bytes => v_fs3_bytes,
    
54.                          fs4_blocks => v_fs4_blocks,
    
55.                          fs4_bytes => v_fs4_bytes,
    
56.                          full_blocks =>v_full_blocks,
    
57.                          full_bytes =>v_full_bytes,
    
58.                          partition_name =>p_partition
    
59.                          );
    
60.      dbms_output.put_line('unformatted_blocks :'||v_unformatted_blocks);
    
61.      dbms_output.put_line('fs1_blocks (0-25):'|| v_fs1_blocks);
    
62.      dbms_output.put_line('fs2_blocks (25-50):'|| v_fs2_blocks);
    
63.      dbms_output.put_line('fs3_blocks (50-75):'|| v_fs3_blocks);
    
64.      dbms_output.put_line('fs4_blocks (75-100):'|| v_fs4_blocks);
    
65.      dbms_output.put_line('full_blocks :'||v_full_blocks);
    
66.    else
    
67.      --dbms_space.free_blocks
    
68.      dbms_space.free_blocks(segment_owner =>p_owner,
    
69.                          segment_name =>p_seg_name,
    
70.                          segment_type =>p_type,
    
71.                          freelist_group_id =>0,
    
72.                          free_blks =>v_free_blocks,
    
73.                          partition_name =>p_partition
    
74.                          );
    
75.      dbms_output.put_line('free_blks:'||v_free_blocks);
    
76.    end if;
    
77.    --dbms_space.unused_space
    
78.    dbms_space.unused_space(segment_owner =>p_owner,
    
79.                          segment_name =>p_seg_name,
    
80.                          segment_type =>p_type,
    
81.                          total_blocks =>v_total_blocks,
    
82.                          total_bytes =>v_total_bytes,
    
83.                          unused_blocks =>v_unused_blocks,
    
84.                          unused_bytes =>v_unused_bytes,
    
85.                          last_used_extent_file_id =>v_last_used_ext_file_id,
    
86.                          last_used_extent_block_id =>v_last_used_ext_block_id,
    
87.                          last_used_block =>v_last_used_block,
    
88.                          partition_name =>p_partition
    
89.                          );
    
90.     dbms_output.put_line('total_blocks :'||v_total_blocks);
    
91.     dbms_output.put_line('total_bytes :'||v_total_bytes);
    
92.     dbms_output.put_line('unused_blocks :'||v_unused_blocks);
    
93.     dbms_output.put_line('unused_bytes :'||v_unused_bytes);
    
94.     dbms_output.put_line('last_used_extent_file_id :'||v_last_used_ext_file_id);
    
95.     dbms_output.put_line('last_used_extent_block_id :'||v_last_used_ext_block_id);
    
96.     dbms_output.put_line('last_used_block :'||v_last_used_block);
    
97. end;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28539951/viewspace-2116162/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28539951/viewspace-2116162/