Subject: Re: rescueing index
Newsgroups: comp.databases.oracle.misc,comp.databases.oracle.server,comp.databases.oracle.tools,comp.databases
Followup-To: comp.databases.oracle.misc,comp.databases.oracle.server,comp.databases.oracle.tools,comp.databases
References: <33D8EDA8.32DC@lingocd.com>
Organization: InterAccess, Co. - Chicagoland's Full Service Internet Provider
Reply-To: akaplan@interaccess.com
Raul Sanchez (rsanchez@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: <->
<-> <->
<-> www.arikaplan.com <->
<-> <->
<-> email: akaplan@interaccess.com <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
Back to Ari Kaplan's Home Page