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,762 other followers

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

 
%d bloggers like this: