Re: Deleting columns in a table

You can drop column in 8i .

1.
alter table table_name DROP COLUMN col_name CASCADE CONSTRAINTS CHECKPOIN 1000;

Checkpoints are usefull when you have large tables and small rollbacks. 1000 - it means every 1000 records make a checkpoint. When this operation starts, table status is set to INVALID until this operation is finished.
If sth happens to DB in the mean time and the instance hase to go down, this table will be INVALID after bringing instance up. In this case, to resume this operation you have to do:

alter table table_name DROP COLUMNS CONTINUE;

This statement, however, will give you an error if the status of table_name is VALID.

2.
You can also set column as UNUSED.

alter table table_name SET UNUSED COLUMN col_name;

and
alter table table_name DROP UNUSED COLUMNS CHECKPOINT 1000;

and (in case of accident )
alter table table_name DROP COLUMNS CONTINUE CHECKPOINT 1000;

Here, the performance is much better. When you set the column as UNUSED, you Oracle will behave as if this column didn't exist (desc will not show it). Setting cols as UNUSED is much better when you want to delete more than one column from a table.

hope this help

"dgdss" <dgdss_at_hotmail.com> wrote in message news:dDs56.224493$_5.50585207_at_news4.rdc1.on.home.com...
> You are going to have to export the table and create a new table without
 th
> esaid column, then import into the new table. You can't just drop a
 column.
> "Dietrich Johnson" <djohnson_at_polaris.umuc.edu> wrote in message
> news:3A563BB3.6DFCEA0B_at_polaris.umuc.edu...
> > Is there an easy way to delete a column that has no constraints.
> >
>
>
Received on Sat Jan 06 2001 - 01:14:28 CET