Re: rescueing index

Raul Sanchez (rsanchez_at_lingocd.com) wrote:
: Hi,
:
: while loading in direct load state my index got locked after
: I kill the job. I killed the session by using the alter
: system utility. I managed to alter the system, but the index
: still remained in direct load state. How can I rescue the index
: without having to truncate or drop the table? Any ideas? Thanx
: in advance!
:
: --jchook :P

Don't drop or truncate the table! You can just rebuild the index. I don't know what version of Oracle you are using, so I will describe one method to rebuild an index:

1. Find out the columns of the index: SELECT COLUMN_NAME FROM USER_INDEXES WHERE INDEX_NAME = 'index_name';
2. Find out the storage parameters of the index: SELECT UNIQUENESS, TABLESPACE_NAME, INITIAL_EXENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE, PCT_FREE FROM USER_INDEXES WHERE INDEX_NAME = 'index_name';

From the above two steps, you can create a "CREATE INDEX" SQL statement. Drop the index and recreate it with your SQL statement.

-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 90+ Oracle tips, visit my Web Page:                       <->
<->                                                               <->
<->              http://homepage.interaccess.com/~akaplan         <->
<->                                                               <->
<->             email: akaplan_at_interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->