Home » RDBMS Server » Server Administration » ORA-20000: index "SYS"."WRH$_ACTIVE_SESS_HIST_PK" (10.1.0.5.0/HPUX)
ORA-20000: index "SYS"."WRH$_ACTIVE_SESS_HIST_PK" [message #309211] Wed, 26 March 2008 16:32 Go to next message
it_me24
Messages: 167
Registered: March 2006
Location: delhi
Senior Member
Hi All,

Users is getting the below error ...

server err ora 01502 index inder_ref_idx partition is in unusable state

and in the alert log i found the below contents

ARCH: Connecting to console port...
Wed Mar 26 22:00:53 2008
GATHER_STATS_JOB encountered errors. Check the
trace file.Wed Mar 26 22:00:53 2008
Errors in file /unisoft/ora10/admin/unidb/bdump/
unidb_j006_5307.trc:
ORA-20000: index "SYS"."WRH$_ACTIVE_SESS_HIST_PK"
or partition of such index is in unusable state
Wed Mar 26 23:30:59 2008
Private_strands 248 at log switch
Thread 1 advanced to log sequence 146206
Current log# 3 seq# 146206 mem# 0: /uniredoA/oradata/unidb/redo3a.log
Current log# 3 seq# 146206 mem# 1: /uniredoB/oradata/unidb/redo3b.log
Wed Mar 26 23:30:59 2008

KINDLY HELP IN RESOLVING THIS ERROR, IT IS REALLY URGENT.....

THANKS FOR YOUR TIME.



Re: ORA-20000: index "SYS"."WRH$_ACTIVE_SESS_HIST_PK" [message #309212 is a reply to message #309211] Wed, 26 March 2008 16:37 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
// MERGE: 1489 RENUMBERED TO 1502
// *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

Re: ORA-20000: index "SYS"."WRH$_ACTIVE_SESS_HIST_PK" [message #309214 is a reply to message #309211] Wed, 26 March 2008 17:15 Go to previous messageGo to next message
it_me24
Messages: 167
Registered: March 2006
Location: delhi
Senior Member
Hats off to you anacedent.

I checked in TOAD and found that all the indexex have become invalid.
now in order to rebuild the index what steps should i follow.

I tried the below steps but oracle returned error.

SQL> DROP INDEX ASDWLRDB.GP_IGNOREUDR_REFERENCENO_PK;
DROP INDEX ASDWLRDB.GP_IGNOREUDR_REFERENCENO_PK
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified


SQL> DROP INDEX ASDWLRDB.IDXCALLDATETIMEAS;
DROP INDEX ASDWLRDB.IDXCALLDATETIMEAS
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

Then i tried rebuilding the index online but it is taking too much time to re-build the index.

ALTER INDEX ASDWLRDB.IDXCALLDATETIMEAS REBUILD ONLINE;

Is there any other way around to re-build the indexes a bit faster.

Thanks once again.
Re: ORA-20000: index "SYS"."WRH$_ACTIVE_SESS_HIST_PK" [message #309215 is a reply to message #309211] Wed, 26 March 2008 17:18 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
in initSID.ora file set the following undocumented parameter
_MAKE_ORACLE_FASTER_=TRUE
& restart the instance.
Re: ORA-20000: index "SYS"."WRH$_ACTIVE_SESS_HIST_PK" [message #309221 is a reply to message #309211] Wed, 26 March 2008 18:10 Go to previous messageGo to next message
it_me24
Messages: 167
Registered: March 2006
Location: delhi
Senior Member
YOU ARE SIMPLY GREAT..!! ANACEDENT

Thanks for your valuable Support. Smile
Re: ORA-20000: index "SYS"."WRH$_ACTIVE_SESS_HIST_PK" [message #309223 is a reply to message #309211] Wed, 26 March 2008 18:15 Go to previous messageGo to next message
it_me24
Messages: 167
Registered: March 2006
Location: delhi
Senior Member
Hi anacedent,

please guide me in the lst step now.

how to rebuild the Primary key...


Thanks
How to re-builded the primary key [message #309224 is a reply to message #309211] Wed, 26 March 2008 18:19 Go to previous messageGo to next message
it_me24
Messages: 167
Registered: March 2006
Location: delhi
Senior Member
I have re-builded two indexes, now i have to rebuild the primary key, please guide me to do so.

Thanks.
Re: ORA-20000: index "SYS"."WRH$_ACTIVE_SESS_HIST_PK" [message #309227 is a reply to message #309211] Wed, 26 March 2008 18:29 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
I feel sorry for you that both GOOGLE & the search function on this forum are broken for you.

Results 1 - 10 of about 207,000 for oracle rebuild primary key
I guess you'll be forced to RTFM found at http://tahiti.oracle.com
Re: ORA-20000: index "SYS"."WRH$_ACTIVE_SESS_HIST_PK" [message #309229 is a reply to message #309211] Wed, 26 March 2008 18:41 Go to previous messageGo to next message
it_me24
Messages: 167
Registered: March 2006
Location: delhi
Senior Member
Thanks anacedent,

Tried searching, but did not get my specific result.

I would highly request you to please send me the sql command to rebuld the primary key because i have very less time left to resolve this issue.

Please Anacedent.....
Re: ORA-20000: index "SYS"."WRH$_ACTIVE_SESS_HIST_PK" [message #309230 is a reply to message #309211] Wed, 26 March 2008 18:50 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
it_me24,
you have been posting here for about 2 years, yet you continue to ignore posting guidelines; such as provide DDL to construct tables.

Based upon what you presented so far in this thread, how is anyone but YOU to know which columns in which table comprise the PK?

The lack of planning on your part does not constitute an emergency on my part.

Would you like some cheese with your WHINE?
Re: ORA-20000: index "SYS"."WRH$_ACTIVE_SESS_HIST_PK" [message #309231 is a reply to message #309211] Wed, 26 March 2008 19:02 Go to previous messageGo to next message
it_me24
Messages: 167
Registered: March 2006
Location: delhi
Senior Member
My Appologies..

Below is the Primary key creation DDL.

ALTER TABLE ASD.IGNORE ADD (
CONSTRAINT GPIGNORE_REFERENCENO_PK PRIMARY KEY (REFERENCENO)
USING INDEX
TABLESPACE RDB_TS_GP
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
));

Please guide ....

Thanks
Re: ORA-20000: index "SYS"."WRH$_ACTIVE_SESS_HIST_PK" [message #309320 is a reply to message #309231] Thu, 27 March 2008 02:05 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please once more read the guidelines, it explains how to post code.

Regards
Michel
Previous Topic: How to know this table belongs to which tablespace?
Next Topic: alter rollback segment
Goto Forum:
  


Current Time: Fri Dec 02 19:05:46 CST 2016

Total time taken to generate the page: 0.05654 seconds