This may not be an answer but worth to check up. Some indexes may have been set to invisible as an alternative to being dropped. These are still maintained by the database but ignored by the optimizer. You'll be able to see them by querying the dictionary, but the compare tool may ignore them.
SELECT INDEX_NAME, INDEX_TYPE, VISIBILITY
FROM ALL_INDEXES
WHERE INDEX_NAME LIKE 'EMP%';
Seems like you have media corruption. I would consult Automatic Diagnostic Repository (ADR) contents if there are open failures in your database. You can do that via Enterprise Manager or using RMAN command-line utility:
[oracle@oca ~]$ rman target=/
RMAN> list faliure;
If there are failures with status OPEN
listed, you can ask Data Recovery Advisor (DRA) to analyze them and recommend you the solution to repair the failures:
RMAN> advise failure;
Since you have block corruption, DRA will probably suggest you to recover the corrupted blocks and will create the appropriate script which you can run manually or in the same flow with RMAN:
RMAN> repair failure;
After failures are repaired they're automatically closed by DRA. You can check if all failures are closed with one more list failure
command in RMAN, and if not you can get another advice from DRA.
EDIT:
Since the recovery you performed didn't last long (elapsed time: 00:00:00
), I infer that the datafile wasn't written to by RMAN, and maybe even checked. Thus I would recommend you to proactively validate the database; to do that, just issue validate database
in RMAN and analyze the output. RMAN will check all datafiles (including control file and spfile) and show you if there are any corrupted blocks.
In your comment, you said that RMAN returns the message "Block Media Recovery requires Enterprise Edition" in response to one of your commands, and based on the response from RMAN to validate database
command I suspect this feature is also not available in your software edition. If your database is running in ARCHIVELOG
mode (check with select log_mode from v$database
) you could just completely restore and recover your database without loss of committed transaction, but before doing this I would make sure that your media (hard disk) is not damaged.
Best Answer
Use this one:
Note, when you use quotes, then names are case-sensitive in Oracle.