ORA-04000

I created a table in an Oracle 11.2 database. I did not specify pctfree or pctused so it was given the defaults of 10% and 40% respectively:

SQL> conn system/manager
Connected.
SQL> create table t1 (c1 number)
  2  /

Table created.

SQL> select pct_free, pct_used
  2  from user_tables
  3  where table_name = 'T1'
  4  /

  PCT_FREE   PCT_USED
---------- ----------
        10         40

SQL>

When Oracle inserts rows into a table, the pctfree specifies the percentage of space to leave free in each block for subsequent updates to the rows. This free space is used later if an extra column is added to the table or if a varchar2 column is updated to store a longer value than before. Once the pctfree in a given block falls below the specified value, no new rows can be inserted in that block so it is removed from the free list. It is then not allowed to accept new rows until the percentage of space used in the block falls below the pctused figure. When this happens, the block goes back onto the free list again. You can alter the pctfree and/or pctused settings like this:

SQL> alter table t1 pctfree 20
  2  /

Table altered.

SQL>

Going through some old notes from an Oracle 9 performance tuning course, I read that the sum of pctfree and pctused cannot be more than 100. This seemed reasonable. If you had a pctused of 40%, a pctfree of 70% and a block which was 35% full, Oracle would not know what to do with it. The pctused figure of 40% would tell Oracle to leave the block on the free list wheras the pctfree figure of 70% would tell Oracle to remove it (from the free list). In situations like this, Oracle usually has a special error message to display. In this case, it is ORA-04000, as you can see below:

SQL> alter table t1 pctfree 70
  2  /
alter table t1 pctfree 70
*
ERROR at line 1:
ORA-04000: the sum of PCTUSED and PCTFREE cannot
exceed 100

SQL>