Home » RDBMS Server » Server Administration » ORA-01654: unable to extend index (oracle 11g)
ORA-01654: unable to extend index [message #593130] Wed, 14 August 2013 08:15 Go to next message
oracle_doubt
Messages: 96
Registered: December 2012
Location: CHENNAI
Member
Hi all,
We met unable to extend index exception.I have executed below mentioned query.
ERROR:ORA-01654: index
(128, tablespace USERS) You can not extend the OMS_SG_IT.TRN_BD_MNTHLY_OCF_SLT_PKC.

QUERY:

select db.FILE_NAME,
db.AUTOEXTENSIBLE,
db.INCREMENT_BY,
db.USER_BLOCKS,
t.INITIAL_EXTENT,
t.NEXT_EXTENT,
t.EXTENT_MANAGEMENT
FROM dba_data_files db,
dba_tablespaces t
where db.TABLESPACE_NAME = t.TABLESPACE_NAME
and t.TABLESPACE_NAME = 'USERS';

RESULT:
/u01/app/oracle/oradata/SCSKTRA/users01.dbf
YES
5
3872
65536
(null)
LOCAL


ANOTHER QUERY:
select tablespace_name,file_name,bytes/1024/1024 from dba_data_files where tablespace_name='USERS';

RESULT:

USERS
/u01/app/oracle/oradata/SCSKTRA/users01.dbf
31.25


Now what i have to do...
Re: ORA-01654: unable to extend index [message #593132 is a reply to message #593130] Wed, 14 August 2013 08:21 Go to previous messageGo to next message
joy_division
Messages: 4490
Registered: February 2005
Location: East Coast USA
Senior Member
SQL> !oerr ora 1654
01654, 00000, "unable to extend index %s.%s by %s in tablespace %s"
// *Cause: Failed to allocate an extent for index segment in tablespace.
// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
// files to the tablespace indicated.
Re: ORA-01654: unable to extend index [message #593133 is a reply to message #593132] Wed, 14 August 2013 08:24 Go to previous messageGo to next message
oracle_doubt
Messages: 96
Registered: December 2012
Location: CHENNAI
Member
Hi joy,
Thanks.
But i tried
alter database datafile '/u01/app/oracle/oradata/SCSKTRA/users01.dbf' resize 1500M;

it shows
linux-x86_64 error28:no space left on device.
Re: ORA-01654: unable to extend index [message #593136 is a reply to message #593133] Wed, 14 August 2013 08:32 Go to previous messageGo to next message
joy_division
Messages: 4490
Registered: February 2005
Location: East Coast USA
Senior Member
That is an OS message. You have no space left on the chosen filesystem. Add a new file to another filesystem.
Re: ORA-01654: unable to extend index [message #593137 is a reply to message #593133] Wed, 14 August 2013 08:41 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1874
Registered: May 2013
Location: World Wide on the Web
Senior Member
siraj.accet@gmail.com wrote on Wed, 14 August 2013 18:54

linux-x86_64 error28:no space left on device.


It could also be that kernel.shmall value is incorrectly calculated. Your system is Linux x86 with 64 bit processor.

Can you find this value -
$ getconf PAGE_SIZE


As per Oracle SHMALL should be set to the total amount of physical RAM divided by page size.

For example, if your system has 64GB memory, so change
kernel.shmall = 1024 * 1024 * 1024 * 64 / 4096 = 16777216


You can check whether it is correctly set by doing this -
$cat /proc/sys/kernel/shmmax


If the above two values matches, then fine. Else change the kernel.shmall value as described above.
Re: ORA-01654: unable to extend index [message #593148 is a reply to message #593137] Wed, 14 August 2013 10:20 Go to previous message
oracle_doubt
Messages: 96
Registered: December 2012
Location: CHENNAI
Member
Thanks lalit,joy

We are going to increase our disc space and increased data file size to 100M.
Previous Topic: no of users logged on
Next Topic: change Character set for oracle 8i databases
Goto Forum:
  


Current Time: Thu Jul 31 05:42:02 CDT 2014

Total time taken to generate the page: 0.15973 seconds