Oracle Pctfree and pctused detailed

First, when establishing the table, pay attention to the role of pctfree parameters

PCTFREE: The percentage of space reserved for a block, indicating when the data block can be insert, the default is 10, indicating that when the free space of the block is less than 10%, it cannot be insert and can only be used for update, i.e. when a block is used, The block is always able to be inserted before reaching Pctfree, which is at an ascending stage.

Pctused: Refers to the amount of data in the block is less than the percentage, but also can be re-insert, the general default is 40, that is, 40%, that is: when the data is less than 40%, you can write new data, this time in the fall period.

Ii. Examples and explanations

Suppose you have a block can hold 100 data, and Pctfree is 10,pctused is 40, then: constantly inserting data into the block, if stored to 90, it is not possible to store new data, which is controlled by the Pctfree, reserved space for the update.

When you delete a data, do you want to insert a new data line? No, must be deleted 41, that is, less than 40 after the new data can be inserted, which is controlled by pctused.

Note: If ASSM is enabled on the tablespace, you can specify only pctfree when establishing the table, otherwise you can specify Pctfree and pctused.

Third, adjust the pctfree and pctused
1, through the User_tables pct_free,pct_used to view
Select A.table_name, A.pct_free, a.pct_used, a.* from User_tables A;
Description
Pctfree represents the percentage that is used to retain the update operation and cannot insert data if it exceeds the value.
Pctused represents the minimum percentage of data that cannot be inserted if Pctfree is reached, and if pctused is reached after the delete, it can be used to insert

2, if you are using the Automatic management table space pctused do not need to set
altertable tablename Pctfree values;
Description
Pctfree default is 10, mainly look at the size of the updated data, you can view the table Max_row_len if large and frequent updates you can consider increasing this value.
Pctused mainly look at the size of the deleted data, if it is large can be resized to the value, if not very frequently can be set a little bit 30-40
Pctused+pctfree<90

3. View automatic management of table spaces
Select Tablespace_name,segment_space_management from User_tablespaces; --segment_space_management automatically manages tablespace for auto
Mainly play a role in saving the table space.

Oracle Pctfree and pctused detailed