Solved

Unable to run Analyze Table - ORA 03113 error (end-of-file on communication channel)

Posted on 2009-04-10
6
1,468 Views
Last Modified: 2013-12-19
I had a data block corruption error on a table and used dbms_repair to mark the blocks corrupt and skip them in the future.  There were some index errors as well, so I rebuit all the indexes for this table.  Now everything seems to be working fine, except that I get an error when I try to run the "analyze table validate structure online" command.  I want to run this command in order to ensure that the block corruption has indeed been fixed.

BTW, when this error comes up, I also lose my connection and have to reconnect in sqlplus.
SQL>analyze table tablename validate structure online;
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

Open in new window

0
Comment
Question by:VikramChari
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 14

Expert Comment

by:ajexpert
ID: 24119900
are you running on client machine?
if you have access to server, can you run this command directly on server box
0
 

Author Comment

by:VikramChari
ID: 24119957
I am running this directly on the server box in sqlplus.
0
 
LVL 14

Expert Comment

by:ajexpert
ID: 24119998
There could be many things causing this error
see this link
http://www.experts-exchange.com/Database/Oracle/Q_23389246.html
 
 
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:VikramChari
ID: 24120066
I check that link, but none of those things seem to apply to my situation.  Here's the sequence of everything that has happened so far:

1.  I noticed a block corruption error on the table when I ran a "analyze table validate structure" command.
2.  I ran dbms_repair to mark the blocks as corrupt.
3.  I deleted some rows from the table that were not needed.
4.  I rebuilt all the indexes from the table.

The server is the same, there has been no upgrade in the Oracle version or any other software, I am able to query and modify all tables, including the table that is causing the problem, I am able to run the "analyze table" command on all other tables except for this one, no new data was imported into the table, etc.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 24121257
I am just thinking why don't you create a new table with the data from this table and then rename the old one to something else and rename the newly created one to the actual name. This is just to see if we can do this successfully and then run analyze command on the newly created table to see if that works.

note: you need to note what all triggers/views/indexes/etc are on the table and you need to setup your new table accordingly after you think it is working fine.  If you have toad/plsql developer, you can take a script of the old table before you do any activity against that old table which we can use later to apply the constraints/indexes/etc on the newly created table.

We can do this if this issue is with just one table and no other database object.  we can try this to see if this workaround can be of use to you. But i suggest that any "ORA-03113: end-of-file on communication channel
" errors should be given to DBA's and an explanation should be sought from them as to why this happened and what needs to be done to ensure that this does not happen again.

OR

Get your DBA to fix this for you. Let them do with the own method to make it working for you.
0
 

Accepted Solution

by:
VikramChari earned 0 total points
ID: 24174036
I finally solved this by exporting the data, dropping the table, recreating the table and associated indexes, and importing the data back into it.
0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: You must have administrative privileges in order to configure lead or case queues. Salesforce.com (http://www.Salesforce.com) is a cloud-based customer relationship management (CRM) system. It is widely used around the world by sales and ma…
Messaging apps are amazing tools with the power to do a lot of good, but the truth is the process of collaborating with coworkers requires relationships established through meaningful communication - the kind of communication that only happens face-…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

738 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