Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1326
  • Last Modified:

Problem Dropping the index

Hello OracleDBAs,
Iam trying to drop an index defined on a table.It gives me the following error.
"ORA-00054: resource busy and acquire with NOWAIT specified"
Is there any way I can drop the index forcefully...so that I can proceed with my work.
Please help me..
Thanks in advance.
sridhar
0
sridhar_don
Asked:
sridhar_don
1 Solution
 
catchmeifuwantCommented:
It means the index is locked for some other purpose by oracle(probably a previous drop command is still running).

Wait from sometime and try the drop index command again...
0
 
cjjcliffordCommented:
catchmeifuwant is correct that the table is currently locked for some other reason... waiting may help you, but better yet, before attempting the DROP INDEX perform the following:

LOCK TABLE table_name IN EXCLUSIVE MODE;

This command obtains an exclusive lock on the table, which is freed after a single command (in your case the index drop) - this LOCK TABLE will wait until it can obtain the lock, which may take some time....

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
You might try to check WHO locks your table:
v$locked_object
Who is locking what:
select
  oracle_username
  os_user_name,
  locked_mode,
  object_name,
  object_type
from
  v$locked_object a,dba_objects b
where
  a.object_id = b.object_id

CHeers

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now