PCTUSED and PCTFREE usage

The PCTUSED parameter sets the minimum percentage of a block that can be used for row data plus overhead before new rows are added to the block. After a data block is filled to the limit determined by PCTFREE, Oracle considers the block unavailable for the insertion of new rows until the percentage of that block falls beneath the parameter PCTUSED

test@9IR2> select tablespace_name,SEGMENT_SPACE_MANAGEMENT
  2  from dba_tablespaces
  3  where SEGMENT_SPACE_MANAGEMENT = 'MANUAL'
  4  and tablespace_name ='DATA2'
  5  /

TABLESPACE_NAME                SEGMEN
------------------------------ ------
DATA2                          MANUAL

Elapsed: 00:00:00.01
test@9IR2> create table t (
  2     x number,
  3     y varchar2(4000),
  4     z varchar2(4000)
  5  ) tablespace DATA2 pctfree 30 pctused 40;

Table created.

Elapsed: 00:00:00.03
test@9IR2>
test@9IR2> select dbms_metadata.get_ddl('TABLE','T') from dual;

DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------

  CREATE TABLE "TEST"."T"
   (    "X" NUMBER,
        "Y" VARCHAR2(4000),
        "Z" VARCHAR2(4000)
   ) PCTFREE 30 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "DATA2"


Elapsed: 00:00:00.18
test@9IR2>
test@9IR2> exec show_space(user,'T','TABLE');
l_free_blks*******************  0
l_total_blocks****************  8
l_total_bytes*****************  65536
l_unused_blocks***************  7
l_unused_bytes****************  57344
l_last_used_extent_file_id****  12
l_last_used_extent_block_id***  696337
l_last_used_block*************  1

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

Now we have 8 blocks allocated to the Table, where 7 blocks are above HWM and 1 block contains actual Table data.

    * Now 1 block = 8kb (8192 bytes)
    * 20% PCTFREE is (8192 * 0.2) 1638 bytes
    * Total space available for data loading is 6553 bytes (8192 - 1638)
   
test@9IR2>
test@9IR2> insert into t values (1,rpad('*',4000,'*'), rpad('*',3000,'*'));

1 row created.

Elapsed: 00:00:00.00
test@9IR2> insert into t values (2,rpad('*',4000,'*'), rpad('*',3000,'*'));

1 row created.

Elapsed: 00:00:00.01
test@9IR2> insert into t values (3,rpad('*',4000,'*'), rpad('*',3000,'*'));

1 row created.

Elapsed: 00:00:00.00
test@9IR2> insert into t values (4,rpad('*',4000,'*'), rpad('*',3000,'*'));

1 row created.

Elapsed: 00:00:00.01
test@9IR2> insert into t values (5,rpad('*',4000,'*'), rpad('*',3000,'*'));

1 row created.

Elapsed: 00:00:00.00
test@9IR2>
test@9IR2> commit;

Commit complete.

Elapsed: 00:00:00.01
test@9IR2>
test@9IR2> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
test@9IR2> exec show_space(user,'T','TABLE');
l_free_blks*******************  1
l_total_blocks****************  8
l_total_bytes*****************  65536
l_unused_blocks***************  2
l_unused_bytes****************  16384
l_last_used_extent_file_id****  12
l_last_used_extent_block_id***  696337
l_last_used_block*************  6

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03

Now we have 2 blocks are above HWM and 6 block contains actual Table data. If the free space available in the block drops below the PCTUSED (3276 kb) of data, then In this case, a data block used for this table's data segment is considered available for any new datas. ( data block will be available to Free list)

test@9IR2>
test@9IR2>
test@9IR2> update t
  2  set y = rpad('*',100,'*')
  3  where x = 1;

1 row updated.

Elapsed: 00:00:00.00
test@9IR2>
test@9IR2>
test@9IR2> commit;

Commit complete.

Elapsed: 00:00:00.01
test@9IR2> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
test@9IR2> exec show_space(user,'T','TABLE');
l_free_blks*******************  2
l_total_blocks****************  8
l_total_bytes*****************  65536
l_unused_blocks***************  2
l_unused_bytes****************  16384
l_last_used_extent_file_id****  12
l_last_used_extent_block_id***  696337
l_last_used_block*************  6

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03
test@9IR2>
test@9IR2>