1Z0-052 · Question #32
You execute this command to drop the ITEM table, which has the primary key referred in the ORDERS table: SQL> DROP TABLE scott.item CASCADE CONSTRAINTS PURGE; Which two statements are true about the…
The correct answer is A. No flashback is possible to bring back the ITEM table D. The dependent referential integrity constraints in the ORDERS table are removed. DROP TABLE with CASCADE CONSTRAINTS permanently removes the table and drops all referential integrity constraints in dependent tables. The PURGE option bypasses the recycle bin, making flashback recovery impossible.
Question
You execute this command to drop the ITEM table, which has the primary key referred in the ORDERS table:
SQL> DROP TABLE scott.item CASCADE CONSTRAINTS PURGE; Which two statements are true about the effect of the command? (Choose two.)
Options
- ANo flashback is possible to bring back the ITEM table
- BThe ORDERS table is dropped along with the ITEM table
- CThe dependent referential integrity constraints in the ORDERS table are disabled
- DThe dependent referential integrity constraints in the ORDERS table are removed
- EThe table definition of the ITEM table and associated indexes are placed in the recycle bin
How the community answered
(18 responses)- A78% (14)
- B11% (2)
- C6% (1)
- E6% (1)
Why each option
DROP TABLE with CASCADE CONSTRAINTS permanently removes the table and drops all referential integrity constraints in dependent tables. The PURGE option bypasses the recycle bin, making flashback recovery impossible.
The PURGE keyword instructs Oracle to skip the recycle bin and immediately deallocate all storage associated with the ITEM table. Because no entry is placed in the recycle bin, the FLASHBACK TABLE command cannot be used to recover the table.
CASCADE CONSTRAINTS only removes the referential integrity constraints that point to ITEM; it does not drop the ORDERS table itself or any other dependent tables.
The constraints in ORDERS are dropped entirely by CASCADE CONSTRAINTS, not merely disabled; disabling would leave the constraint definition in place, which is not what this clause does.
CASCADE CONSTRAINTS causes Oracle to drop - not disable - every foreign key constraint in other tables (such as ORDERS) that references the primary key of the ITEM table. The constraints are permanently removed from the data dictionary.
The PURGE option explicitly prevents the table from going to the recycle bin, so neither the table definition nor its indexes are placed there.
Concept tested: Oracle DROP TABLE CASCADE CONSTRAINTS PURGE behavior
Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/DROP-TABLE.html
Topics
Community Discussion
No community discussion yet for this question.