UMESHNC
asked on
ORA-01502: index is in unusable state
I am using sqlloader with direct=TRUE , i am trying to insert 2 million records in to table through flat files. This flat files some times may have duplicated records also.
(using Direct Path Load)
The loading tables has a primary key constraint on some columns.
Sqlloader is loading successfully but the index which is created for primary key by default by oracle
is getting in unusable state.
1>Due to this the select query in the procedure is giving the error that index is in unsable state.
ORA-01502: index 'DOS_OWNER.PK_MARKETING_CO
index is in unusable state
2>I am not able to even delete the duplicate records also because for the same error.
SQL> alter session set skip_unusable_indexes=true
Session altered.
SQL> ed
Wrote file afiedt.buf
1 delete from marketing_copy
2 where rowid in ( select rowid
3 from ( select
4 row_number() over ( partition by full_isbn,
5 region,
6 marketing_copy_type,load_d
7 order by full_isbn ) rn
8 from marketing_copy )
9* where rn <> 1)
SQL> /
delete from marketing_copy
*
ERROR at line 1:
ORA-01502: index 'GLOBAL_HEPM_OWNER.PK_MARK
index is in unusable state
3> I am not able to re build also
SQL> alter index PK_MARKETING_COPY1234 rebuild;
alter index PK_MARKETING_COPY1234 rebuild
*
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
==========================
I am using
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
1) How to proceed now any logic/method to handle this situation.
2) how can i rebuild my index / How delete duplicate records
Please Help me ouT....!
(using Direct Path Load)
The loading tables has a primary key constraint on some columns.
Sqlloader is loading successfully but the index which is created for primary key by default by oracle
is getting in unusable state.
1>Due to this the select query in the procedure is giving the error that index is in unsable state.
ORA-01502: index 'DOS_OWNER.PK_MARKETING_CO
index is in unusable state
2>I am not able to even delete the duplicate records also because for the same error.
SQL> alter session set skip_unusable_indexes=true
Session altered.
SQL> ed
Wrote file afiedt.buf
1 delete from marketing_copy
2 where rowid in ( select rowid
3 from ( select
4 row_number() over ( partition by full_isbn,
5 region,
6 marketing_copy_type,load_d
7 order by full_isbn ) rn
8 from marketing_copy )
9* where rn <> 1)
SQL> /
delete from marketing_copy
*
ERROR at line 1:
ORA-01502: index 'GLOBAL_HEPM_OWNER.PK_MARK
index is in unusable state
3> I am not able to re build also
SQL> alter index PK_MARKETING_COPY1234 rebuild;
alter index PK_MARKETING_COPY1234 rebuild
*
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
==========================
I am using
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
1) How to proceed now any logic/method to handle this situation.
2) how can i rebuild my index / How delete duplicate records
Please Help me ouT....!
UMESHNC:
1. drop the index first:
SQL>drop index GLOBAL_HEPM_OWNER.PK_MARKE
2. delete the duplicate rows:
delete from
GLOBAL_HEPM_OWNER.marketin
where row_id > (select min(row_id) from
GLOBAL_HEPM_OWNER.marketin
group by <PK_marketing_copy columns>);
1. drop the index first:
SQL>drop index GLOBAL_HEPM_OWNER.PK_MARKE
2. delete the duplicate rows:
delete from
GLOBAL_HEPM_OWNER.marketin
where row_id > (select min(row_id) from
GLOBAL_HEPM_OWNER.marketin
group by <PK_marketing_copy columns>);
Sorry, i submitted accidentally before I even finished:
as a step 3:
3. recreate the index.
as a step 3:
3. recreate the index.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
1. drop the index first:
SQL>drop index GLOBAL_HEPM_OWNER.PK_MARKE
2. delete the duplicate rows:
delete from
GLOBAL_HEPM_OWNER.marketin
where row_id not in (select min(row_id) from
GLOBAL_HEPM_OWNER.marketin
group by <PK_marketing_copy columns>);
3. import.
4. built the index again.
SQL>drop index GLOBAL_HEPM_OWNER.PK_MARKE
2. delete the duplicate rows:
delete from
GLOBAL_HEPM_OWNER.marketin
where row_id not in (select min(row_id) from
GLOBAL_HEPM_OWNER.marketin
group by <PK_marketing_copy columns>);
3. import.
4. built the index again.
ASKER
hi seazodiac,
The Delete statement takes more time than the one i have Given Earlier...
Your delete statement takes more than a 10 min ....i killed the query. I had 1 million
record with 80% duplicate records. Where my query took 1.5 minutes.
delete from
GLOBAL_HEPM_OWNER.marketin
where row_id > (select min(row_id) from
GLOBAL_HEPM_OWNER.marketin
group by <PK_marketing_copy columns>);
Thanks
The Delete statement takes more time than the one i have Given Earlier...
Your delete statement takes more than a 10 min ....i killed the query. I had 1 million
record with 80% duplicate records. Where my query took 1.5 minutes.
delete from
GLOBAL_HEPM_OWNER.marketin
where row_id > (select min(row_id) from
GLOBAL_HEPM_OWNER.marketin
group by <PK_marketing_copy columns>);
Thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
2) alter index <index name> rebuild