ORA-02291: integrity constraint(SYS.WRM$_SNAPSHOT_FK) violated
Error
SQL> Exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
begin
2 DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
3 end;
4 /
begin
*
ERROR at line 1:
ORA-13509: error encountered during updates to a AWR table
ORA-02291: integrity constraint (ORA-02291: integrity constraint(SYS.WRM$_SNAPSHOT_FK) violated - parent key not found.) violated - parent key not found
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 10
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 33
ORA-06512: at line 2
Cause
Foreign key constraint error in between tables.
Solution
Option 1: Check the Constraint table in DBA_CONSTRAINTS and find the row which causing problem and delete them.
ORA-02291 error caused due to insert operation are trying to insert a row in child without a matching parent(foreign key constraint).
You need to add row in parent table first then in child table.
Option 2: Following step will re-initiate the AWR tables and all data exists is lost:
Re-initiate the AWR repository tables
1. Run the script to drop the objects
@?/rdbms/admin/catnoawr.sql
2. Run the script will create the objects
@?/rdbms/admin/catawr.sql;
3. Compile the invalid objects
@?/rdbms/admin/utlrp.sql;
4. Restart the database.
shutdown immediate
startup
5. Manually create snapshot and test the AWR report.
-- Create snapshot
exec dbms_workload_repository.create_snapshot;
--wait for 1 min, create snapshot
exec dbms_workload_repository.create_snapshot;
--Fetch the reports
@?\rdbms\admin\awrrpt.sql;
Note: If not work then follow following link:
Recreate the AWR Report in Oracle