Oracle PL/SQL Tutorial/System Packages/dbms space
Show table space
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>
Use dbms_space.free_blocks and dbms_space.unused_space
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.