Re: How does Oracle determine how much to extend autoextend datafiles?

Rich,

I don't know the answer to your question but we experienced very odd behavior on segments that had their segment header block in an autoextend datafile - where the datafile had reached its max size and autoextend on. While other datafiles in the same tablespace had plenty of space, Oracle continued to try to grab space in the full datafile. Turning off autoextend resolved that odd behavior.

I wanted to test it but I haven't had a.) time and b.) a good test case so I just have the general observations of what was happening at the time. It was exceptionally frustrating though while it was happening until I turned off autoextend on the datafiles for the tablespace holding the affected segments.

This was also in 12.1.0.2. I have a suspicion that something is 'off' about autoextend in 12.1 but nothing definite and nothing concrete to open an SR about.

Chris

On Thu, Mar 14, 2019 at 10:18 AM Rich J <rjoralist3_at_society.servebeer.com> wrote:

> Hey all,
>
> In 12.1.0.2, I have a LMT tablespace set to autoallocate with an 8K
> blocksize and 22 datafiles. Each datafile is set to initial 256M, extend
> 256M, autoextend to a max of 31GB. The contents are only heap tables with
> a few inline LOBs.
>
> Last night, Oracle decided to autoextend three of the datafiles that
> aren't maxed, a total of 124 times 256MB, which happens to equal 31GB.
> That's fine, except it only required less than 2GB, presumably by a table
> expanding.
>
> So, why did Oracle grab a total of 31GB -- suspiciously the exact same as
> my weird max size of each datafile -- when it only needed maybe 2GB?
>
> Thanks,
> Rich
>

--
http://www.freelists.org/webpage/oracle-l