Resize of oracle sys.AUD$ does not stay permanent on 7.3.4

[Jack C. Mahaffey]

Don't know if anybody else has encountered this but I resized the sys.AUD$ table last week on multiple servers only to check today and see that next extent settings to be different and no where close to what they were set to.

We run a nightly report from the table and then truncate the table. I suspect the change happens when the table is truncated.

If anybody has a handle on what resets the storage settings for next extent, let me know. Table filled up last week, thus the reason for increasing the next extent size.

Jack...

[Patti Johnson]

Jack,

Not sure if I'm reading your question correctly. Are you saying the the next_extent size that you defined for the table is changing? Or that the next extent that you allocated has gone away.

Truncate table without the REUSE STORAGE clause will release all but the initial extent.

If that's not the case is it possible that your script is dropping and recreating the table?

Patti

[Jack C. Mahaffey]

I'm just using the truncate table table without any additional arguments. I'm setting the next extent size only. I'm not recreating the table. This is really weird.

I found a article on the truncate concern and it appears to be normal to resize the next extent.

"The tableâ s storage parameter NEXT is changed to be the size of the last extent
deleted from the segment in the process of truncation."

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1222036354881

Learn something every day.
jack

[Indira Aramandla · Accepted solution]

Hi Jack,

When you truncate a table the "Truncate" command uses the DROP STORAGE clause which keeps 'minextents' extents, thus only 1 extent.

So the storage parameter next extent of the table goes back to the default definition.

I had this issue too. So what you do is truncate the table then increate the next extent parameter using alter table storage (next 9999);. This will keep the next extents storage to what you set when you start population the table again.


Indira A