| 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...
|
|
|
|
| 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.
|
|
|
|
|
|
|
|
| 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 -
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
