oracle数据库删除某一列,如何删除已经有数据表中的列？

Prior to Oracle8i, the only way to remove a column was to re-create the table

without the column name. In Oracle8i, you have the much awaited option of

dropping a column. You can drop a column that is not used immediately, or

you can mark the column as not used and drop it later.

Here is the syntax for dropping a column:

ALTER TABLE [schema.]table_name

DROP {COLUMN column_name |(column_names)}[CASCADE

CONSTRAINTS ]

DROP COLUMN drops the column name specified from the table. You can pro-vide

more than one column name separated by commas inside parentheses. The

indexes and constraints on the column are also dropped. You must specify

CASCADE CONSTRAINTS if the dropped column is part of a multicolumn con-straint;

the constraint will be dropped.

The syntax for marking a column as unused follows:

ALTER TABLE [schema.]table_name

SET UNUSED {COLUMN column_name |(column_names)}[CASCADE

CONSTRAINTS ]

You usually mark a column as unused and not dropped if the table is very

large and takes a lot of resources at peak hours. In such cases, you would

mark the column as unused and drop it later. Once the column is marked as

unused, you will not see it as part of the table definition. Let’s mark the

UPDATE_DT column in the ORDERS table as unused:

SQL>ALTER TABLE orders SET UNUSED COLUMN update_dt;

Table altered.

SQL>DESCRIBE orders

Name Null?Type

-------------------------------------------------------

ORDER_NUMBER NOT NULL NUMBER(8)

STATUS VARCHAR2(15)

ORDER_DATE DATE

QUANTITY NUMBER(10,3)

SQL>

The syntax for dropping a column already marked as unused is

ALTER TABLE [schema.]table_name

DROP {UNUSED COLUMNS |COLUMNS CONTINUE}

Use the COLUMNS CONTINUE clause to continue a DROP operation that was

previously interrupted. To clear data from the UPDATE_DT column from

the ORDERS table, do this:

ALTER TABLE orders DROP UNUSED COLUMNS;