Limiting Next Extent Size of table/index

[Former Member]

Hello Experts,

System details : ECC6 SP17 system (upgraded from 4.6C) and Oracle 10.2.0.4.

We have observed that table TST03 size is around 12GB and free space is around 94%. And when we checked the next extent size, it's 655MB, which seems to be very big.

Is there any way to limit the maximim extent size of all the tables/indexes to specified size (lets say 50MB). And what will be the pros and cons of doing so.

On what basis, oracle will decide the next extent size.

Thanks

Davinder

[Former Member]

Thanks for your inputs

[stefan_koehler]

Hello Davinder,

> On what basis, oracle will decide the next extent size.

The most important part is the kind of tablespace and the allocation type that is used.

To determine you can perform the following SELECTs.

shell> sqlplus / as sysdba
SQL> set linesize 500
SQL> select TABLESPACE_NAME, INITIAL_EXTENT, MAX_EXTENTS, NEXT_EXTENT, PCT_INCREASE 
     from ALL_TABLES where TABLE_NAME = 'TST03';
SQL> select TABLESPACE_NAME, BLOCK_SIZE, EXTENT_MANAGEMENT, ALLOCATION_TYPE 
     from DBA_TABLESPACES;

After you have identified the allocation type, you can think about how the extents are calculated. Please post the output of the SQLs from above and we can point you to the right documentation.

Regards

Stefan

[Former Member]

As this was upgraded, The tablespace in which TST03 is residing is not local and it is dictionary managed. That is the reason it is the next extent can be made so high.

Stefan query should give all the answers needed to analyze why.

Pravin

[Former Member]

Hello All,

Thank you for your time.

Here are the results of sql queries, please give your feedback.

SQL> select TABLESPACE_NAME, INITIAL_EXTENT, MAX_EXTENTS, NEXT_EXTENT, PCT_INCREASE from ALL_TABLES where TABLE_NAME = 'TST03';

TABLESPACE_NAME INITIAL_EXTENT MAX_EXTENTS NEXT_EXTENT PCT_INCREASE

-

---

-

---

-

---

-

---

-

---

PSAPBTABD 11960320 2147483645 83886080 0

SQL> select TABLESPACE_NAME, BLOCK_SIZE, EXTENT_MANAGEMENT, ALLOCATION_TYPE

from DBA_TABLESPACES;

TABLESPACE_NAME BLOCK_SIZE EXTENT_MAN ALLOCATIO

-

---

-

---

-

---

-

---

SYSTEM 8192 DICTIONARY USER

PSAPTEMP 8192 DICTIONARY USER

PSAPPOOLI 8192 DICTIONARY USER

PSAPDDICD 8192 DICTIONARY USER

PSAPDDICI 8192 DICTIONARY USER

PSAPUSER1I 8192 DICTIONARY USER

PSAPSTABD 8192 DICTIONARY USER

PSAPBTABI 8192 DICTIONARY USER

PSAPCLUI 8192 DICTIONARY USER

PSAPDOCUI 8192 DICTIONARY USER

PSAPLOADI 8192 DICTIONARY USER

TABLESPACE_NAME BLOCK_SIZE EXTENT_MAN ALLOCATIO

-

---

-

---

-

---

-

---

PSAPPROTI 8192 DICTIONARY USER

PSAPBTABD 8192 DICTIONARY USER

PSAPLOADD 8192 DICTIONARY USER

PSAPPROTD 8192 DICTIONARY USER

PSAPSOURCEI 8192 DICTIONARY USER

PSAPUSER1D 8192 DICTIONARY USER

PSAPCLUD 8192 DICTIONARY USER

PSAPSTABI 8192 DICTIONARY USER

PSAPPOOLD 8192 DICTIONARY USER

PSAPDOCUD 8192 DICTIONARY USER

PSAPSOURCED 8192 DICTIONARY USER

TABLESPACE_NAME BLOCK_SIZE EXTENT_MAN ALLOCATIO

-

---

-

---

-

---

-

---

PSAPES700I 8192 LOCAL SYSTEM

PSAPES700D 8192 LOCAL SYSTEM

PSAPUNDO 8192 LOCAL SYSTEM

SYSAUX 8192 LOCAL SYSTEM

PSAPEL700D 8192 LOCAL SYSTEM

PSAPEL700I 8192 LOCAL SYSTEM

I don't have much knowledge on Oracle, as per my understanding from the following results

1. Some of the tablespaces are DICTIONARY Managed and some are LOCAL - so can i assume that our system is running on DTMS.

2. Will there be any advantage of changing these to locally managed and howmuch efforts would be involved in that.

Thanks

Davinder

[stefan_koehler]

Hello Davinder,

thanks for the query output.

The table TST03 is located in the tablespace PSAPBTABD. And the tablespace itself is a DICTIONARY managed with USER allocation.

> 1. Some of the tablespaces are DICTIONARY Managed and some are LOCAL - so can i assume that our system is running on DTMS.

No, you can't say it that way. Some tables are located in a dictionary managed tablespace and some in a local managed tablespace. So your system is running within two different management types.

> 2. Will there be any advantage of changing these to locally managed and howmuch efforts would be involved in that.

Yes you will get a lot of advantages. You can read more about it in sapnote #214995. You need to create an additional tablespace which is locally managed and reorg your tables/indexes into this new one. This is the only way to get all the "new features" of a LMT. You can also do a DMT-to-LMT conversion, but then you still have the type "user allocation".

Just lets focus on your initial question quickly:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/clauses009.htm#i997450

PCT_INCREASE = 0 (= all extents after the first are the same size)

Regards

Stefan

[former_member204746]

read SAP note 646681 to reorg into a locally managed tablespace with ASSM.

[Former Member]

But SAP Notes are always there

Note 855187 - Maintaining the NEXT extents with BR*Tools

Note 403704 - BRCONNECT - Enhanced functions for Oracle DBA

Note 575522 - Reduction of NEXT extent sizes with BRCONNECT

[Former Member]

Davinder

http://www.arlati.ch/knowhow/archive/nextextent.pdf

http://help.sap.com/saphelp_nw04s/helpdata/EN/6e/e8e93a29768802e10000000a11402f/content.htm

http://searchsap.techtarget.com/tip/0,289483,sid21_gci918391,00.html

http://sap.ittoolbox.com/groups/technical-functional/sap-basis/reduce-next-extent-size-636854

thanks

Bhudev