Database Reference
In-Depth Information
■
In the following pL/sQL, the index reported on must be built in an MssM tablespace, and not assM. If you
attempt to run this against an index in an assM tablespace, you'll receive an “Ora-10618: Operation not allowed on this
segment” message.
Note
EODA@ORA12CR1> declare
2 l_freelist_blocks number;
3 begin
4 dbms_space.free_blocks
5 ( segment_owner => user,
6 segment_name => 'BIG_TABLE_PK',
7 segment_type => 'INDEX',
8 freelist_group_id => 0,
9 free_blks => l_freelist_blocks );
10 dbms_output.put_line( 'blocks on freelist = ' || l_freelist_blocks );
11 end;
12 /
blocks on freelist = 0
PL/SQL procedure successfully completed.
EODA@ORA12CR1> select leaf_blocks from user_indexes where index_name = 'BIG_TABLE_PK';
LEAF_BLOCKS
-----------
1043
Before we perform this mass deletion, we have no blocks on the
FREELIST
and there are 1,043 blocks in the
leafs
of the index, holding data. Now, we'll perform the delete and measure the space utilization again:
EODA@ORA12CR1> delete from big_table where id <= 250000;
250000 rows deleted.
EODA@ORA12CR1> commit;
Commit complete.
EODA@ORA12CR1> declare
2 l_freelist_blocks number;
3 begin
4 dbms_space.free_blocks
5 ( segment_owner => user,
6 segment_name => 'BIG_TABLE_PK',
7 segment_type => 'INDEX',
8 freelist_group_id => 0,
9 free_blks => l_freelist_blocks );
10 dbms_output.put_line( 'blocks on freelist = ' || l_freelist_blocks );
11 dbms_stats.gather_index_stats
12 ( user, 'BIG_TABLE_PK' );
13 end;
14 /
