Re: nextexent issue

On Fri, 18 Jul 2003 10:54:36 -0700, Daniel Morgan wrote:

>Mark D Powell wrote:
>
>> jamesyang_at_163.net (James) wrote in message news:<86815f22.0307171911.342eaf84_at_posting.google.com>...
>> > We are running SAP based on Oracle database.
>> >
>> > There is table which is increased very quickly. The backgroud
>> > statistic job calculated/updated the nextextent to 2G but the problem
>> > is the maximum data file size is 2G and we can not ensure the free
>> > space is continuous for the next exntent of this table. We can not
>> > extent the table except change the nextextent parameter, example to
>> > 500M, manually.

You don't have to let SAP recalculate the extents. Don't use this tool.
Use only some size for the extents (by example : 64KB, 4MB, 64MB) and resize them manually. If you use a lot of different size, your tablespace will be fragmented and you will lose a lot of space.

>> > So my question is:
>> >
>> > Can I set the parameter's property to "no changeable", sure can be
>> > changed under some condition?
>> >
>> > If somebody know SAP very well, please advise can we check/update all
>> > the objects except this table?
>> >
>> > Thanks
>> > jamesyang_at_163.net
>>
>> James, I would like to suggest you consider rebuilding the Oracle
>> tablespaces used to support your SAP application to being locally
>> managed with uniform extents.

I agree for using locally managed tablespace but with the autoallocate clause.
The uniform clause will be a nightmare with SAP. There are several thousands tables. Most are ridiculous small. Some are very very big. And it is very hard to know which one will get big.

I've created a SAP database which is in production since 2 months. I've made all tablespace locally managed with autoallocate extents.

There is actually around 150 GB of data. I looked at the free space in each tablespace. Sometimes, I look the very big table. If the table get bigger than 4 GB, I move it in a dedicated tablespace locally managed with uniform extent.

When I compare the work I have to do with the time spent with the Test Database which was dictionnary managed, it's night and day. And the risk of failure is greatly reduced.

no more maxextents, no more segments with thousand extents.

Take the time to switch your tablespace in locally managed with autoallocate extents, you will not regret it.

Look at the SAP web site, locally managed tablespace are not only supported but recommanded. Received on Fri Jul 18 2003 - 15:31:58 CDT