The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,685 other followers

Archive for February 18th, 2016

Oracle alter table drop column tips: unused columns

Posted by jpluimers on 2016/02/18

An interesting tip at alter table drop column tips for Oracle: unused columns.

I knew about the multi-column syntax for drop column, but the unused columns:

You can also drop a table column by marking it unused and then dropping the column, thusly:

alter table
    table_name
set unused column
    column_name
;

They will appear in the DBA_UNUSED_COL_TABS view.

After that:

alter table
    table_name
drop unused columns
;

You can perform the first statement for a few columns over time, then the last statement will delete them all.

Together with some other views like described in places like oracle – How to check if a column exists before adding it to an existing table in PL/SQL?, you can create nifty scripts for your meta-data maintenance.

–jeroen

via: alter table drop column tips.

Posted in Database Development, Development, OracleDB | Leave a Comment »

 
%d bloggers like this: