If you want to drop a column from a table, you can by creating a new table using the following steps:
- Create another table with all of the columns you want to retain
- Transfer the data from the original table to the new table with the SQL statement
INSERT
INTO FROM SELECT
- Drop the original table
- 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.
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
USER_UNUSED_COL_TABS, ALL_UNUSED_COL_TABS, and DBA_UNUSED_COL_TABS
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.