Foreign keys and "on delete cascade" for a few delete statements only

Let assume that we have 2 tables, in a parent-child relationship, but the foreign key was created without "on delete cascade"; we want to run a few delete statements on the parent table, which should be cascaded to the child table and after the completion we want to restore the initial mode.

One way to do it is to delete from the child table first and then from the parent table, but this may pose a challenge, we only have conditions for delete for the parent table.

The solution will be re-create the existing foreign key with "on delete cascade", run all the delete statements and after that re-create the FK again, this time exactly as it was before.

Below is a small example:


SQL> create table parent (
  2  col1_parent number not null);

Table created.


SQL> alter table parent add constraint parent_pk primary key (col1_parent);

Table altered.


SQL> insert into parent values(1);

1 row created.

SQL> insert into parent values(2);

1 row created.

SQL> insert into parent values(3);

1 row created.


SQL> create table child (
  2   col1_child number,
  3  FOREIGN KEY (col1_child) REFERENCES parent(col1_parent) );

Table created.


 * To delete 1 row from the parent table, we first must delete from the child:


SQL> delete from parent where COL1_PARENT=1;
delete from parent where COL1_PARENT=1
*
ERROR at line 1:
ORA-02292: integrity constraint (FLORINM.SYS_C001881207) violated - child
record found


SQL> delete from child where col1_child=1;

1 row deleted.

SQL> delete from parent where COL1_PARENT=1;

1 row deleted.


 * Assume we'd like to delete a few rows from parent, that we'll be cascaded to the child, as a one time thing only:


SQL> insert into child values(2);

1 row created.

SQL> insert into child values(3);

1 row created.

SQL> insert into parent values(10);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from parent;

COL1_PARENT
-----------
          2
          3
         10

SQL> select * from child;

COL1_CHILD
----------
         2
         3



  1* select table_name,constraint_name,constraint_type from user_constraints
SQL> i
  2  where table_name in ('PARENT','CHILD');

TABLE_NAME                     CONSTRAINT_NAME                C
------------------------------ ------------------------------ -
CHILD                          SYS_C001881207                 R
PARENT                         SYS_C001881205                 C
PARENT                         PARENT_PK                      P




SQL> alter table CHILD add constraint CHILD_FK foreign key (col1_child) references parent(col1_parent) on delete cascade;

Table altered.


SQL> select * from parent;

COL1_PARENT
-----------
          2
          3
         10

SQL> select * from child;

COL1_CHILD
----------
         2
         3

SQL> delete parent;

3 rows deleted.

SQL> select * from child;

no rows selected


SQL> alter table CHILD drop constraint CHILD_FK;

Table altered.

SQL> alter table CHILD add constraint CHILD_FK foreign key (col1_child) references parent(col1_parent);

Table altered.

How to check and modify the AWR settings?

I've decided to write this post, since every time I need to check/modify the AWR snapshot settings, I never remember the exact views and procedures to use.
So here they are:

1) Getting the current AWR snapshot settings:

SQL> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL                                                               RETENTION                                                                   TOPNSQL
---------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- ----------
2560013153 +00000 00:10:00.0                                                           +00010 00:00:00.0                                                           DEFAULT


What this means is that the snapshots are running every 10 minutes and the retention time is 10 days.


2) Modifying the snapshot interval to every 15 minutes and retention time to 31 days only:

SQL> execute dbms_workload_repository.modify_snapshot_settings( interval => 15,retention => 44640);


 interval is in minutes, retention also.


SQL> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL                                                               RETENTION                                                                   TOPNSQL
---------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- ----------
2560013153 +00000 00:15:00.0                                                           +000031 00:00:00.0                                                           DEFAULT