How to change the next extent value for oracle

[Former Member]

Hi,

How to change the next extent value for a tablespace.Thanks in advance.

[stefan_koehler · Accepted solution]

Hello Ambarish,

do you use locally managed tablespaces (LMT)? If yes .. the NEXT attribute is obsolete.

You can check this with the following query:

SQL> SELECT TABLESPACE_NAME, EXTENT_MANAGEMENT, ALLOCATION_TYPE FROM DBA_TABLESPACES;

Check sapnote #599694 for more information about the extent allocation in a LMT with autoallocate.

Regards

Stefan

[Former Member · Accepted solution]

Output of the query

PSAPBTABD DICTIONARY USER

How to proceed further.as always response much appreciated.

[stefan_koehler · Accepted solution]

Hello Ambarish,

thanks for the output. You are using dictionary managed tablespaces .. so in this case the NEXT attribute is valid.

You can change it with the following SQL command

SQL> ALTER TABLESPACE PSAPBTABD DEFAULT STORAGE(NEXT <SIZE>);

But keep in mind that the default tablespace setting has no influence on the database objects that are created through SAP. The database objects are created with the definitions that are stored in the DDIC tables TAORA, IAORA, etc... You can take a look at those with SE16.

For more information about that - please check sapnote #666061 (Point " 12. How does the SAP system determine the tablespace and storage parameters for objects to be created?")

Regards

Stefan

[former_member204746 · Accepted solution]

you should run NEXT_EXTENT job in DB13 on a daily basis.

this will run:

brconnect -c -u / -f next

This job will evaluate size of next extent to a better value.

[Former Member · Accepted solution]

Hi Stefan,

After running the select query in DB02 will it show next extent as the size mentioned in the selet query.

[former_member524429 · Accepted solution]

Hi,

Please refer following SAP Notes to get more information.

[ Note 855187 - Maintaining the NEXT extents with BR*Tools|https://websmp130.sap-ag.de/sap%28bD1lbiZjPTAwMQ==%29/bc/bsp/spn/sapnotes/index2.htm?numm=855187]

Which Oracle version you are using ?

If possible, migrate from Dictionary Managed Table space (DMTS) to Locally Managed Table Space (LMTS) layout to get faster, efficient and less time consuming space management in Oracle.

[Note 214995 - Oracle locally managed tablespaces in the SAP environment|https://websmp130.sap-ag.de/sap%28bD1lbiZjPTAwMQ==%29/bc/bsp/spn/sapnotes/index2.htm?numm=214995]

[ Note 355771 - Oracle: Explanation of the new tablespace layout|https://websmp130.sap-ag.de/sap%28bD1lbiZjPTAwMQ==%29/bc/bsp/spn/sapnotes/index2.htm?numm=355771]

Regards,

Bhavik G. Shroff