how can i drop a column with sql? 2

[mokesql]

hi!
please help me how i can drop a column with sql. thx for the quick answer

kemo

 

[markco]

ALTER TABLE TABLE_NAME SET UNUSED (COLUMN_NANE
ALTER TABLE TABLE_NAME DROP UNUSED COLUMNS;

Where the TABLE_NAME is your target table,
COLUMN_NAME is your target column.

If there is a CONSTRAINT, you should use 'CASCADE CONSTRAINT' also.

 

[Kenrae]

Alter table table_name drop column column_name;

 

[markco]

better to set unused, since drop column will have heavy server workload. Drop unused column when server are free, never direct drop column when server busy.

 

[rcurva]

For versions 7.3.x and 8.0.x there are no SQL commands to delete a column but there is a workaround:

1. Perform export of your TABLE. Please refer to Oracle
Utility Guide for exporting instructions.

2. Create a view referencing all the columns desired minus
the one that you want to drop.

SQL>CREATE VIEW <VIEWNAME> AS SELECT COL1,COL2.. FROM SQL>TABLE;

3. Once the view is created perform a query on that view to
verify that the columns are exactly how you want the new
revised table to look.

4. Issue the following command to create the new table:

SQL>CREATE TABLE <NEWNAME> AS SELECT * FROM <VIEWNAME>;

5. Previous command should have created a new table minus
the column(s) that you wanted to delete.

6. Next step is to DROP the old table(the original with the
column not desired).

7. Once that table is dropped then you can rename the new
table to the original table name with the following command:

SQL> RENAME <newtable> to <orginaltable>;

Note that the <newtable> is the table that was created
from the view and the <originaltable> is the table
that was dropped.

But if you're in 8i, the first solutions given can be applied.