Mega Code Archive
Show table space
<![CDATA[
SQL>
SQL> 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 )
5 as
6 l_free_blks number;
7
8 l_total_blocks number;
9 l_total_bytes number;
10 l_unused_blocks number;
11 l_unused_bytes number;
12 l_LastUsedExtFileId number;
13 l_LastUsedExtBlockId number;
14 l_last_used_block number;
15 begin
16 dbms_space.free_blocks( segment_owner => p_owner,
17 segment_name => p_segname,
18 segment_type => p_type,
19 partition_name => p_partition,
20 freelist_group_id => 0,
21 free_blks => l_free_blks );
22
23 dbms_space.unused_space( segment_owner => p_owner,
24 segment_name => p_segname,
25 segment_type => p_type,
26 partition_name => p_partition,
27 total_blocks => l_total_blocks,
28 total_bytes => l_total_bytes,
29 unused_blocks => l_unused_blocks,
30 unused_bytes => l_unused_bytes,
31 last_used_extent_file_id => l_LastUsedExtFileId,
32 last_used_extent_block_id => l_LastUsedExtBlockId,
33 last_used_block => l_last_used_block );
34
35 dbms_output.put_line( 'Free Blocks');
36 dbms_output.put_line( l_free_blks );
37 dbms_output.put_line( 'Total Blocks');
38 dbms_output.put_line( l_total_blocks );
39 dbms_output.put_line( 'Total Bytes');
40 dbms_output.put_line( l_total_bytes );
41 dbms_output.put_line( 'Unused Blocks');
42 dbms_output.put_line( l_unused_blocks );
43 dbms_output.put_line( 'Unused Bytes');
44 dbms_output.put_line( l_unused_bytes );
45 dbms_output.put_line( 'Last Used Ext FileId');
46 dbms_output.put_line( l_LastUsedExtFileId );
47 dbms_output.put_line( 'Last Used Ext BlockId');
48 dbms_output.put_line( l_LastUsedExtBlockId );
49 dbms_output.put_line( 'Last Used Block');
50 dbms_output.put_line( l_last_used_block );
51 end;
52 /
Procedure created.
SQL>
]]>