-------------------------------------------------------
    SQL-script to DROP field from an ORACLE table
-------------------------------------------------------
Tablename: DEPT
Colunname: DNAME (must be removed from table)

1). Copy table DEPT to DEPT_TMP without field DNAME
----------------------------------------------------
CREATE TABLE dept_tmp
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
STORAGE(INITIAL         10000
        NEXT            10000
        MINEXTENTS      1
        MAXEXTENTS      UNLIMITED
        PCTINCREASE     0
        FREELISTS       1)
TABLESPACE tab
AS SELECT deptno,          
          loc          
FROM dept;

2). Drop all constraints referencing orign table DEPT
-----------------------------------------------------
ALTER TABLE emp DROP CONSTRAINT emp_foreign_key;
ALTER TABLE dept DROP CONSTRAINT dept_primary_key;

3). Drop origin table DEPT
--------------------------
DROP TABLE dept;

4). Rename table DEPT_TMP to new table DEPT
-------------------------------------------
RENAME dept_tmp to dept;

5). Drop all all auomatically renamed constraints in new table DEPT
-------------------------------------------------------------------
SPOOL drop_constraints.sql
SELECT 'ALTER TABLE dept DROP CONSTRAINT '||constraint_name||';'
  FROM user_constraints
 WHERE UPPER(table_name) = 'DEPT';
SPOOL OFF;
start drop_constraints.sql

6). Rebuild constraints on new table DEPT
-----------------------------------------
ALTER TABLE dept MODIFY (deptno CONSTRAINT nn_dept_deptno NOT NULL);
ALTER TABLE dept ADD
  CONSTRAINT dept_primary_key PRIMARY KEY (deptno)
  USING INDEX
  TABLESPACE idx
  STORAGE
 (INITIAL       500K
  NEXT          500K
  MINEXTENTS    1
  MAXEXTENTS    UNLIMITED
  PCTINCREASE   0
  FREELISTS     2);

7). Rebuild Foreign Keys in all tables referencing table DEPT
---------------------------------------------------------------
ALTER TABLE emp
  ADD (CONSTRAINT emp_foreign_key FOREIGN KEY (deptno)
  REFERENCES dept (deptno));

8). Rebuild Indexes in all tables referencing table DEPT
--------------------------------------------------------
CREATE INDEX emp_deptno_idx ON emp (deptno)
  TABLESPACE idx
  STORAGE (INITIAL 500K NEXT 500K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0)
  PCTFREE 10;

9). Grant all privileges again
------------------------------
GRANT SELECT,INSERT,UPDATE,DELETE ON dept TO .....;