Oracle Database Discussions

While creating index the session was killed so trying to drop it

SQL> drop index JSW_CUSTOM.JSWUS_MATL_RECEIVING_TEMP_N1;
drop index JSW_CUSTOM.JSWUS_MATL_RECEIVING_TEMP_N1
                      *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

did

SQL> DECLARE
RetVal BOOLEAN;
OBJECT_ID BINARY_INTEGER;
WAIT_FOR_LOCK BINARY_INTEGER;
BEGIN
OBJECT_ID := 1892315;
WAIT_FOR_LOCK := NULL;
RetVal := SYS.DBMS_REPAIR.ONLINE_INDEX_CLEAN (OBJECT_ID);
COMMIT;
END;
/  2    3    4    5    6    7    8    9   10   11

PL/SQL procedure successfully completed.

and then try to drop it shows same error

SQL> select o.object_id,t.table_name,l.ORACLE_USERNAME,OS_USER_NAME,
  2   DECODE(l.locked_mode
  3   , 0, 'None'
 , 1, 'Null'
  4    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 ='JSWUS_MATL_RECEIVING_TEMP_N1'
 13   and i.owner = 'JSW_CUSTOM'
 14   and o.owner= 'JSW_CUSTOM'
 15   and o.object_id = l.object_id
 16   and i.table_name=t.table_name;

 OBJECT_ID TABLE_NAME                     ORACLE_USERNAME                OS_USER_NAME                   Lock      PROCESS
---------- ------------------------------ ------------------------------ ------------------------------ --------- ------------
   1561410 JSWUS_MATL_RECEIVING_TEMP      APPS                           astlprd                        Row-X     14131

But  sql running in for the process doesnt use that index..

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4072829732

-------------------------------------------------------------------------------------------------
| Id  | Operation           | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                           |  7795 |   806K| 40131   (1)| 00:08:24 |
|   1 |  SORT AGGREGATE     |                           |     1 |    27 |            |          |
|*  2 |   TABLE ACCESS FULL | JSWUS_MATL_RECEIVING_TEMP |     1 |    27 | 40119   (1)| 00:08:24 |
|*  3 |  FILTER             |                           |       |       |            |          |
|   4 |   SORT GROUP BY     |                           |  7795 |   806K| 40131   (1)| 00:08:24 |
|*  5 |    TABLE ACCESS FULL| JSWUS_MATL_RECEIVING_TEMP |   155K|    15M| 40126   (1)| 00:08:24 |
-------------------------------------------------------------------------------------------------