Oracle Primary Key Index – How to Make Usable from Unusable

You can update a join in Oracle if the following conditions are met:

1. Only one base table is updated
2. All other tables are key-preserved: each of them must have at most one row for each row of the base table.

(additional restrictions on updating views apply)

In your example you update table PG_MACHINE only. Oracle has to make sure that for a single row of this table, only one row of the other can be found. This seems to be the case since you have a PK on PG_LABLOCATION.LABLOCID. Therefore you should be able to update the join. See for example this SQLFiddle with a similar setup.

In your case you should either:

• make sure that the primary key is enabled, validated, not deferrable (interestingly, a deferrable constraint prevents Oracle from updating the join!)
• use MERGE if PG_LABLOCATION.LABLOCID is unique for the relevant query. MERGE is less strict than update with joins and will only return an error if there is actually a duplicate in the result set (whereas UPDATE will fail if there is the possibility of a duplicate).

• review your query, since you don't need values from the parent table in the SELECT clause, you could rewrite it as a semi-join (that guarantees that no duplicate will be generated):

  UPDATE (SELECT mac.in_use, mac.update_time
            FROM pg_machine mac
           WHERE mac.lablocid IN (SELECT loc.lablocid 
                                    FROM pg_lablocation loc 
                                   WHERE loc.dnsname = 'value')
             AND to_date('02-JAN-2013') > mac.update_time)
     SET in_use = 1 - MOD(101, 2), 
         update_time = to_date('02-JAN-2013');
  

  This could be rewritten as:

  UPDATE pg_machine mac
     SET in_use = 1 - MOD(101, 2), 
         update_time = to_date('02-JAN-2013')
   WHERE mac.lablocid IN (SELECT loc.lablocid 
                            FROM pg_lablocation loc 
                           WHERE loc.dnsname = 'value')
     AND to_date('02-JAN-2013') > mac.update_time;
  

In this case I would go with the third option: in general you can't update the parent in a parent-child join.

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.