Failed to modify index's next extent size

[samc]

Hi all,
I am getting the below problem when i try to reduce the next extent size from 2050M to 1500M. How can I find out what is the process locking the index? TQ

SQL> alter index test.test_new storage (next 1500M pctincrease 0);
alter index test.test_new storage (next 1500M pctincrease 0)
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

[Thomasps]

SQL> alter index test_ind rebuild;
alter index test_ind rebuild
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified


col object_name for a20
col oracle_username for a10
col lock for a10
col tale_name for a10
col os_user_name for a10


SQL> 
  1  select o.object_id,t.table_name,l.ORACLE_USERNAME,OS_USER_NAME,
  2  DECODE(l.locked_mode
  3               , 0, 'None'
  4               , 1, 'Null'
  5               , 2, 'Row-S'
  6               , 3, 'Row-X'
  7               , 4, 'Share'
  8               , 5, 'S/Row-X'
  9               , 6, 'Exclusive')  "Lock",l.process
 10  from dba_indexes i,dba_objects o,v$locked_object l,dba_tables t
 11  where o.object_name = t.table_name
 12  and i.index_name ='TEST_IND'
 13  and i.owner = 'SCOTT'
 14  and o.owner= 'SCOTT'
 15  and o.object_id = l.object_id
 16* and i.table_name=t.table_name
SQL> /

 OBJECT_ID TABLE_NAME                     ORACLE_USE OS_USER_NA Lock       PROCESS
---------- ------------------------------ ---------- ---------- ---------- ------------
     30343 TEST                           SCOTT      SaviourT   Row-X      820:624

[samc]

Thanks Thomas, is the any other way to get rid of the error beside to kill the user process? tq

[adewri]

Ask the user who has acquired the lock to commit or rollback.

[Thomasps]

Originally posted by adewri
Ask the user who has acquired the lock to commit or rollback.

Exactly true. And also try with ONLINE key word so will not report an error, but will wait. Rebuilding will start only when there are no locks on the table. RTFM for further

[Thomasps]

Info:
When I was doing a test got the message

SQL> alter index test_ind rebuild online;
alter index test_ind rebuild online
*
ERROR at line 1:
ORA-08106: can not create journal table SCOTT.SYS_JOURNAL_30357

This is a bug in 9.2 and fixed in 10g. Droped the journal table.

SQL> drop table SYS_JOURNAL_30357 ;

Table dropped.

SQL> alter index test_ind rebuild online;

Index altered.