drop a column

[lwangf]

Hi,

I want to drop a column, is anyone know the command to drop it?

Thanks !

[sysdba]

Drop column only works in Oracle 8i

[marist89]

alter table xyz drop column col1;

[TPS]

alter table tablename drop column columnname

[maske]

That is new feature in Oracle 8i.
Command: alter table xxx drop column xxx;

[sreddy]

ALTER TABLE table_name DROP column_name CASCADE CONSTRAINTS ;

Restrictions on the drop_column_clause:

Each of the parts of this clause can be specified only once in the statement and cannot be mixed with any other ALTER TABLE clauses. For example, the following statements are not allowed:

ALTER TABLE t1 DROP COLUMN f1 DROP (f2);
ALTER TABLE t1 DROP COLUMN f1 SET UNUSED (f2);
ALTER TABLE t1 DROP (f1) ADD (f2 NUMBER);
ALTER TABLE t1 SET UNUSED (f3)
ADD (CONSTRAINT ck1 CHECK (f2 > 0));

You can drop an object type column only as an entity. Dropping an attribute from an object type column is not allowed.

You can drop a column from an index-organized table only if it is not a primary key column. The primary key constraint of an index-organized table can never be dropped, so you cannot drop a primary key column even if you have specified CASCADE CONSTRAINTS.

You can export tables with dropped or unused columns. However, you can import a table only if all the columns specified in the export files are present in the table (that is, none of those columns has been dropped or marked unused). Otherwise, Oracle returns an error.

You cannot drop a column on which a domain index has been built.

You cannot use this clause to drop:

A pseudocolumn, clustered column, or partitioning column. (You can drop nonpartitioning columns from a partitioned table if all the tablespaces where the partitions were created are online and in read-write mode.)

A column from a nested table, an object table, or a table owned by SYS.

[marist89]

[QUOTE][i]Originally posted by sreddy [/i]
[B]You can export tables with dropped or unused columns. [/B][/QUOTE]

Of course this should read:
You can export tables with dropped or unused columns as long as you are not using a direct export (bug 1274582).

[sysdba]

Here is link from this site:

[url]http://www.dbasupport.com/oracle/ora8/page1.shtml[/url]