ORA-01445: Cannot Select ROWID from a Join View without a Key-Preserved Table

Question:  My database was seriously running out of space, and when I checked the Segment Advisor Recommendation on OEM some tables were recommended for shrinking, so I clicked on execute through the OEM and it successfully executed the following commands.

alter table "customer.account" enable row movement
alter table "customer.account" shrink space

But now users have being complaining of getting error ORA-01445 from their applications.   What can we do?

Answer:  To diagnose any error, you start by using the oerr utility to display the ORA-01445 error:

ORA-01445: cannot select ROWID from a join view without a key-preserved table

Cause: A SELECT statement attempted to select ROWIDs from a view derived from a join operation. Because the rows selected in the view do not correspond to underlying physical records, no ROWIDs can be returned.

Action: Remove ROWID from the view selection clause, then re-execute the statement.

First, try removing the ROWID from the view selection clause.  This might fix the errors. However, there are other causes for ORA-01445.

Check to see if a primary key exists on the tables subject to the join.  Usually that causes the ORA-01445 error.

The solution is to either create primary key constraints on the base tables, or create the materialized view with the  BUILD IMMEDIATE option:

SQL> alter table test1 add constraint pk_test1 primary key (test1_coas_code);
SQL> alter table test2 add constraint pk_test2 primary key (test2_coas_code);

or

(2) create the materialized view with BUILD IMMEDIATE

Also, check the patch level as it may be a bug in the specific version of Oracle.

More information is available in Metalink #101349.1