OraFAQ Forum: Server Administration » ORA-01654: unable to extend index

Home » RDBMS Server » Server Administration » ORA-01654: unable to extend index (oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-01654: unable to extend index [message #593130]

Wed, 14 August 2013 08:15

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

Report message to a moderator

Re: ORA-01654: unable to extend index [message #593132 is a reply to message #593130]

Wed, 14 August 2013 08:21

joy_division
Messages: 4963
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.

Report message to a moderator

Re: ORA-01654: unable to extend index [message #593133 is a reply to message #593132]

Wed, 14 August 2013 08:24

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.

Report message to a moderator

Re: ORA-01654: unable to extend index [message #593136 is a reply to message #593133]

Wed, 14 August 2013 08:32

joy_division
Messages: 4963
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.

Report message to a moderator

Re: ORA-01654: unable to extend index [message #593137 is a reply to message #593133]

Wed, 14 August 2013 08:41

Lalit Kumar B
Messages: 3174
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 -

Code: [Select all] [Show/ hide]

$ 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

Code: [Select all] [Show/ hide]

kernel.shmall = 1024 * 1024 * 1024 * 64 / 4096 = 16777216

You can check whether it is correctly set by doing this -

Code: [Select all] [Show/ hide]

$cat /proc/sys/kernel/shmmax

If the above two values matches, then fine. Else change the kernel.shmall value as described above.

Report message to a moderator

Re: ORA-01654: unable to extend index [message #593148 is a reply to message #593137]

Wed, 14 August 2013 10:20

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.

Report message to a moderator

Previous Topic:	no of users logged on
Next Topic:	change Character set for oracle 8i databases

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jun 06 22:03:47 CDT 2025