Skip to Content
0

ORACLE DRAMATICALLY AUTOEXTENDS DATAFILE

Nov 09, 2017 at 06:37 AM

97

avatar image

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!!

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
James Zhang
Nov 10, 2017 at 01:50 AM
0

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

Show 6 Share
10 |10000 characters needed characters left characters exceeded

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

0

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

1

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

0

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

0

Hi James,

Thank you for your reply.

NO. Im not using bigfile.

Regards,

Maria

0

Hi Maria,

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

Best regards,
James

0
Maria Jhiosa Vergara Nov 14, 2017 at 01:56 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded