Need inputs on the pros and cons of setting unlimited extents

[Former Member]

Hi,

We are planning to set unlimited extents on all the DB tables inorder to avoid the frequent alerts and errors we get for TOO MANY EXTENTS. Now we are manually doing the changes in SE14.

But we hava a proposal of setting unlimited extents for all tables which we think will solve the issue.

Kindly provide our valuable feedback on this. Is it advisable or not?

SAP Version : 4.6C

Oracle : 10.2

OS - AIX UNIX

Thanks

Anupama

[Former Member · Accepted solution]

As you have 10.x you might be using LMT already. As suggsted in the ealier post in this case you do not need to worry. Could you please specify what/where errors you see regarding this?

[Former Member · Accepted solution]

Hi Manoj,

The errors we get are like below :

BR0970W Database administration alert - level: WARNING, type: TOO_MANY_EXTENTS, object: (table) SAPR3.AFPO, value: 92.29% (> 90%)

In this we normally goto SE14 and increase extent value for that particular table.

Anupama

[lbreddemann · Accepted solution]

Hmm.. when I enter "TOO_MANY_EXTENTS" into the search box in the upper right corner of this forum I get back this:

SAP Note 435290 Description of BRCONNECT 6.10 DB check conditions

And in there I find:

"16. TOO_MANY_EXTENTS (formerly MANY_EXTENTS)

----

---

This condition checks whether there are tables or indices for which the number of allocated extents exceeds the specified threshold value. As a default, all tables and indexes are checked against the same threshold value. However, you can define different threshold values for individual segments or tablespaces by specifying their names in the OBJECT field of the DBCHECKORA table (transaction DB17).

Possible checking operands, threshold values, value units:

16a. >, >= <number> P

More than <number> % of the maximum number of extents was already allocated.

16b. >, >= <number> <none>

More than <number> extents were already allocated

16c. <, <= <number> P

Fewer than <number> % of the maximum number of extents can still be allocated.

16b. <, <= <number> <none>

Fewer than <number> extents can still be allocated.

Note: The check condition TOO_MANY_EXTENTS has only an informative role for locally managed tablespaces (LMTS), where the max extents attribute is set to UNLIMITED and the next extents size is determined automatically by Oracle. As a result, the number of extents is no longer a critical factor for LMTS."

Given that you've performed a proper notes search yourself before posting this question I really wonder, why you did not find this information...

So, to finally answer your question: just put the MAXEXTENTS value to UNLIMITED or reorganize the tables/indexes into a locally managed tablespace.

And please start reading the DBA documenation for SAP on Oracle!

regards,

Lars

[Former Member · Accepted solution]

Thanks for replying Lars.

This was useful. I agree many a times the terms we enter to search matters on what results we get.

I shall take care of this in future

One last question, excuse my ignorance here please.. Is there any wrong effect in making this setting?

Regards,

Anupama

[lbreddemann · Accepted solution]

> One last question, excuse my ignorance here please.. Is there any wrong effect in making this setting?

There's no risk in setting MAXEXTENTS to UNLIMITED.

However, usually the warning itself indicates, that the NEXT extent size was not configured correctly (too small for the amount of data in the table).

With LMTS you don't have to care of this as the next extent size will automatically be adapted to the growth of the segment.

With DMTS you have to run brconnect -f next regularly to make this adaption of the next extent parameter.

regards, Lars

[Former Member · Accepted solution]

Thank you Lars

[stefan_koehler]

Hello Anupama,

i wonder .. are you still using "dictionary managed tablespaces"?

If you would use LMT (locally managed tablespaces) ... MAXEXTENTS is no topic anymore, because you can't specify it.

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

Regards

Stefan

[Former Member]

Thanks for the reply Stefan..

Kindly provide me some more inputs as to how do we go about making the changes to LMT, as I am not a DB expert?

or is it fine to keep maintain same DMT and set extents to unlimited?

Which wil you advise?

Regards,

Anupama.

[Former Member]

Hi Anupama,

It is recommended to migrate to LMTS because of the advantages offered.

To migrate you can use the DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL package

SQL> execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('&tablespace_name');

See this

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_spadmn.htm

award points if helpful

Thanks,

Salman

[lbreddemann]

>

> Hi Anupama,
> 
> It is recommended to migrate to LMTS because of the advantages offered.
> To migrate you can use the DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL package
> award points if helpful
> 
> Thanks,
> Salman

Great Salman!

You can google the documentation and you're brave enough to recommend the only way of migrating DMTS to LMTS that IS NOT SUPPORTED by SAP.

Do you even understand what this procedure does and what the resulting tablespace looks like?

Automatic Extent-Managent? Not there with you're solution!

Please post at least the sap note on this issue, if you really think that reposting documentation is the right thing for this forum...

Lars

[Former Member]

My Dear Lars,

This is what I hate about this forum......"BIG BROTHER ATTITUDE"

Can't you be more polite in putting your point across?

You say "Do you even understand what this procedure does and what the resulting tablespace looks like?"

• DO YOU? *

Also you say "You can google the documentation and you're brave enough ......." Oh! you assume a lot dear.....

And yes, you are the developer of SAP & ORACLE

Also, yes this option is not recommended by SAP, Thanks for sharing your absolute knowledge

Salman

Edited by: Salman M.A. Qayyum on Feb 22, 2011 6:53 AM

[lbreddemann]

Sorry if my post was too harsh - it was not meant to insult you.

But simply re-posting documentation links isn't the kind of participation wanted in the SDN forums.

You may want to check the [rules of engagement|https://wiki.sdn.sap.com/wiki/display/HOME/RulesofEngagement] on this.

If you would have posted about your own experience with this and how it works, this would have been a completely different quality of answer.

But just posting a link you've found and also asking for contribution points is not OK.

In fact posts like this pull down the quality of these forums a lot and personally p*** me off.

I'd like to see more posts from you (and everybody else, of course), based on experience and knowledge and without the "gimme-points" sentence.

regards,

Lars