ORA-02291: integrity constraint(SYS.WRM$_SNAPSHOT_FK) violated

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.