Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1333
  • Last Modified:

ora-600 [17182]. Any idea?

Dear experts,

during: analyze table tablename validate structure cascade

I get ora-600 [17182] [  ]  [   ], in my trace files.

In addition during dbverf80 I get a page failing, in a block# and file# which corresponds to that table.

I have NT and Oracle 8.0.5.2.6  Is this an Oracle bug?

The problem is that when I try to create a new table from the table previously mentioned, without the data residing in the block I get from dbverf80, the resulting rows are equal to the rows of the previous table, meaning I get all the data.
Afterwards I build the index and I get the same ora-600 error.

Any help would be very much appreciated.

J.K

 
0
i020242
Asked:
i020242
  • 4
  • 4
  • 2
  • +2
1 Solution
 
fvaCommented:
AFAIS on Metalink, this error is a memory-related error (heap corruption) and is only the "root" of other problems. I also saw there are lots of "sequels" and lots of possible causes and I think you should try to work directly with Oracle people on that.

F.
0
 
i020242Author Commented:
Hi,

does this metalink suggests that an upgrade to 8.0.6.* would solve the problem?

Can you tell me what maybe going wrong?

Thanks
J.K
0
 
fvaCommented:
The problem seems to appear in more than one version. Actually there was no info about your particular case.
Also, the problem seems more general. It's a sign of corruption of some "magic" value in the header of a block in the memory heap. In some cases it seems that data remains OK, others have complained about data problems, too. In the global picture, you should try to work directly with Oracle to sort it out (if you have purchased the appropriate support together with your DB) or to upgrade and hope for the better.

I'm afraid that's all I can say. Maybe you'll get better advice from someone else that had experienced directly this problem. Keep therefore this Q open for a while.

F.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
alx512Commented:
We took this problem too.
It appear then index has a small selectivity.
This is internal error in Oracle.
You must recreate index with better selectivity.
As a last resort you can add a primary key into index.
0
 
i020242Author Commented:
Hi,

can you be more detailed please? Give me an example. I tend to believe that this is an internal error, but what has the selectivity of an index to do with ora-600 from analyze command?

Please provide me with an example.

Cheers,
J.K
0
 
alx512Commented:
Execute this statement:

select value from sys.v_$parameter
where name = 'optimizer_features_enable'

if result is 8.0.5 then disable this line in init.ora
and restart the instance. The problem must disappear.
0
 
i020242Author Commented:
Hi,

I do not have this parameter in my init.ora file, so Oracle uses the default value which is 8.0.0.

Any further help?

J.K
0
 
alx512Commented:
No other ideas, sorry.
Can you show your table and index definition?
0
 
i020242Author Commented:
Hi,
here are the scripts:

_________________________________________________________
CREATE        TABLE "SAPR3"."ACCTCR"
                    ("MANDT" VARCHAR2(3) DEFAULT '000'  NOT NULL,"AWTYP" VARCHAR2(5) DEFAULT ' '  NOT NULL,"AWREF" VARCHAR2(10) DEFAULT ' '  NOT NULL,"AWORG" VARCHAR2(10) DEFAULT ' '  NOT NULL,"POSNR" VARCHAR2(10) DEFAULT '0000000000'  NOT NULL,
                    "CURTP" VARCHAR2(2) DEFAULT ' '  NOT NULL,"WAERS" VARCHAR2(5) DEFAULT ' '  NOT NULL,"WRBTR" NUMBER(13,2) DEFAULT 0  NOT NULL,"KURSF" NUMBER(9,5) DEFAULT 0  NOT NULL)
                    TABLESPACE  "PSAPBTABD"
                    PCTFREE  10  PCTUSED   40  INITRANS     1  MAXTRANS   255
                    STORAGE (INITIAL   10256K  NEXT    10240K  PCTINCREASE  0
                    /* USED SPACE   10256K */  MINEXTENTS   1  MAXEXTENTS 300);


EXIT;
___________________________________________________________
CREATE UNIQUE INDEX "SAPR3"."ACCTCR____0"
                 ON "SAPR3"."ACCTCR"("MANDT","AWTYP","AWREF","AWORG","POSNR","CURTP","WAERS")
                    TABLESPACE  "PSAPBTABI"
                    PCTFREE  10                INITRANS     2  MAXTRANS   255
                    STORAGE (INITIAL    5136K  NEXT     2560K  PCTINCREASE  0
                    /* USED SPACE    5136K */  MINEXTENTS   1  MAXEXTENTS 300);

EXIT;


___________________________________________________________
ALTER         TABLE "SAPR3"."ACCTCR"
                ADD (CONSTRAINT "ACCTCR____0"
                     PRIMARY KEY ("MANDT","AWTYP","AWREF","AWORG","POSNR","CURTP","WAERS")
                     USING INDEX TABLESPACE "PSAPBTABI"
                                 PCTFREE 10 INITRANS 2 MAXTRANS 255
                                 STORAGE (INITIAL 16K NEXT 2560K
                                          MINEXTENTS 1 MAXEXTENTS 300
                                          PCTINCREASE 0) );

EXIT;

___________________________________________________________

Please advice.

J.K




0
 
MoondancerCommented:
Please update and finalize this old, open question.  Please:

1) Award points ... if you need Moderator assistance to split points, comment here with details please or advise us in Community Support with a zero point question and this question link.
2) Ask us to delete it if it has no value to you or others
3) Ask for a refund so that we can move it to our PAQ at zero points if it did not help you but may help others.

EXPERT INPUT WITH CLOSING RECOMMENDATIONS IS APPRECIATED IF ASKER DOES NOT RESPOND.

Thanks,
Moondancer - EE Moderator

P.S.  Please click your Member Profile, expand View Question History to navigate and update all your open and locked questions.
0
 
DanRollinsCommented:
Lacking timely response from i020242 or contributing experts, but seeing that there is some useful info here, I recommend:

    Refund points and save as a 0-pt PAQ.

DanRollins -- EE database cleanup volunteer
0
 
MoondancerCommented:
Thanks, Dan.
I finalized this today and will monitor it in the event an adjustment is needed.
100 points refunded, PAQd to close.
Moondancer - EE Moderator
0
 
alx512Commented:
Why you create a unique index and primary key on same columns? Primary key genarates index automaticaly. Oracle does not permit you to create indexes on same columns. But in this case you disturb the oracle permissions.
Drop the index and then analyze the table.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now