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:
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
BUILD IMMEDIATE option:
(1) create primary key constraints on the base table
table test1 add constraint pk_test1 primary key (test1_coas_code);
SQL> alter table test2 add constraint pk_test2 primary key
(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
Get the Complete
Oracle SQL Tuning Information
The landmark book
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
for 30% off directly from the publisher.