Report and Remove Unused Columns in Oracle 26ai - Exercise

Objective: Interpret an unused-column report, write schema-qualified cleanup commands, and plan the staged removal of a column from a busy table.

Background and Assumptions

You administer an Oracle 26ai application PDB containing three application schemas. The report below lists columns already marked UNUSED. A separate approved change will retire CREATED_TIME from WEBPETS.PRODUCT_INVENTORY.

Assume ordinary internal heap-organized tables that support the illustrated operations, appropriate ALTER privileges on each table, and authorized access to DBA_UNUSED_COL_TABS. CREATED_TIME is still an accessible column at the start, is not a key or partitioning column, and has no indexes or constraints requiring additional handling. In particular, it has no DEFERRABLE constraint. Application dependencies have been reviewed and will stop using it before the change.

This is an unscored written exercise. Submit SQL and explanations for comparison; the website does not execute SQL. No download or simulation is required.

Report

SELECT owner, table_name, count AS unused_column_count
FROM dba_unused_col_tabs
WHERE (owner = 'PETSTORE' AND table_name = 'PRODUCT')
   OR (owner = 'EPET1' AND table_name = 'CUSTOMER_ACCOUNT')
   OR (owner = 'WEBPETS' AND table_name = 'PRODUCT_INVENTORY')
ORDER BY owner, table_name;

Supplied exercise snapshot, before any changes
OWNER	TABLE_NAME	UNUSED_COLUMN_COUNT
PETSTORE	PRODUCT	1
EPET1	CUSTOMER_ACCOUNT	2
WEBPETS	PRODUCT_INVENTORY	3

Instructions

Using the snapshot, write three ALTER TABLE statements to remove all currently unused columns from the listed tables during an approved maintenance window.
For the busy PRODUCT_INVENTORY table, describe how to make CREATED_TIME inaccessible first while deferring physical removal. Explain the application coordination required and whether the first step can be reversed with ROLLBACK or SET USED.
Write the schema-qualified SQL for that two-stage change. Use the supported ONLINE option for the initial SET UNUSED operation under the assumptions above. Then write the later cleanup statement and reuse the report query to verify the counts. State the expected count after each stage.

Questions 2–3 form a separate scenario. Start from the supplied snapshot as though Question 1's commands have not been executed. No other administrator changes the unused columns during this scenario. The cleanup must account for the three columns already unused, not just CREATED_TIME.

Hints

DBA_UNUSED_COL_TABS reports counts, not the original names of unused columns. Keep all schema names in your DDL. An approved maintenance window is determined by workload and dependencies, not simply by whether it is nighttime.

Submit Your Answers

Enter numbered answers to all three questions. Separate the initial change from the later maintenance command.