2 Replies Latest reply: Apr 1, 2013 10:37 AM by bobmagan RSS

    Parameters for Table containing only inserts

    bobmagan
      I'm on 11.2 DB and need to create an audit table that will be populated by DB triggers of other tables (after Insert,Update and Delete). The triggers will only ever be inserting data into the audit table. I have read that for insert only tables, you should define the 'pctfree' as 0. Is this correct? Do I need to set any other params (like pct_used) for tables only ever being inserted into?

      Thanks
        • 1. Re: Parameters for Table containing only inserts
          rp0428
          >
          I'm on 11.2 DB and need to create an audit table that will be populated by DB triggers of other tables (after Insert,Update and Delete). The triggers will only ever be inserting data into the audit table. I have read that for insert only tables, you should define the 'pctfree' as 0. Is this correct?
          >
          Yes - there is no need to reserver space for future updates that might expand the row if there will be no future updates.

          >
          The PCTFREE Parameter

          The PCTFREE parameter sets the minimum percentage of a data block to be reserved as free space for possible updates to rows that already exist in that block.
          >
          http://docs.oracle.com/cd/B28359_01/server.111/b28318/logical.htm#i19164
          >
          Do I need to set any other params (like pct_used) for tables only ever being inserted into?
          >
          No - you will not be deleting rows.
          >
          The PCTUSED Parameter

          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 Database considers the block unavailable for the insertion of new rows until the percentage of that block falls beneath the parameter PCTUSED. Until this value is achieved, Oracle Database uses the free space of the data block only for updates to rows already contained in the data block.