Dropping indexes: urgenT
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Dropping indexes: urgenT

  1. #1
    Join Date
    Aug 2000
    Location
    Chicago IL
    Posts
    586

    Exclamation

    Hello,,
    I need to drop 10 indexes owned by fnd_concurrent_requests and when i try dropping these indexes i get an ora 054. Resource busy and aquire with nowait specified. How do i go about dropping them. Once i re-create them i will need to analyze them using
    ANALYZE TABLE FND_CONCURRENT_REQUESTS VALIDATE STRUCTURE CASCADE.

    THANKS
    Jigar

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    That error means that somebody is using that index. Query dba_locks to see which session is using that object and then kill that users session to free the locks (after the transaction rolls back).
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Aug 2000
    Location
    Chicago IL
    Posts
    586

    Exclamation

    Jeff,
    how do i figure out if that particular index is locked
    SQL> desc dba_locks;
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    SESSION_ID NUMBER
    LOCK_TYPE VARCHAR2(26)
    MODE_HELD VARCHAR2(40)
    MODE_REQUESTED VARCHAR2(40)
    LOCK_ID1 VARCHAR2(40)
    LOCK_ID2 VARCHAR2(40)
    LAST_CONVERT NUMBER
    BLOCKING_OTHERS VARCHAR2(40)

  4. #4
    Join Date
    Aug 2000
    Location
    Chicago IL
    Posts
    586
    i believe my indexes have contraints which i will need to disable to drop them.
    so i can do alter table fnd_concurrent_requests
    disable primary key ???

    then drop the indexes?

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Lemme take that back. DBA_DML_LOCKS and DBA_DDL_LOCKS would be better if you know the object name. Look at the NAME column.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width