SQL*Loader   «Prev  Next»

Report, Remove Unused Column in Oracle - Exercise

Dropping columns using commands

Objective: Write SQL commands to drop columns.

Background and Overview

You are going to write some SQL commands to drop columns or set columns to unused status. You will also answer a question about these commands.
  • Download files: None.

Instructions

Answer the questions below:
  1. Here is a query and the resulting report. Using the information shown here, write a set of SQL commands that drop all the unused columns from their corresponding tables. 
    <pre>
SQL> select * from DBA_UNUSED_COL_TABS;
OWNER TABLE_NAME COUNT
---------- ------------------------------
---------
PETSTORE PRODUCT 1
EPET1 CUSTOMER_ACCOUNT 2
WEBPETS PRODUCT_INVENTORY 3

  2. The PRODUCT_INVENTORY table is in heavy use during the day, but you want to drop the CREATED_TIME column from the table. How can you drop the column?
  3. Write the SQL commands you can use.

Submitting your Exercise

Type your answer into the text box and click the Submit button to submit your answer. Remember that you must submit all your responses to this exercise at once.
  1. Answer to question 1: 
    ALTER TABLE PETSTORE.PRODUCT DROP UNUSED COLUMNS;
ALTER TABLE EPET1.CUSTOMER_ACCOUNT DROP UNUSED COLUMNS;
ALTER TABLE WEBPETS.PRODUCT_INVENTORY DROP UNUSED COLUMNS;
  2. Answer to question 2:
    First, mark the column as unused during the day. Second, at night or over the weekend, drop the column.
  3. Answer to question 3: 
    ALTER TABLE PRODUCT_INVENTORY SET UNUSED COLUMN CREATED_TIME;
ALTER TABLE PRODUCT_INVENTORY DROP UNUSED COLUMNS;