Mega Code Archive
Use dbms_space to show space
<![CDATA[
SQL> --show space
SQL>
SQL>
SQL> create or replace procedure show_space(
2 p_segname in varchar2,
3 p_owner in varchar2 default user,
4 p_type in varchar2 default 'TABLE')
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
16 begin
17 dbms_space.free_blocks(
18 segment_owner => p_owner,
19 segment_name => p_segname,
20 segment_type => p_type,
21 freelist_group_id => 0,
22 free_blks => l_free_blks );
23
24 dbms_space.unused_space(
25 segment_owner => p_owner,
26 segment_name => p_segname,
27 segment_type => p_type,
28 total_blocks => l_total_blocks,
29 total_bytes => l_total_bytes,
30 unused_blocks => l_unused_blocks,
31 unused_bytes => l_unused_bytes,
32 last_used_extent_file_id => l_lastusedextfileid,
33 last_used_extent_block_id => l_lastusedextblockid,
34 last_used_block => l_last_used_block );
35
36 dbms_output.put_line( 'Free Blocks');
37 dbms_output.put_line( l_free_blks );
38 dbms_output.put_line( 'Total Blocks');
39 dbms_output.put_line( l_total_blocks );
40 dbms_output.put_line( 'Total Bytes');
41 dbms_output.put_line( l_total_bytes );
42 dbms_output.put_line( 'Unused Blocks');
43 dbms_output.put_line( l_unused_blocks );
44 dbms_output.put_line( 'Unused Bytes');
45 dbms_output.put_line( l_unused_bytes );
46 dbms_output.put_line( 'Last Used Ext FileId');
47 dbms_output.put_line( l_lastusedextfileid );
48 dbms_output.put_line( 'Last Used Ext BlockId');
49 dbms_output.put_line( l_lastusedextblockid );
50 dbms_output.put_line( 'Last Used Block');
51 dbms_output.put_line( l_last_used_block );
52 end;
53 /
Procedure created.
SQL>
SQL> --
]]>