Managing Tables   «Prev 

Dropping columns from a table

If you want to drop a column from a table, you can by creating a new table using the following steps:
  1. Create another table with all of the columns you want to retain
  2. Transfer the data from the original table to the new table with the SQL statement INSERT…INTO FROM SELECT…
  3. Drop the original table
  4. Use the ALTER TABLE command to change the name of the new table to the name of the original table
Do not "COMMIT" this transaction until you successfully rename the new table, otherwise you will lose data.

Dropping a Column

You can use the table reorganization options to drop columns (because the source and target can have different column definitions). As an alternative, you can mark columns to have an unused state during regular usage, then drop them when a longer maintenance window is available. For example, you can mark the Wind column as unused:
alter table TROUBLE 
set unused column Wind;

Marking a column as unused does not release the space previously used by the column until you drop the unused columns: alter table TROUBLE drop unused columns; You can query

to see all tables with columns marked as unused.
Note: Once you have marked a column as unused, you cannot access that column.
You can drop multiple columns in a single command, as shown in the following listing:
alter table TROUBLE 
drop (Condition, Wind);

Note: When dropping multiple columns, you should not use the column keyword of the alter table command; it causes a syntax error. The multiple column names must be enclosed in parentheses, as shown in the preceding listing. If the dropped columns are part of primary keys or unique constraints, you will need to also use the cascade constraints clause as part of your alter table command. If you drop a column that belongs to a primary key, Oracle will drop both the column and the primary key index.