i think D is correct

C is correct.

Testet: OBJECT_ID LOCKED_MODE ---------- ----------- 85674 3 85676 3 Only rows locked.

Answer is C The database acquires a full table lock on the child table when no index exists on the foreign key column of the child table, and a session modifies a primary key in the parent table (for example, deletes a row or modifies primary key attributes) or merges rows into the parent table. When both of the following conditions are true, the database acquires a full table lock on the child table: No index exists on the foreign key column of the child table. A session modifies a primary key in the parent table (for example, deletes a row or modifies primary key attributes) or merges rows into the parent table.

When you delete row from parent table, you can check in select * from v$locked_object; in this query you can use object_id numbers in dba_objects to identify table names. For example select*from dba_objects where object_id in(79902,79900); that both table locked, so A is not correct in my opinion. D is not correct because after delete operation both tables locks not only rows which deleted, you can not update other rows two. In C the employees table is child table and we cannot update there rows or insert due to parent table, so I think the answer is B.

Answer is C Because- Locks and Unindexed Foreign Keys The database acquires a full table lock on the child table when no index exists on the foreign key column of the child table, and a session modifies a primary key in the parent table (for example, deletes a row or modifies primary key attributes) or merges rows into the parent table. https://docs.oracle.com/database/121/CNCPT/consist.htm#CNCPT1351 in section "Locks and Foreign Keys"

The key point of this question is the ON DELETE CASCADE option. If the primary key of the parent table is deleted (note that the primary key constraint is not deleted here), then the corresponding row of the child table will also be deleted, so when deleting the primary key of the parent table , The sub-table is locked, the purpose is to delete the corresponding row of the sub-table at the same time. Because Oracle deletes rows based on row-level locks. The test proved that it is possible to delete rows on other sub-tables.

if no index for that FK constraint at table employee the lock will be table wise

I think it's A Reference: https://docs.oracle.com/database/121/CNCPT/consist.htm#CNCPT1351 in section “Locks and Indexed Foreign Keys” A is correct as “Figure 9-4 Locking Mechanisms with Indexed Foreign Key” and its description indicate that the lock at table level only occurs on the parent table. Answers B and C are ruled out because they both indicate that “the locking allows query but not update the parent or child tables (DML)”, the reference however states that “A lock on the parent table prevents transactions from acquiring exclusive table locks, but does not prevent DML on the parent or child table during the primary key modification”.

I think it's C