Home » RDBMS Server » Server Administration » Disable Oracle Index
Disable Oracle Index [message #61708] Wed, 19 May 2004 04:10 Go to next message
vishal goya;
Messages: 2
Registered: May 2004
Junior Member
How to disable an oracle index
Re: Disable Oracle Index [message #61710 is a reply to message #61708] Wed, 19 May 2004 08:40 Go to previous messageGo to next message
croca
Messages: 50
Registered: January 2004
Member
If it is a primary key or unique constraint, you can
ALTER TABLE name DISABLE PRIMARY KEY

You can enable later, you can enable it NOVALIDATE so oracle will not validate data already inserted in the table, but will validate new inserted rows. This way you can save time thus oracle will 'recrete' the index transparently for those new records.

if it is a simple index you will have to drop it. You will loose the index and will have to recreate it if you need it.

Best luck.
Using RMAN backups?, try RMV from www.otools.com.ar
Re: Disable Oracle Index [message #61715 is a reply to message #61708] Wed, 19 May 2004 21:56 Go to previous messageGo to next message
Trifon Anguelov
Messages: 514
Registered: June 2002
Senior Member
You can disable a constraint, since it's a logical database structure.

Indexes are physical structures - they contain rowids of the table rows. To disable a physical structure - you have to destroy it. In the case of the indexes:

SQL> drop index user.index-name;

Hope that helps,

clio_usa - OCP 8/8i/9i DBA

Oracle resources | Oracle DBA Forums | USENET Oracle newsgroups
Re: Disable Oracle Index [message #61717 is a reply to message #61708] Wed, 19 May 2004 22:08 Go to previous message
Daljit Singh
Messages: 290
Registered: October 2003
Location: Texas
Senior Member
Hi,

Well according to me there is no need to drop the entire index, we can simply disable an index by issuing the following command :

ALTER INDEX IND_POL_ID UNUSABLE;

After that when we want to enable the index than we have to recreate the index that we can do using following command :

ALTER INDEX IND_POL_ID REBUILD;

Daljit Singh
Previous Topic: How to recover a Redo.log file
Next Topic: ORL9i & transaction logs
Goto Forum:
  


Current Time: Wed Jan 08 16:09:25 CST 2025