Home » RDBMS Server » Server Administration » ORA-01502 (Oracle 9.2.0.6.0, Solaris 10)
ORA-01502 [message #365061] Wed, 10 December 2008 16:08 Go to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
Hi, I am getting the following error while trying to drop a user with casacde optioni:

ORA-01502: index 'SYS.I_ACCESS1' or partition of such index is in unusable state

Before that I was doing an import which terminated without errors. I shutdown the database and now cannot start it back up. Here are the contents of the alert file:

Errors in file /data/app/oracle/admin/rmsprod/udump/rmsprod_ora_22198.trc:
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index 'SYS.I_ACCESS1' or partition of such index is in unusable state
Wed Dec 10 16:48:14 2008
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 22198
ORA-1092 signalled during: ALTER DATABASE OPEN...


Please advise! Thank you!

[Updated on: Wed, 10 December 2008 16:13]

Report message to a moderator

Re: ORA-01502 [message #365065 is a reply to message #365061] Wed, 10 December 2008 16:27 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
ORA-01502: index 'string.string' 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 


And have you looked at the file rmsprod_ora_22198.trc for additional information?

Re: ORA-01502 [message #365075 is a reply to message #365065] Wed, 10 December 2008 20:19 Go to previous messageGo to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
Yes, I googled this explanation also Smile

There is no additional info in .trc file. I was just wondering why this error took place. Thank you.
Re: ORA-01502 [message #365118 is a reply to message #365061] Thu, 11 December 2008 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
'SYS.I_ACCESS1' is an index on SYS.ACCESS$, what did you do to make it unusable?

Now, you have to restore/recover your database from a backup.

Regards
Michel
Re: ORA-01502 [message #365346 is a reply to message #365118] Thu, 11 December 2008 10:40 Go to previous messageGo to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
I would like to know that also. There was no activity in the db except of an import of a schema from a different db. After the import I got this message. Yes, restore and recover, but it would be nice know what produced this behavior. Thank you!
Re: ORA-01502 [message #365352 is a reply to message #365346] Thu, 11 December 2008 11:48 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
You didn't do an export and import as the SYS user, did you?
Re: ORA-01502 [message #365360 is a reply to message #365352] Thu, 11 December 2008 13:17 Go to previous messageGo to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
As a matter of fact I did. Could that be a problem?
Re: ORA-01502 [message #365364 is a reply to message #365360] Thu, 11 December 2008 13:39 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Yes.

That has most likely destroyed the database and put in in an unusable state.

Don't EVER touch the sys schema with imports/exports or put own database objects in it.
Re: ORA-01502 [message #365366 is a reply to message #365364] Thu, 11 December 2008 13:50 Go to previous message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
Ok, thank you! I tried to search docs about it and it does not say anywhere that you cant use SYS to do imports, etc. I learned my lesson though.
Previous Topic: Memory parameter not included in sga
Next Topic: Oracle XE 10 so many oracle processes
Goto Forum:
  


Current Time: Fri Dec 02 20:56:17 CST 2016

Total time taken to generate the page: 0.08218 seconds