Oracle 1Z0-071: True about unused columns?

Table of Contents

Question
Answer
Explanation
Reference

Question

Which two are true about unused columns? (Choose two.)

A. Setting an indexed column to unused results in an error.
B. You can query the data dictionary to see the names of unused columns.
C. You can specify multiple column names in an ALTER TABLE…SET UNUSED statement.
D. If you set all the columns of a table to unused, the table is automatically dropped.
E. CASCADE CONSTRAINTS must be specified when setting a column to unused if that column is referenced in a constraint on another column.

Answer

B. You can query the data dictionary to see the names of unused columns.
E. CASCADE CONSTRAINTS must be specified when setting a column to unused if that column is referenced in a constraint on another column.

Explanation

The two statements that are true about unused columns are:

Let’s explain each of these statements in detail:

B. You can query the data dictionary to see the names of unused columns:
In Oracle, the data dictionary contains metadata information about the database objects, including tables and columns. When a column is set to unused using the ALTER TABLE…SET UNUSED statement, you can query the data dictionary views to see the names of unused columns. The relevant view is USER_UNUSED_COL_TABS, which provides information about the tables and columns that have been marked as unused. By querying this view, you can identify the unused columns in a table.

E. CASCADE CONSTRAINTS must be specified when setting a column to unused if that column is referenced in a constraint on another column:
When you set a column to unused, Oracle checks if the column is referenced in any constraints defined on the table. If there are constraints that reference the column, you must specify the CASCADE CONSTRAINTS clause along with the ALTER TABLE…SET UNUSED statement. This ensures that any dependent constraints are also dropped along with the unused column. By using CASCADE CONSTRAINTS, you maintain referential integrity and avoid potential errors caused by leaving orphaned constraints in the database.

Now, let’s evaluate the other options:

A. Setting an indexed column to unused results in an error:
This statement is not true. Setting an indexed column to unused does not result in an error. You can set an indexed column to unused, and it will be marked as unused in the data dictionary. However, the index on that column will still exist, and it will continue to consume space. To reclaim the space used by the index, you need to explicitly drop the index.

C. You can specify multiple column names in an ALTER TABLE…SET UNUSED statement:
This statement is not true. The ALTER TABLE…SET UNUSED statement is used to mark a single column as unused. If you want to mark multiple columns as unused, you need to execute separate ALTER TABLE…SET UNUSED statements for each column.

D. If you set all the columns of a table to unused, the table is automatically dropped:
This statement is not true. Setting all the columns of a table to unused does not result in the automatic dropping of the table. The table remains in the database, but the columns marked as unused are no longer visible in queries and consume no space.

In summary, the two true statements about unused columns in Oracle are:

Reference

Oracle Database 12c SQL 1Z0-071 certification exam practice question and answer (Q&A) dump with detail explanation and reference available free, helpful to pass the Oracle Database 12c SQL 1Z0-071 exam and earn Oracle Database 12c SQL 1Z0-071 certification.