Availability and Optimization of Free Space in a Data Block

Two types of statements can increase the free space of one or more data blocks: DELETE statements, and UPDATE statements that update existing values to smaller values. The released space from these types of statements is available for subsequent INSERT statements under the following conditions:

• If the INSERT statement is in the same transaction and subsequent to the statement that frees space, then the INSERT statement can use the space made available

rajesh@10GR2> create table t
  2  as
  3  select *
  4  from all_objects
  5  where rownum <= 100;

Table created.

Elapsed: 00:00:00.10

rajesh@10GR2> begin
  2     dbms_output.put_line (' Block number Allocated for the Table ''T'' ');
  3
  4     for x in (select distinct dbms_rowid.rowid_block_number(rowid) as block_num from T)
  5     loop
  6             dbms_output.put_line (x.block_num);
  7     end loop;
  8
  9     delete from t;
 10
 11     insert into t
 12     select *
 13     from all_objects
 14     where rownum <= 100;
 15
 16     dbms_output.put_line (' Utilized block number after delete and Insert on table ''T'' ');
 17
 18     for x in (select distinct dbms_rowid.rowid_block_number(rowid) as block_num from T)
 19     loop
 20             dbms_output.put_line (x.block_num);
 21     end loop;
 22
 23  end;
 24  /

 Block number Allocated for the Table 'T'
    78365
    78364
 Utilized block number after delete and Insert on table 'T'
    78365
    78364

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09

• If the INSERT statement is in a separate transaction from the statement that frees space (perhaps being run by another user), then the INSERT statement can use the space made available only after the other transaction commits and only if the space is needed.

rajesh@10GR2> select userenv('sid') from dual;

USERENV('SID')
--------------
           144

Elapsed: 00:00:00.00
rajesh@10GR2> begin
  2     dbms_output.put_line (' Utilized block number ');
  3
  4     for x in (select distinct dbms_rowid.rowid_block_number(rowid) as block_num from T)
  5     loop
  6             dbms_output.put_line (x.block_num);
  7     end loop;
  8
  9     delete from t;
 10  end;
 11  /

Utilized block number
78368
78369
PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03

rajesh@10GR2> select userenv('sid') from dual;

USERENV('SID')
--------------
           149

Elapsed: 00:00:00.01

rajesh@10GR2> begin
  2     dbms_output.put_line (' Before Insert Block Utilized ');
  3
  4     for x in (select distinct dbms_rowid.rowid_block_number(rowid) as block_num from T)
  5     loop
  6             dbms_output.put_line (x.block_num);
  7     end loop;
  8
  9
 10     insert into t
 11     select *
 12     from all_objects
 13     where rownum <= 100;
 14
 15     dbms_output.put_line (' After Insert Block Utilized ');
 16
 17     for x in (select distinct dbms_rowid.rowid_block_number(rowid) as block_num from T)
 18     loop
 19             dbms_output.put_line (x.block_num);
 20     end loop;
 21
 22  end;
 23  /

 Before Insert Block Utilized
78368
78369
 After Insert Block Utilized
78368
78369
78371
78372

PL/SQL procedure successfully completed.