Home » RDBMS Server » Server Administration » URGENT HELP PLEASE - drop index hangs on SAP (Linux, Oracle10.2.02)
URGENT HELP PLEASE - drop index hangs on SAP [message #451327] Wed, 14 April 2010 01:15 Go to next message
newbie01.oracle
Messages: 14
Registered: December 2009
Junior Member
Hi all,

Need some urgent help advise please on how to get around this problem.

On a SAP system, am trying to drop six indexes, largest is 300MB and smallest is 50MB.

I tried running drop index sapusername.index_name on the 50MB index via SQL*Plus and it seems to be taking forever. Can anyone please suggest if there is anything I can check on the database on why it is taking such a long time?

I can leave it to run overnight but worried that when I come back the next day, it will still be hanged. Is there any quick way of dropping the index, .i.e. drop immediate ... Smile

Am not using SAP's BRTOOLs as it is also hanging from there and the SAP-ADMIN had approved for the DBA to drop it from our end instead.

Any response and advise on this will be very much appreciated.

Thanks in advance.

Report message to a moderator

Re: URGENT HELP PLEASE - drop index hangs on SAP [message #451334 is a reply to message #451327] Wed, 14 April 2010 01:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
URGENT help has to be requested to Oracle Corp. and payed for it.
Forum is help when posters have time.

There is nothing strange that creating indexes take hours for hundred million rows.
First check CPU and IO bottleneck at OS level.

Regards
Michel

[Updated on: Wed, 14 April 2010 01:37]

Report message to a moderator

Re: URGENT HELP PLEASE - drop index hangs on SAP [message #451337 is a reply to message #451327] Wed, 14 April 2010 01:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

For reference: same question posted at http://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=39953

Report message to a moderator

Re: URGENT HELP PLEASE - drop index hangs on SAP [message #451338 is a reply to message #451334] Wed, 14 April 2010 01:44 Go to previous messageGo to next message
newbie01.oracle
Messages: 14
Registered: December 2009
Junior Member
Hi,

Thanks for your response.

Already logged with Oracle Support via SAP ... which unfortunately is slower than getting responses or advise from the FORUM.

Anyway, from Google'ing, I found some that mentioned that the fault could be because of the existence of the constraints on the table that I am dropping the index of.

Checking the table, it has 17 SYS_ named constraints. None of these constraints have INDEX_NAME that refers to the indexes that am dropping.

Should I disable the constraints and then run the drop index again?

Will wait and see if I can get a response from Oracle Support

Report message to a moderator

Re: URGENT HELP PLEASE - drop index hangs on SAP [message #451339 is a reply to message #451338] Wed, 14 April 2010 01:46 Go to previous message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Should I disable the constraints and then run the drop index again?

No.

You cannot drop an index that is used to enforce a constraint, so if you already dropped the indexes this means they are not used by constraints.

Regards
Michel

Report message to a moderator

Previous Topic: High Load on DB Server
Next Topic: Shrink Space
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Jul 01 08:18:28 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.