Re: "alter table <tabname> drop unused columns"

Chris,

You must set column unused first to drop them later. "drop unused" command will not drop empty columns.

"Columns in a table can be marked as unused. Unused columns are treated as if they were dropped, even though their column data remains in the table's rows. This feature is useful when you just don't want to drop the column during peak periods and want to remove the column from regular access.

Until you actually drop the columns, the columns are counted towards the Overall Column Limit (1000) in Oracle 8.1 and also if you have a long column set to unused, you cannot add another long column until you are have dropped the old one. The dictionary views dba_unused_col_tabs and user_unused_col_tabs can be used to view the columns that are currently marked as unused.

Dropping a column or Unused Column from a Table

This feature drops the column from a table and releases any space back to the segment. When you use the drop column clause, it will also drop any columns that were previously marked as being unused. This clause also causes any indexes, constraints and statistics on this column to be dropped

Alter table dbatest drop unused columns;

We just wanted to go one step ahead and drop all columns and Oracle reports a ORA-12983-Cannot drop all columns in a table.

Restrictions

You cannot combine drop an set unused clauses in the same statement. Also you cannot drop a column from an object type table or a nested table or a partitioning key column or a parent key column. Another good restriction is that you cannot drop a column from any of the tables owned by sys."

HTH Vladimir Ivanovich

Alter table dbatest set unused ( c3 );

"C. R. Soza" <crsoza_at_hotmail.com> wrote in message news:c18ea449.0108290629.541a961c_at_posting.google.com...
> Hello,
>
> We are hitting a problem as described in oracle note: 148740.1.
> The workaround Oracle has suggested is to issue:
> "alter table <tabname> drop unused columns".
>
> My question is, is there any danger in issueing this command ?.
> Will it drop all empty columns from the table ?. or will it only drop
> columns which have been previously marked as unused. In our case we
> haven't marked any columns as unused.
>
> Any clarification of the above command and dangers would be much
> appreciated
>
> Rgds
>
> Chris
Received on Wed Aug 29 2001 - 19:43:22 CEST