Monday, August 1, 2011

Part II: Detail Overview on INDEX in Oracle

In the previous article, we had discussed about “Index overview and some operation”. Now in this article, we will discuss about some more operation on the index of oracle database. The operations are as follows:

How to rename index?
How to drop index?
How to restore index?

How to Rename Index
: Name of index is very helpful for accessing an index. It is recommended to you that, choose an appropriate name for the index that describe the content of index. Suppose we have made some change in a index and want to the rename the index. We can do this by given below syntax.

ALTER INDEX index_name RENAME TO new_index_name; 
Example: ALTER INDEX stellar_employees RENAME TO stellar_data_recovery_employees;

Where Old name of index is stellar_employees and new name of index is stellar_data_recovery_employees.

How to DROP Index: Sometimes, we need to drop index. This can be done by the given below query.

DROP INDEX index_name;
Example: DROP INDEX stellar_data_recovery_employees;

Note: in this example, we are dropping the index stellar_data_recovery_employees.

How to Restore Index: Oracle database has introduced an advanced feature that is recycle bin. A database administrator can enable or disable the recycle bin. When the recycle bin is enable then it works like Microsoft windows recycle bin. An index can be drop by the query or by human mistake then you can restore it from the recycle bin because when you drop an index, it is not removed from database. It is placed in the recycle bin.

What happens when Recycle bin is disable???

Solution: When recycle bin of oracle database is disable then you are not able to restore drop index from the recycle bin. At this situation you can take help from any third party oracle database recovery program. The most of the third party data recovery programs are able to recover oracle database in this situation. 

No comments:

Post a Comment