Mega Code Archive
Create a stored procedure to measure a table usage
<![CDATA[
SQL>
SQL> set echo on
SQL>
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 procedure get_data is
12 begin
13 dbms_space.free_blocks
14 ( segment_owner => USER,
15 segment_name => 'T',
16 segment_type => 'TABLE',
17 FREELIST_group_id => 0,
18 free_blks => l_free_blks );
19
20 dbms_space.unused_space
21 ( segment_owner => USER,
22 segment_name => 'T',
23 segment_type => 'TABLE',
24 total_blocks => l_total_blocks,
25 total_bytes => l_total_bytes,
26 unused_blocks => l_unused_blocks,
27 unused_bytes => l_unused_bytes,
28 LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
29 LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
30 LAST_USED_BLOCK => l_last_used_block ) ;
31
32
33 dbms_output.put_line( L_free_blks || ' on FREELIST, ' ||
34 to_number(l_total_blocks-l_unused_blocks-1 ) ||
35 ' used by table' );
36 end;
37 begin
38 for i in 0 .. 10
39 loop
40 dbms_output.put( 'insert ' || to_char(i,'00') || ' ' );
41 get_data;
42 insert into t (x) values ( i );
43 commit ;
44 end loop;
45
46
47 for i in 0 .. 10
48 loop
49 dbms_output.put( 'update ' || to_char(i,'00') || ' ' );
50 get_data;
51 update t set y = null where x = i;
52 commit;
53 end loop;
54 end;
55 /
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>
SQL> drop table t;
Table dropped.
SQL>
SQL> --
]]>