Entering content frameProcedure documentation Viewing and Changing Storage Parameters Locate the document in its SAP Library structure

Use

You can view and change storage parameters using SAPDBA for Oracle:

Prerequisites

Procedure

  1. If you are performing this procedure directly from the reorganization menu, perform the following steps:
    1. Choose Reorganization ® Alter/show table or index storage parameters.
    2. Enter Owner and Table names.

      3. The owner is usually SAPR3 . You can use a wildcard to find the table or index, for example, by entering ACT% to find all tables or indexes beginning with the letters ACT .

    3. Select whether to use the ABAP dictionary to determine the size of the NEXT extent.

      4. For more information, see Default Value for NEXT.

    4. Choose Alter/show parameters to view the current parameter values.

SAPDBA displays the current , suggested , and new values. Note that you can only change the values marked with an option on the left-hand side.

Recommendation

We recommend that you only change the parameters PCTINCREASE , PCTFREE , PCTUSED , and FREELISTS in consultation with SAP, for example, if suggested by the EarlyWatch Service.

You cannot change all the parameters listed below if you are changing storage parameters directly from the reorganization menu.
  1. Change the parameter values as required, considering the following factors:

Note

When setting INITIAL and NEXT , it helps to check the space situation in the relevant tablespace. Refer to Checking Freespace for Tablespaces.

If you expect a table or index to grow quickly, it makes sense to increase INITIAL . The additional space in the initial extent enables the database system to add data without having to immediately create a new extent. When possible, define the extent large enough to cover the requirements of the table or index for some time.

You can reduce the initial extent if the table is static or shrinking in size. In this case, do not select the option to reduce the object size (ReduceOb) during a reorganization or export/import.

The suggested value for NEXT is calculated by SAPDBA. Refer to Default Value for NEXT for more information. Allow for the expected growth of the table or index when setting this value.

You can check for rapidly growing objects by Checking Freespace for Objects in Tablespace.

If the table is growing rapidly, it makes sense to increase NEXT . To anticipate this situation, regularly use the SAPDBA command line option sapdba -next . See sapdba -next <tablespace(s)>.

This is normally set to 1. It only needs to be greater than 1 if the table or index is so large that it does not fit into a single data file or the largest available freespace area.

We recommend that MAXEXTENTS is set to 100, with a block size of 8 KB. This is the SAP default, that is, the soft limit. Starting with Oracle 7.3, you can set MAXEXTENTS to UNLIMITED . If MAXEXTENTS is set to zero, this means UNLIMITED .

MAXEXTENTS is automatically reset to the SAPDBA default value when there is an import or a reorganization of the relevant object. This value is set to the default value even when you use one of the options for changing storage parameters manually.

Caution

If the table is approaching the MAXEXTENTS limits as described in Limitations of the Oracle Database System, you must reorganize it as soon as possible. If only the soft limit is reached, you can still increase MAXEXTENTS , but be sure to reorganize the table soon.

Only change these parameters in consultation with SAP or if you have extensive experience with Oracle database administration. You can simulate an asynchronous, linear PCTINCREASE response by using the command option sapdba -next . Refer to sapdba -next <tablespace(s)>.
  1. If you are performing this procedure for multiple objects – this is not possible if you are using Alter/show table or index storage parameters directly from the reorganization menu – select the following:
    1. next object

      2. Select this to set the parameters for the next object,

    2. name <%>

      3. Select this to restrict the display to objects beginning with a certain string. For example, enter ACT to view all objects beginning with the letters ACT.

    3. type

Select this to restrict the display to tables, indexes, or all objects.

SAPDBA uses the settings from the previous menu to reorganize any objects for which you do not explicitly assign storage parameters
  1. Choose Commit or continue reorganization to change the parameters to the values shown under new value.
Leaving content frame