ora-01654 error

[Former Member]

Hi all,

We are getting this following short dump in our system.

ora-01654 unable to extend index sapsr3.d010inc~1 by 8192 in the tablespace psapsr3701.

When i checked in the db02 the tablespace was having sufficient space.I tried to add another data file to it.

I was getting the below error then.

ORA-01119: error in creating database file

'/oracle/DS3/sapdata1/sr3701_9/sr3701.data9'

ORA-27040: file create error, unable to create file

Could someone help us in understanding the issue.Thanks.

[Former Member · Accepted solution]

Hi,

ORA-01119: error in creating database file
'/oracle/DS3/sapdata1/sr3701_9/sr3701.data9'
ORA-27040: file create error, unable to create file

The issue seems to be because of permission. Make sure that u have required permission for the file system '/oracle/DS3/sapdata<n>'.

Regards,

Varadharajan M

[Former Member · Accepted solution]

Hi Varadhu,

Have checked the permissions everything seems to be fine at the first glance.

drwxrwxrwx 32 ora<sid> dba 4096 Sep 21 15:49 sapdata1

Have checked the space for the filesystem also and there is enough space in it.Thanks.

[Former Member · Accepted solution]

Hi,

Eventhough u seem to have enough space at the OS, the actual disk space might have been occupied if the datafiles are set to auto extend. Try to turn off the auto extend so that u can gain some space at the OS level. Then try to extend the tablespace by adding a new datafile.

Steps to turn off the auto extend.

Login to the server as ora<SID>

Execute BRTools

2 - Space Management
5 - Alter data file
c - cont
c - cont
4 - Turn off autoextend
Select the datafile for which u want to turn off the tablespace by entering the corresponding number(for example 1).
c - cont
c - cont

Repeat the steps mentioned above till u gain space at the OS level.

Hope this will resolve ur issue.

Regards,

Varadharajan M

[Former Member · Accepted solution]

The issue has been resolved.I was able to add the data file using brspace commands from orasid.

But i had one doubt with what Varadh had said.

When we add a datafile with autoextend on does that mean the data file size would correspond to the initial size and not the maximum size.Thanks.

[Former Member · Accepted solution]

Hi,

Whenever a new datafile is added with autoextend "ON", a file will be generated with the size specified and not the max size. But some space (specified for the next extend) will also be occupied at the OS level but that will not be visible(meaning, u could see free space at the OS level but actually it was occupied by the datafiles) and when the entire disk space is occupied, u will not be able to add a new datafile or the DB will not be able to extend the existing datafiles eventhough there seems to be some free space available at the OS level.

Hope this clarifies.

Mark this thread as answered as ur issue got resolved.