Drop unwanted columns from a table

Prior to version 8 the only way to really drop unused columns always involved creating a second table into which contained the required columns only, the originally was then dropped and finally the new table was renamed to be the original. This was always tedious hence on many sites , these redundant columns where just ignored.

On Oracle 8i it is possible to drop unnecessary columns from a table. Consider the examples below.

  

Create test table 


CREATE TABLE test_tab 
(
        seq_no     NUMBER,
        name      VARCHAR2(100),
        test_col  VARCHAR2(100)
)
/

METHOD 1: Set/Drop Unused Columns

ALTER TABLE test_tab SET UNUSED COLUMN test_col;

SELECT * from sys.dba_unused_col_tabs;

ALTER TABLE test_tab DROP UNUSED COLUMNS;

METHOD 1: Drop Column CASCADE CONSTRAINTS

ALTER TABLE test_tab DROP COLUMN test_tab CASCADE CONSTRAINTS;
Warning: Before dropping columns ensure you have adequate backups for recovery. Ensure routines that rely on table have not got columns hard coded. You know it makes sense.
 

This script is provided for educational purposes only. The script has been tested and appears to work as intended. However, you should always test any script before relying on it. No responsibility will be accepted for Lost or damage that may occur from it's use.