Which three commands succeed on a read-only PRODUCTS table?
Short answer
CDE DROP TABLE, CREATE INDEX and DROP UNUSED COLUMNS all execute successfully while PRODUCTS is in read-only mode.
Question
Key Takeaways
It tests exactly which object-level and storage-level operations survive ALTER TABLE ... READ ONLY; the trap is assuming that anything affecting the table's storage (TRUNCATE, DROP COLUMN, SET UNUSED) is blocked when dropping unused columns and creating an index are still permitted.
Placing the PRODUCTS table in read-only mode blocks row-changing DML and most structural DDL, but a handful of commands still execute. This page confirms that DROP TABLE, CREATE INDEX and ALTER TABLE ... DROP UNUSED COLUMNS succeed on a read-only table, while TRUNCATE and DROP COLUMN fail with ORA-12081.
Common mistake: The most common wrong picks are A (ALTER TABLE ... DROP COLUMN) and B (TRUNCATE TABLE), because candidates over-generalize 'read-only' to mean 'no DDL at all'; both actually raise ORA-12081, whereas CREATE INDEX and DROP UNUSED COLUMNS are allowed.
Expert Analysis
Why the Answer Is Correct
Read-only mode is implemented by ALTER TABLE... READ ONLY and it restricts changes to a table's rows and its column structure — it does not make the object indestructible. C is correct because DROP TABLE products simply removes the segment: with the DROP ANY TABLE privilege the table can still be dropped even while it is flagged read-only. D is correct because CREATE INDEX price_idx ON products (price) builds a brand-new, separate index segment and does not touch a single row of PRODUCTS, so Oracle permits it on a read-only table. E is correct because those columns were already logically removed by a prior SET UNUSED; ALTER TABLE... DROP UNUSED COLUMNS only performs physical cleanup of columns that no longer exist in the table definition, so it is tolerated in read-only mode. All three are object- or segment-level operations rather than row-level modifications, which is precisely the distinction READ ONLY enforces.
Why the Other Options Are Wrong
A fails: ALTER TABLE products DROP COLUMN expiry_date is a structural change that rewrites the row image, so Oracle raises ORA-12081, operation not allowed on table PRODUCTS in read-only mode. B fails: TRUNCATE TABLE products also raises ORA-12081 — although TRUNCATE is classified as DDL, it deletes every row and resets the high-water mark, so it is treated as a data-changing operation on a read-only table. The related statement ALTER TABLE products SET UNUSED (expiry_date), which appears in the original question as another distractor option, is blocked for the same reason: marking a column UNUSED is structural DDL that changes the table definition. Only the three operations that either work outside the row data or complete a change that was already logically applied are accepted.
Community Comment Notes
bca123 captures the winning logic by noting that unused columns can be dropped "SINCE IT DOESNOT CHANGE THE STRUCTURE" — from the data dictionary's point of view the column is already gone. treat sharpens the same point, stating "U cant set the columns to unused for a read only table" while confirming that columns already marked unused can still be dropped, which is exactly the line Oracle draws between SET UNUSED and DROP UNUSED COLUMNS. Thameur01's selection included ALTER TABLE products SET UNUSED (expiry_date) alongside the index and unused-column drop; that statement is the trap in this question because read-only mode blocks it with ORA-12081, so it cannot be one of the three successful commands.
Official Reference
- https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ALTER-TABLE.html
- https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-INDEX.html
These links open the official vendor documentation in a new tab.
Exam Strategy
When you see a read-only table question, immediately sort the options into row-level/data-changing operations (INSERT, UPDATE, DELETE, MERGE, TRUNCATE, SET UNUSED, ADD/MODIFY/DROP COLUMN) and object- or segment-level operations (SELECT, DROP TABLE, CREATE INDEX, DROP UNUSED COLUMNS); only the second group survives. Memorize the ORA-12081 error text so you can recognize that TRUNCATE and DROP COLUMN are the distractors here.
Frequently Asked Questions
Why does TRUNCATE TABLE fail on a read-only PRODUCTS table?
Can ALTER TABLE ... SET UNUSED run on a read-only table?
Community Votes
100% of anonymous learners picked answer CDE. Votes are pick records left by other test-takers — they are not the verified answer.
Discussion 3 contributions
Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors.
From public community threads 3
-
Thameur01 👍 1D. CREATE INDEX price_idx ON products (price); E. ALTER TABLE products DROP UNUSED COLUMNS; F. ALTER TABLE products SET UNUSED (expiry_date);
-
treat 👍 1Since the columns are already set as unused u can drop them. U cant set the columns to unused for a read only table
-
bca123 👍 1WE CAN DROP THE UNUSED COLUMNS SINCE IT DOESNOT CHANGE THE STRUCTURE THE DATA STRUCTURE
Reader comments & corrections
No reader corrections yet — spotted an error or have a note? Share it below.