Solved

ora-600 [17182]. Any idea?

Posted on 2001-07-17
13
1,308 Views
Last Modified: 2011-09-20
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
Comment
Question by:i020242
  • 4
  • 4
  • 2
  • +2
13 Comments
 
LVL 4

Expert Comment

by:fva
ID: 6292795
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
 
LVL 1

Author Comment

by:i020242
ID: 6293186
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
 
LVL 4

Expert Comment

by:fva
ID: 6293263
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
 
LVL 1

Expert Comment

by:alx512
ID: 6293528
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
 
LVL 1

Author Comment

by:i020242
ID: 6293749
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
 
LVL 1

Expert Comment

by:alx512
ID: 6294013
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Author Comment

by:i020242
ID: 6297225
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
 
LVL 1

Expert Comment

by:alx512
ID: 6297291
No other ideas, sorry.
Can you show your table and index definition?
0
 
LVL 1

Author Comment

by:i020242
ID: 6297363
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
 
LVL 1

Expert Comment

by:Moondancer
ID: 7034598
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
 
LVL 49

Expert Comment

by:DanRollins
ID: 7053196
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
 
LVL 1

Accepted Solution

by:
Moondancer earned 0 total points
ID: 7054320
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
 
LVL 1

Expert Comment

by:alx512
ID: 7055519
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now