Mega Code Archive
Use dbms_space free_blocks and dbms_space unused_space
<![CDATA[
SQL> set echo on
SQL>
SQL> create table t ( x int, y char(1000) default 'x' );
Table created.
SQL>
SQL> create or replace procedure measure_usage
2 as
3 l_free_blks number;
4 l_total_blocks number;
5 l_total_bytes number;
6 l_unused_blocks number;
7 l_unused_bytes number;
8 l_LastUsedExtFileId number;
9 l_LastUsedExtBlockId number;
10 l_LAST_USED_BLOCK number;
11
12 procedure get_data
13 is
14 begin
15 dbms_space.free_blocks
16 ( segment_owner => USER,
17 segment_name => 'T',
18 segment_type => 'TABLE',
19 FREELIST_group_id => 0,
20 free_blks => l_free_blks );
21
22 dbms_space.unused_space
23 ( segment_owner => USER,
24 segment_name => 'T',
25 segment_type => 'TABLE',
26 total_blocks => l_total_blocks,
27 total_bytes => l_total_bytes,
28 unused_blocks => l_unused_blocks,
29 unused_bytes => l_unused_bytes,
30 LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
31 LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
32 LAST_USED_BLOCK => l_last_used_block ) ;
33
34
35 dbms_output.put_line( L_free_blks || ' on FREELIST, ' ||
36 to_number(l_total_blocks-l_unused_blocks-1 ) ||
37 ' used by table' );
38 end;
39 begin
40 for i in 0 .. 10
41 loop
42 dbms_output.put( 'insert ' || to_char(i,'00') || ' ' );
43 get_data;
44 insert into t (x) values ( i );
45 commit ;
46 end loop;
47
48
49 for i in 0 .. 10
50 loop
51 dbms_output.put( 'update ' || to_char(i,'00') || ' ' );
52 get_data;
53 update t set y = null where x = i;
54 commit;
55 end loop;
56 end;
57 /
Procedure created.
SQL>
SQL> exec measure_usage
insert 00 0 on FREELIST, 0 used by table
insert 01 1 on FREELIST, 1 used by table
insert 02 1 on FREELIST, 1 used by table
insert 03 1 on FREELIST, 1 used by table
insert 04 1 on FREELIST, 1 used by table
insert 05 1 on FREELIST, 1 used by table
insert 06 1 on FREELIST, 1 used by table
insert 07 1 on FREELIST, 1 used by table
insert 08 1 on FREELIST, 2 used by table
insert 09 1 on FREELIST, 2 used by table
insert 10 1 on FREELIST, 2 used by table
update 00 1 on FREELIST, 2 used by table
update 01 1 on FREELIST, 2 used by table
update 02 1 on FREELIST, 2 used by table
update 03 1 on FREELIST, 2 used by table
update 04 2 on FREELIST, 2 used by table
update 05 2 on FREELIST, 2 used by table
update 06 2 on FREELIST, 2 used by table
update 07 2 on FREELIST, 2 used by table
update 08 2 on FREELIST, 2 used by table
update 09 2 on FREELIST, 2 used by table
update 10 2 on FREELIST, 2 used by table
PL/SQL procedure successfully completed.
SQL>
SQL> drop table t;
Table dropped.
]]>