ORACLE DRAMATICALLY AUTOEXTENDS DATAFILE

[former_member201910]

Good day!

Can you help me with this one?

I have 3 datafiles, all of them are set to autoextend on next 100MB, maxsize is 32GB.

Last october 28 in the morning, those 3 files are extended for a total of 15GB.

Therefore, each datafile was increased to 5GB. When I checked it Oct30, the usage has not grown that much (actually not at all) so how come will the datafile autoextend itself up to 5GB?

I already checked the increment size in Oracle enterprise manager and it says 100MB.

AND.. there are no manual extensions done here. Checked the logs already.

My questions are,

1. Where did that 5GB come from?

2. How does Oracle autoextend its datafile? I just want to know the process.

Fo example,

Autoextend=100MB, MAXSIZE=32GB

If there is a huge data of 15GB and Oracle has to extend the datafile until it has enough space. Does it extends 100MB for 150 times so it can fulfill 15GB? or it will extend 15GB for one time and neglect the 100MB setting for autoextend?

Please I need advices. Thank you!!

[JamesZ · Accepted solution]

Hi Maria,

Seems this behavior is talked in metalink:

AUTOEXTEND Grows To Full Size Without Reason (Doc ID 1459097.1)

The new 11g feature called tablespace extent pre-allocation.

You can request this document via SAP support incident or you have your own metalink account.

Best regards,
James

[former_member201910 · Accepted solution]

Good day James Zhang!

Thank you for your response.

I have read the document and is this mean that the behavior is normal for AUTOEXTEND

feature of Oracle 11G?

Regards,

Maria

[JamesZ · Accepted solution]

Hi Maria,

Good day and you are welcome.
This should be normal, also didn't find oracle has such "bug".

You can monitor the system whether this behavior occurs very often, on the other side, checking whether there is LARGE data size changed via SAP t-code db02.

If this behavior occurs very often and no large activity from db02, then we may consider this is a issue or bug.

Best regards,
James

[former_member201910 · Accepted solution]

Hi James,

I got it now. I already checked the current activity at Oracle Enterprise Manager and it clearly shows that used memory has not updated dramatically. If I am not mistaken, does "Oracle bigfile tablespace pre-allocation" thing explains this situation too? http://ksun-oracle.blogspot.jp/2015/12/oracle-bigfile-tablespace-pre.html

Regards,

Maria

[JamesZ · Accepted solution]

Hi Maria,

pre-allocation is not feature only for bigfile. However if you are using bigfile, then we this behavior makes more sense, are you using bigfile?

Best regards,
James

[former_member201910 · Accepted solution]

Hi James,

Thank you for your reply.

NO. Im not using bigfile.

Regards,

Maria

[JamesZ · Accepted solution]

Hi Maria,

Even though it is still like the pre-allocation feature, please keep monitoring.

Best regards,
James

[former_member201910]

HI James,

Okay. Got this already! Thanks for your help.

I see that there is such thing as

"_ENABLE_SPACE_PREALLOCATION"

and it is enabled. They call it a hidden parameter.

http://ksun-oracle.blogspot.jp/2015/12/oracle-bigfile-tablespace-pre.html

I understand whats happening now. Thanks for your help!

Have a good day!

Regards,

Jhiosa