OraFAQ Forum: Server Administration » ORA-20000: index "SYS"."WRH$_ACTIVE_SESS_HIST

Home » RDBMS Server » Server Administration » ORA-20000: index "SYS"."WRH$_ACTIVE_SESS_HIST_PK" (10.1.0.5.0/HPUX)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-20000: index "SYS"."WRH$_ACTIVE_SESS_HIST_PK" [message #309211]

Wed, 26 March 2008 16:32

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.

Report message to a moderator

Re: ORA-20000: index "SYS"."WRH$_ACTIVE_SESS_HIST_PK" [message #309212 is a reply to message #309211]

Wed, 26 March 2008 16:37

BlackSwan
Messages: 26766
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

Report message to a moderator

Re: ORA-20000: index "SYS"."WRH$_ACTIVE_SESS_HIST_PK" [message #309214 is a reply to message #309211]

Wed, 26 March 2008 17:15

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.

Report message to a moderator

Re: ORA-20000: index "SYS"."WRH$_ACTIVE_SESS_HIST_PK" [message #309215 is a reply to message #309211]

Wed, 26 March 2008 17:18

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

in initSID.ora file set the following undocumented parameter
_MAKE_ORACLE_FASTER_=TRUE
& restart the instance.

Report message to a moderator

Re: ORA-20000: index "SYS"."WRH$_ACTIVE_SESS_HIST_PK" [message #309221 is a reply to message #309211]

Wed, 26 March 2008 18:10

it_me24
Messages: 167
Registered: March 2006
Location: delhi

Senior Member

YOU ARE SIMPLY GREAT..!! ANACEDENT

Thanks for your valuable Support. Smile

Report message to a moderator

Re: ORA-20000: index "SYS"."WRH$_ACTIVE_SESS_HIST_PK" [message #309223 is a reply to message #309211]

Wed, 26 March 2008 18:15

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

Report message to a moderator

How to re-builded the primary key [message #309224 is a reply to message #309211]

Wed, 26 March 2008 18:19

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.

Report message to a moderator

Re: ORA-20000: index "SYS"."WRH$_ACTIVE_SESS_HIST_PK" [message #309227 is a reply to message #309211]

Wed, 26 March 2008 18:29

BlackSwan
Messages: 26766
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

Report message to a moderator

Re: ORA-20000: index "SYS"."WRH$_ACTIVE_SESS_HIST_PK" [message #309229 is a reply to message #309211]

Wed, 26 March 2008 18:41

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.....

Report message to a moderator

Re: ORA-20000: index "SYS"."WRH$_ACTIVE_SESS_HIST_PK" [message #309230 is a reply to message #309211]

Wed, 26 March 2008 18:50

BlackSwan
Messages: 26766
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?

Report message to a moderator

Re: ORA-20000: index "SYS"."WRH$_ACTIVE_SESS_HIST_PK" [message #309231 is a reply to message #309211]

Wed, 26 March 2008 19:02

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

Report message to a moderator

Re: ORA-20000: index "SYS"."WRH$_ACTIVE_SESS_HIST_PK" [message #309320 is a reply to message #309231]

Thu, 27 March 2008 02:05

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Please once more read the guidelines, it explains how to post code.

Regards
Michel

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 02 04:02:55 CST 2024