Oracle performance adjustment and recovery of tablespace fragments

The following articles mainly introduce Oracle performance adjustment and several methods to reclaim tablespace fragments. First, we need to use the compress = Y parameter exp for the relevant tablespace, after truncate the table, import the table imp. This is the first step,

2. Use

 
 


  
  
Alter tablespace name coalesce;

 
 

Manually contract once.

3. Change the pctincreace parameter of the tablespace to greater than 0 to make it automatically contract. Generally, it is changed to 1.

 
 


  
  
Alter tablespace name storage (pctincrease 1 );

 
 

About tablespace pctincrease = 1:

1) do not set pctincrease to 0 because the SMON process will only periodically merge idle chunks of tablespaces not equal to 0 in PCTINCREASE. If it is set to 0, SMON cannot merge idle fragments. In this case, you must manually use alter tablespace xxx coalesce to merge fragments.

2) do not set PCTINCREASE To a large number. It is only used to prevent the newly created table from using a large PCTINCREASE and cause uncontrollable space usage. If you no longer need to create a new table in the tablespace, this parameter will not be affected as long as it is not equal to 0.

In addition, PCTINCREASE has nothing to do with the automatic growth of tablespaces. The automatic growth of tablespaces is controlled by autoextend on, and it is purely linear growth, that is, the specified growth, it will always increase at a time). Unlike the PCTINCREASE of a table, it may lead to geometric growth.

The content above is an introduction to how to adjust Oracle performance and how to recycle tablespace fragments.