ANALYZE TABLE..VALIDATE STRUCTURE runs forever

So, as I've mentioned before, I am a SQL Server DBA/Consultant who is helping out a customer with their Oracle databases. Mostly, I've been able to figure out what I need to and work through things with the help of the doc and Google but I am currently trying to deal with something that seems inscrutable.

One of the databases is an Oracle 10g LIMS database that has been around (and upgraded) for at least 10 years (maybe 20). It's a critical app but they've been having numerous reliability issues with it over the past couple of years. In order to try to get our hands around what's really wrong with it and what we might be able to do about it we had the hosting/MS provider make a physical copy of it on another (identical) server. (I am pretty sure that the provider interpreted that as meaning "RMAN backup and restore").

The idea being that I could then do whatever analysis, investigation, repair attempts, and use any tools that I wanted on it without any danger of impacting production. So far, so good. I ran DBVerify (dbv) on all of the data files, no problems.

Then I ran ANALYZE TABLE .. VALIDATE STRUCTURE CASCADE ONLINE; on every table in the database. For two of these tables, the command took much longer than the other 2500 tables (combined) so I cancelled them and let the others finish, which they did with no problems and no reported errors, etc.

These two tables as it turns out are also the biggest application data tables. So then I start with the largest table (23GB, 36M rows) and decide to analyze it by parts (without the CASCADE), first the table itself then the indexes. The table finishes in 30-60 minutes (I do not remember exactly), but the ANALYZE INDEX .. VALIDATE STRUCTURE ONLINE; for the first index never finishes.

I decided to let it run for a while, and to see if I could find a way to monitor it's progress. Googling this I find many (old) claims that ANALYZE TABLE can be monitored with v$session_longops but looking at it never sh

UPDATE: What Worked...

So after reading the link from @Raj and reading @ora-600's answer I tried to validate the database with the RMAN command backup check logical validate database;. While this worked fine, it was also clear that it was not looking at everything that the ANALYZE INDEX command would.

After trying many different variations, I finally discovered that this command would work:

ANALYZE TABLE .. VALIDATE STRUCTURE CASCADE offline;

Yep, just switching to OFFLINE appears to fix it. And that eventually led me to this bug# 5752105 on this page: http://www.eygle.com/case/10204_buglist.htm. I am not in a position to prove it right now (cannot apply any patches for the time being), but I strongly suspect that this is what I was running into.

So while the question is not fully answered, I am going to mark @ora-600's very helpful answer as correct so that he can collect Paul White's very generous bounty.

StackExchange Database Administrators Q#138503, answer score: 3