Oracle Sql Error 1502
Contents |
SQL TuningSecurityOracle UNIXOracle LinuxMonitoringRemote supportRemote plansRemote servicesApplication Server ApplicationsOracle FormsOracle PortalApp UpgradesSQL ServerOracle ConceptsSoftware SupportRemote Support Development Implementation Consulting StaffConsulting PricesHelp Wanted! Oracle PostersOracle Books Oracle Scripts partition of such index is in unusable state in oracle Ion Excel-DB Don Burleson Blog
how to change unusable index to usable in oracle ORA-01502: index or partition of such index is in usable state how to rebuild index in oracle tips Oracle Database Tips by Burleson Consulting April 4, 2015 The Oracle docs note this on the ORA-01502 error: ORA-01502: index "string.string" or partition of such index isSkip_unusable_indexes
in unusable state Cause: An attempt has been made to access an index or index partition that has been marked unusable by a direct load or by a DDL operation Action: DROP the specified index, or REBUILD the specified index, or REBUILD the unusable index partition The ORA-01502 error can be easily fixed by issuing the alter index rebuild the unusable index partition index_name rebuild partition partition_name; To learn how to rebuild unusable partitioned indexes see these notes on Oracle index partition management. ORA-01502 and skip_unusable_indexes In previous releases of Oracle, the skip_unusable_indexes parameter could only be used at session level and the default value was False. Now, you will be able to set this parameter at session level and instance level as well. By setting the value of the skip_unusable_indexes=true, the optimizer selects an execution plan that does not utilize unusable indexes, preventing ORA-01502 errors. However, when you skip an index you risk getting a sub-optimal SQL execution plan. And since the SQL statement generates no error, the end-user is unaware that the optimizer has chosen a plan that was less than optimal and only notices excessive response time. When you skip an unusable index, the database records an error message in the alert log file whenever an index is marked unusable. ORA-01502 and sqlldr The ORA-01502 can happen while using SQL Loader. They continued to have problems with ORA-01502 (being thrown along with ORA-01
log in tour help Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and ora-01502 while deleting policies of this site About Us Learn more about Stack Overflow the company
Some Indexes Or Index [sub]partitions Of Table Have Been Marked Unusable
Business Learn more about hiring developers or posting ads with us Database Administrators Questions Tags Users Badges Unanswered
Ora-14086
Ask Question _ Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join http://www.dba-oracle.com/t_ora_01502_index_string_string_or_partition_of_such_index_is_in_usable_state.htm them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top ORA-01502: index or partition of such index is in usable state problem up vote 5 down vote favorite 2 I have a table in my Oracle database, where select pkcol, count(*) from http://dba.stackexchange.com/questions/3754/ora-01502-index-or-partition-of-such-index-is-in-usable-state-problem myTable group by pkcol having count(*) > 1; yields PKCOL COUNT(*) ------- ---------- 1 2 2 2 Trying to remove the duplicate rows delete myTable where pkcol = 1; Yields: ORA-01502: index 'MYTABLE.PK_MT' or partition of such index is in usable state. I'm using Oracle.DataAccess.Client.OracleBulkCopy to fill the table. As far as I understand documentation from Oracle PRIMARY KEY constraints had to be checked. Obviously they are not checked, as I found by doing the same bulkcopy two times in succession which ended in duplicates in all row. Now I'm only using it after deleting all rows and I'm using a table with a similar primary key as source. As result I expect no problems. But embedded deep inside my MS Build scripts, I end up with just 2 duplicates out of 2210 rows. I guess that ignoring the primary key in the first place is a clear bug. No Bulkcopy should be allowed to ignore primary key constraints. Edit: Meanwhile I found, that the 2 conflicting rows where normally inserted by some script before bulkcopy was called. The problem reduces to my known problem,
Database How to fix ORA-01502 index or partition of such index is in http://laurenthinoul.com/how-to-fix-ora-01502-index-or-partition-of-such-index-is-in-usable-state/ usable state 02/09/2013•0 Comments If you get the error How to http://www.shaiksameer.com/2012/10/ora-01502-index-or-partition-of-such.html fix ORA-01502: index or partition of such index is in usable state in Oracle. It is possible that an index is broken from a table. To fix this error, you can do the following: delete the data from the table where the index is in oracle part of rebuild the index with the following SQL command: PgSQL ALTER INDEX
and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi] Search This Blog Loading... Tuesday, October 2, 2012 ORA-01502: index or partition of such index is in unusable state Exploring ORA-01502 error and why we usually get this error message. I am not explaining why/how the index status changed to unusable( mostly due to the table move and alter index xxxx unusable ..) You will get ORA-01502: index or partition of such index is in unusable state If you have the parameter skip_unusable_indexes= false then it makes sense that oracle reported this error during DML activity. If you don't care about the unusable indexes during DML and you want the optimizer to choose different(expensive) execution plans during SELECT then you can set the parameter skip_unusable_indexes= true at the instance level or at the session level and move on. what if you got this error even when you have the paramater skip_unusable_indexes set to true at the instance level.. i.e SQL> show parameter skip NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ skip_unusable_indexes boolean TRUE SKIP_UNUSABLE_INDEXES enables or disables the use and reporting of tables with unusable indexes or index partitions. If a SQL statement uses a hint that forces the usage of an unusable index, then this hint takes precedence over initialization parameter settings, including SKIP_UNUSABLE_INDEXES. If the optimizer chooses an unusable index, then an ORA-01502 error will result. (See Oracle Database Administrator's Guide for more information about using hints.) Values: true Disables error reporting of indexes and index partitions marked UNUSABLE. This setting allows all operations (inserts, deletes, updates, and selects) on tables with unusable indexes or index partitions. and you still got the err