Tablespace alter - warning

[mohsin_m3]

Hello All,

while alter tablespace " PSAPBTABD " I have got following warning message

BR1049W Not enough free space in /oracle/MBP/sapdata19 for total maximum size of all database files of tablespace PSAPTEMP located on this disk volume, missing at least 3414.427 MB

nlxpms08:orambp 15> df -k .

/oracle/MBP/sapdata19 (/dev/vgmbp_1/lvol40 ) : 46084144 total allocated Kb

6763656 free allocated Kb

39320488 used allocated Kb

85 % allocation used

nlxpms08:orambp 16>

please guide me .

Regards

Mohsin Mulani

[lbreddemann · Accepted solution]

> BR1049W Not enough free space in /oracle/MBP/sapdata19 for total maximum size of all database files of tablespace PSAPTEMP located on this disk volume, missing at least 3414.427 MB

Hi there. Please read the warning very carfully:

"Not enough free space in ... for total _maximum_ size of all database files"

Therefore it's likely that you set AUTOEXTEND ON for one or more files of this tablespace.

For each single datafile you can specify a maximum size until which the file can grow.

BRCONNECT now sums up the maximum size of all the datafiles for that tablespace and compares it to the current freespace of the filesystem the files are stored in.

So, review the AUTOEXTEND settings for the datafiles/tempfiles here.

E.g. I wouldn't recommend to set PSAPTEMP to AUTOEXTEND at all, since it can lead to a filesystem overflow just due to bad SQL. At least set a reasonable maximum size to prevent this.

regards,

Lars

[Former Member]

Hi again Mohsin,

The warning has to do with the sparse file property wich I described in my previous reply. Since 7.00 patch 20 the BR tools will check that a PSAPTEMP file is really able to grow to the allocated size. The warning means that this is impossible. With the figures you gave this could mean for instance that the PSAPTEMP in sapdata19 have a total configured size of 10 GB but are actually unallocated at this moment. Once they start allocating physical space they can use up only the 6.7 GB currently free in the file systems, but the remaining 3.3 GB the need to reach thir configured size is not available.

This issue is actually more serious than a warning about auto-extend, so you should take action asap. You could for example resize the tempfiles in your sapdata19 so that they cannot become larger than say 6 GB, and make up for that by creating additional PSAPTEMP files in another file system.

Regards,

Mark

[mohsin_m3]

Thanks for Help

[mohsin_m3]

Hello All,

I have checked In <DB02> and found AutoExt (kb) is off for PSAPTEMP

-

---

PSAPTEMP 20.725.760 20.725.760 0 0 0 Off 0 ONLINE NOT ACTIVE

-

---

kindly suggest me for further action

Regards

Mohsin

[Former Member]

Hi Mohsin,

This warning means that tablespace PSAPTEMP has AUTOEXTEND enabled for its files but not enough free space is left in the file system to allow those files to grow to their maximum size (MAXBYTES).

Example: assume the following (the figures are invented, they do not correspond to your specific case):

- PSAPTEMP has 2 files, each currently 2 GB in size

- The two files have AUTOEXTEND enabled and can grow to maximum 8 GB each

- The files are in a file system where there is 9 GB free space

If the PSAPTEMP files grew to their maximum size, they would occupy 16 GB (2x8), which is 12 GB more than now (2x2). However there is only 9 GB free, so the file system would run out of space before the files could ever reach their max size. This situation is detected by BRSPACE and that is why you get the warning. It is only a warning because nothing says that PSAPTEMP will ever autogrow to such a size.

Note that specifically for PSAPTEMP there is an extra risk: a temporary tablespace in Oracle allocates "sparse" files, i.e. only the first and last block is physically allocated. If at the O/S level the tempfile is shown as 2 GB in size, maybe the real size could be as low as just a few kilobytes. The space would only get allocated when it is really needed. This means that you should avoid having PSAPTEMP files on a file system where free space is very limited.

Hope this clarifies things,

Mark

[Former Member]

Hi,

the datafiles in your tablespace may consume more space (when they are full, they extend themselves) than there is available on the disk.

Limit the max. size of the datafiles and if needed create new datafiles on disk with more free space. The name of the tablespace is mentioned in the warning. If it's useful to let it grow so big is up to you.

Regards,

ulf