Hi,
We are getting the below error frequently from the application while doing insertion/dataloading to a table. The mentioned error is in the Primary key index. Dataloading is happening through the application and not from back-end
Error
-----
'ORA-01502: index 'INDEX_NAME' or partition of such index is in unusable state'.
I set the value SKIP_UNUSABLE_INDEXES = TRUE using the command 'ALTER SYSTEM SET SKIP_UNUSABLE_INDEXES = TRUE' to avoid this. Again we are getting the same error and Every time Iam rebuilding('alter index INDEX_NAME rebuild') the index and doing the DML Operation. Can you please provide a permenant solution for this ?
In the oracle Enterprise manager , I can see 2 reccomendation for the same table to reclaim the space as given in the attachment and I have executed the below statement to compress the space .
alter table "TABLE_NAME" compress for oltp;
alter table "TABLE_NAME" move;
Regards,
Prejib
ORA-01502:- index 'index-name' or partition of such index is in unusable state
ORA-01502:- index 'index-name' or partition of such index is 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 solution for this error is:
1. Drop the specified index and/or recreate the index
2. Rebuild the specified index
3. Rebuild the unusable index partition
Since you have already done step 2, you can try step 1.
For step 3,follow the procedure below;
First, find out the partition name using:
Action: DROP the specified index, or REBUILD the specified index, or REBUILD the unusable index partition
The solution for this error is:
1. Drop the specified index and/or recreate the index
2. Rebuild the specified index
3. Rebuild the unusable index partition
Since you have already done step 2, you can try step 1.
For step 3,follow the procedure below;
First, find out the partition name using:
Then rebuild the index using:select INDEX_NAME, PARTITION_NAME from user_ind_partitions;
ALTER INDEX <idx_a> REBUILD PARTITION <your_partition>;
Oracle Database Administration Forums
http://www.oracle-forums.com/
http://www.oracle-forums.com/
ORA-01502:- index 'index-name' or partition of such index is in unusable state
Hi ,
This is not a partitioned table and the output of the query is
SQL> select INDEX_NAME, PARTITION_NAME from user_ind_partitions;
no rows selected
This is not a partitioned table and the output of the query is
SQL> select INDEX_NAME, PARTITION_NAME from user_ind_partitions;
no rows selected
ORA-01502:- index 'index-name' or partition of such index is in unusable state
Can you post the output for this:
select index_name, status, owner from
dba_indexes where STATUS = 'UNUSABLE';
Oracle Database Administration Forums
http://www.oracle-forums.com/
http://www.oracle-forums.com/
ORA-01502:- index 'index-name' or partition of such index is in unusable state
I have already rebuilded the indexes whixh are in the 'UNUSABLE' state. O/P of the query is
SQL> select index_name, status, owner from dba_indexes where STATUS = 'UNUSABLE';
no rows selected
SQL> select index_name, status, owner from dba_indexes where STATUS = 'UNUSABLE';
no rows selected
ORA-01502:- index 'index-name' or partition of such index is in unusable state
I have recreated the index and again recieving the error. Please help to solve this..
ORA-01502:- index 'index-name' or partition of such index is in unusable state
Hi,
1. Verify that you don't have invalid objects:
[ORCLA10 /home/oracle]$ sqlplus '/as sysdba'
SQL> SELECT COUNT(*) FROM dba_objects WHERE status = 'INVALID';
2. If there are invalid objects, recompile them:
[ORCLA10 /home/oracle]$ sqlplus '/as sysdba'
SQL> @?/rdbms/admin/utlrp.sql
3. Confirm that invalid objects are recompiled.
4. Re-run your operation.
1. Verify that you don't have invalid objects:
[ORCLA10 /home/oracle]$ sqlplus '/as sysdba'
SQL> SELECT COUNT(*) FROM dba_objects WHERE status = 'INVALID';
2. If there are invalid objects, recompile them:
[ORCLA10 /home/oracle]$ sqlplus '/as sysdba'
SQL> @?/rdbms/admin/utlrp.sql
3. Confirm that invalid objects are recompiled.
4. Re-run your operation.
Oracle Database Administration Forums
http://www.oracle-forums.com/
http://www.oracle-forums.com/
ORA-01502:- index 'index-name' or partition of such index is in unusable state
nothing is there to compile..
ORA-01502:- index 'index-name' or partition of such index is in unusable state
Hi Prejib,
It seems that it's an internal issue. Please log a Service Request here: http://metalink.oracle.com/
Regards,
Drake
It seems that it's an internal issue. Please log a Service Request here: http://metalink.oracle.com/
Regards,
Drake
Oracle Database Administration Forums
http://www.oracle-forums.com/
http://www.oracle-forums.com/
Re: ORA-01502:- index 'index-name' or partition of such index is in unusable state
http://audiobookkeeper.ruhttp://cottagenet.ruhttp://eyesvision.ruhttp://eyesvisions.comhttp://factoringfee.ruhttp://filmzones.ruhttp://gadwall.ruhttp://gaffertape.ruhttp://gageboard.ruhttp://gagrule.ruhttp://gallduct.ruhttp://galvanometric.ruhttp://gangforeman.ruhttp://gangwayplatform.ruhttp://garbagechute.ruhttp://gardeningleave.ruhttp://gascautery.ruhttp://gashbucket.ruhttp://gasreturn.ruhttp://gatedsweep.ruhttp://gaugemodel.ruhttp://gaussianfilter.ruhttp://gearpitchdiameter.ru
http://geartreating.ruhttp://generalizedanalysis.ruhttp://generalprovisions.ruhttp://geophysicalprobe.ruhttp://geriatricnurse.ruhttp://getintoaflap.ruhttp://getthebounce.ruhttp://habeascorpus.ruhttp://habituate.ruhttp://hackedbolt.ruhttp://hackworker.ruhttp://hadronicannihilation.ruhttp://haemagglutinin.ruhttp://hailsquall.ruhttp://hairysphere.ruhttp://halforderfringe.ruhttp://halfsiblings.ruhttp://hallofresidence.ruhttp://haltstate.ruhttp://handcoding.ruhttp://handportedhead.ruhttp://handradar.ruhttp://handsfreetelephone.ru
http://hangonpart.ruhttp://haphazardwinding.ruhttp://hardalloyteeth.ruhttp://hardasiron.ruhttp://hardenedconcrete.ruhttp://harmonicinteraction.ruhttp://hartlaubgoose.ruhttp://hatchholddown.ruhttp://haveafinetime.ruhttp://hazardousatmosphere.ruhttp://headregulator.ruhttp://heartofgold.ruhttp://heatageingresistance.ruhttp://heatinggas.ruhttp://heavydutymetalcutting.ruhttp://jacketedwall.ruhttp://japanesecedar.ruhttp://jibtypecrane.ruhttp://jobabandonment.ruhttp://jobstress.ruhttp://jogformation.ruhttp://jointcapsule.ruhttp://jointsealingmaterial.ru
http://journallubricator.ruhttp://juicecatcher.ruhttp://junctionofchannels.ruhttp://justiciablehomicide.ruhttp://juxtapositiontwin.ruhttp://kaposidisease.ruhttp://keepagoodoffing.ruhttp://keepsmthinhand.ruhttp://kentishglory.ruhttp://kerbweight.ruhttp://kerrrotation.ruhttp://keymanassurance.ruhttp://keyserum.ruhttp://kickplate.ruhttp://killthefattedcalf.ruhttp://kilowattsecond.ruhttp://kingweakfish.ruhttp://kinozones.ruhttp://kleinbottle.ruhttp://kneejoint.ruhttp://knifesethouse.ruhttp://knockonatom.ruhttp://knowledgestate.ru
http://kondoferromagnet.ruhttp://labeledgraph.ruhttp://laborracket.ruhttp://labourearnings.ruhttp://labourleasing.ruhttp://laburnumtree.ruhttp://lacingcourse.ruhttp://lacrimalpoint.ruhttp://lactogenicfactor.ruhttp://lacunarycoefficient.ruhttp://ladletreatediron.ruhttp://laggingload.ruhttp://laissezaller.ruhttp://lambdatransition.ruhttp://laminatedmaterial.ruhttp://lammasshoot.ruhttp://lamphouse.ruhttp://lancecorporal.ruhttp://lancingdie.ruhttp://landingdoor.ruhttp://landmarksensor.ruhttp://landreform.ruhttp://landuseratio.ru
http://languagelaboratory.ruhttp://largeheart.ruhttp://lasercalibration.ruhttp://laserlens.ruhttp://laserpulse.ruhttp://laterevent.ruhttp://latrinesergeant.ruhttp://layabout.ruhttp://leadcoating.ruhttp://leadingfirm.ruhttp://learningcurve.ruhttp://leaveword.ruhttp://machinesensible.ruhttp://magneticequator.ruhttp://magnetotelluricfield.ruhttp://mailinghouse.ruhttp://majorconcern.ruhttp://mammasdarling.ruhttp://managerialstaff.ruhttp://manipulatinghand.ruhttp://manualchoke.ruhttp://medinfobooks.ruhttp://mp3lists.ru
http://nameresolution.ruhttp://naphtheneseries.ruhttp://narrowmouthed.ruhttp://nationalcensus.ruhttp://naturalfunctor.ruhttp://navelseed.ruhttp://neatplaster.ruhttp://necroticcaries.ruhttp://negativefibration.ruhttp://neighbouringrights.ruhttp://objectmodule.ruhttp://observationballoon.ruhttp://obstructivepatent.ruhttp://oceanmining.ruhttp://octupolephonon.ruhttp://offlinesystem.ruhttp://offsetholder.ruhttp://olibanumresinoid.ruhttp://onesticket.ruhttp://packedspheres.ruhttp://pagingterminal.ruhttp://palatinebones.ruhttp://palmberry.ru
http://papercoating.ruhttp://paraconvexgroup.ruhttp://parasolmonoplane.ruhttp://parkingbrake.ruhttp://partfamily.ruhttp://partialmajorant.ruhttp://quadrupleworm.ruhttp://qualitybooster.ruhttp://quasimoney.ruhttp://quenchedspark.ruhttp://quodrecuperet.ruhttp://rabbetledge.ruhttp://radialchaser.ruhttp://radiationestimator.ruhttp://railwaybridge.ruhttp://randomcoloration.ruhttp://rapidgrowth.ruhttp://rattlesnakemaster.ruhttp://reachthroughregion.ruhttp://readingmagnifier.ruhttp://rearchain.ruhttp://recessioncone.ruhttp://recordedassignment.ru
http://rectifiersubstation.ruhttp://redemptionvalue.ruhttp://reducingflange.ruhttp://referenceantigen.ruhttp://regeneratedprotein.ruhttp://reinvestmentplan.ruhttp://safedrilling.ruhttp://sagprofile.ruhttp://salestypelease.ruhttp://samplinginterval.ruhttp://satellitehydrology.ruhttp://scarcecommodity.ruhttp://scrapermat.ruhttp://screwingunit.ruhttp://seawaterpump.ruhttp://secondaryblock.ruhttp://secularclergy.ruhttp://seismicefficiency.ruhttp://selectivediffuser.ruhttp://semiasphalticflux.ruhttp://semifinishmachining.ruhttp://spicetrade.ruhttp://spysale.ru
http://stungun.ruhttp://tacticaldiameter.ruhttp://tailstockcenter.ruhttp://tamecurve.ruhttp://tapecorrection.ruhttp://tappingchuck.ruhttp://taskreasoning.ruhttp://technicalgrade.ruhttp://telangiectaticlipoma.ruhttp://telescopicdamper.ruhttp://temperateclimate.ruhttp://temperedmeasure.ruhttp://tenementbuilding.rutuchkashttp://ultramaficrock.ruhttp://ultraviolettesting.ru
http://geartreating.ruhttp://generalizedanalysis.ruhttp://generalprovisions.ruhttp://geophysicalprobe.ruhttp://geriatricnurse.ruhttp://getintoaflap.ruhttp://getthebounce.ruhttp://habeascorpus.ruhttp://habituate.ruhttp://hackedbolt.ruhttp://hackworker.ruhttp://hadronicannihilation.ruhttp://haemagglutinin.ruhttp://hailsquall.ruhttp://hairysphere.ruhttp://halforderfringe.ruhttp://halfsiblings.ruhttp://hallofresidence.ruhttp://haltstate.ruhttp://handcoding.ruhttp://handportedhead.ruhttp://handradar.ruhttp://handsfreetelephone.ru
http://hangonpart.ruhttp://haphazardwinding.ruhttp://hardalloyteeth.ruhttp://hardasiron.ruhttp://hardenedconcrete.ruhttp://harmonicinteraction.ruhttp://hartlaubgoose.ruhttp://hatchholddown.ruhttp://haveafinetime.ruhttp://hazardousatmosphere.ruhttp://headregulator.ruhttp://heartofgold.ruhttp://heatageingresistance.ruhttp://heatinggas.ruhttp://heavydutymetalcutting.ruhttp://jacketedwall.ruhttp://japanesecedar.ruhttp://jibtypecrane.ruhttp://jobabandonment.ruhttp://jobstress.ruhttp://jogformation.ruhttp://jointcapsule.ruhttp://jointsealingmaterial.ru
http://journallubricator.ruhttp://juicecatcher.ruhttp://junctionofchannels.ruhttp://justiciablehomicide.ruhttp://juxtapositiontwin.ruhttp://kaposidisease.ruhttp://keepagoodoffing.ruhttp://keepsmthinhand.ruhttp://kentishglory.ruhttp://kerbweight.ruhttp://kerrrotation.ruhttp://keymanassurance.ruhttp://keyserum.ruhttp://kickplate.ruhttp://killthefattedcalf.ruhttp://kilowattsecond.ruhttp://kingweakfish.ruhttp://kinozones.ruhttp://kleinbottle.ruhttp://kneejoint.ruhttp://knifesethouse.ruhttp://knockonatom.ruhttp://knowledgestate.ru
http://kondoferromagnet.ruhttp://labeledgraph.ruhttp://laborracket.ruhttp://labourearnings.ruhttp://labourleasing.ruhttp://laburnumtree.ruhttp://lacingcourse.ruhttp://lacrimalpoint.ruhttp://lactogenicfactor.ruhttp://lacunarycoefficient.ruhttp://ladletreatediron.ruhttp://laggingload.ruhttp://laissezaller.ruhttp://lambdatransition.ruhttp://laminatedmaterial.ruhttp://lammasshoot.ruhttp://lamphouse.ruhttp://lancecorporal.ruhttp://lancingdie.ruhttp://landingdoor.ruhttp://landmarksensor.ruhttp://landreform.ruhttp://landuseratio.ru
http://languagelaboratory.ruhttp://largeheart.ruhttp://lasercalibration.ruhttp://laserlens.ruhttp://laserpulse.ruhttp://laterevent.ruhttp://latrinesergeant.ruhttp://layabout.ruhttp://leadcoating.ruhttp://leadingfirm.ruhttp://learningcurve.ruhttp://leaveword.ruhttp://machinesensible.ruhttp://magneticequator.ruhttp://magnetotelluricfield.ruhttp://mailinghouse.ruhttp://majorconcern.ruhttp://mammasdarling.ruhttp://managerialstaff.ruhttp://manipulatinghand.ruhttp://manualchoke.ruhttp://medinfobooks.ruhttp://mp3lists.ru
http://nameresolution.ruhttp://naphtheneseries.ruhttp://narrowmouthed.ruhttp://nationalcensus.ruhttp://naturalfunctor.ruhttp://navelseed.ruhttp://neatplaster.ruhttp://necroticcaries.ruhttp://negativefibration.ruhttp://neighbouringrights.ruhttp://objectmodule.ruhttp://observationballoon.ruhttp://obstructivepatent.ruhttp://oceanmining.ruhttp://octupolephonon.ruhttp://offlinesystem.ruhttp://offsetholder.ruhttp://olibanumresinoid.ruhttp://onesticket.ruhttp://packedspheres.ruhttp://pagingterminal.ruhttp://palatinebones.ruhttp://palmberry.ru
http://papercoating.ruhttp://paraconvexgroup.ruhttp://parasolmonoplane.ruhttp://parkingbrake.ruhttp://partfamily.ruhttp://partialmajorant.ruhttp://quadrupleworm.ruhttp://qualitybooster.ruhttp://quasimoney.ruhttp://quenchedspark.ruhttp://quodrecuperet.ruhttp://rabbetledge.ruhttp://radialchaser.ruhttp://radiationestimator.ruhttp://railwaybridge.ruhttp://randomcoloration.ruhttp://rapidgrowth.ruhttp://rattlesnakemaster.ruhttp://reachthroughregion.ruhttp://readingmagnifier.ruhttp://rearchain.ruhttp://recessioncone.ruhttp://recordedassignment.ru
http://rectifiersubstation.ruhttp://redemptionvalue.ruhttp://reducingflange.ruhttp://referenceantigen.ruhttp://regeneratedprotein.ruhttp://reinvestmentplan.ruhttp://safedrilling.ruhttp://sagprofile.ruhttp://salestypelease.ruhttp://samplinginterval.ruhttp://satellitehydrology.ruhttp://scarcecommodity.ruhttp://scrapermat.ruhttp://screwingunit.ruhttp://seawaterpump.ruhttp://secondaryblock.ruhttp://secularclergy.ruhttp://seismicefficiency.ruhttp://selectivediffuser.ruhttp://semiasphalticflux.ruhttp://semifinishmachining.ruhttp://spicetrade.ruhttp://spysale.ru
http://stungun.ruhttp://tacticaldiameter.ruhttp://tailstockcenter.ruhttp://tamecurve.ruhttp://tapecorrection.ruhttp://tappingchuck.ruhttp://taskreasoning.ruhttp://technicalgrade.ruhttp://telangiectaticlipoma.ruhttp://telescopicdamper.ruhttp://temperateclimate.ruhttp://temperedmeasure.ruhttp://tenementbuilding.rutuchkashttp://ultramaficrock.ruhttp://ultraviolettesting.ru