Re: Can we remove a column from a table??

Precisely what TOAD does. See Database|Rebuild Table function.

Sample output:

--
--  Table Rebuild script generated by TOAD
--
--  Original table: EMPLOYEE
--  Backup of table: EMPLOYEE_X1
--  Date: 02/13/2000 11:11:45 AM
--

--  Lock original table before rename

LOCK TABLE EMPLOYEE IN EXCLUSIVE  MODE ;


--  Make backup copy of original table

RENAME  EMPLOYEE TO EMPLOYEE_X1 ;


--  drop fKey constraint from DEMO.LINHAS_PROMOCAO
ALTER TABLE DEMO.LINHAS_PROMOCAO DROP CONSTRAINT LIN_PROM_IDENTIFICADO_POR2
;

--  drop fKey constraint from DEMO.CUSTOMER
ALTER TABLE DEMO.CUSTOMER DROP CONSTRAINT SYS_C00962 ;


--  Remove all other NAMED Table Constraints because
--  they will cause errors when re-creating the table
ALTER TABLE DEMO.EMPLOYEE_X1 DROP CONSTRAINT EMP_CHECK_TEST ;


--  Remove original Primary Key now that FKeys are dropped

ALTER TABLE DEMO.EMPLOYEE_X1 DROP CONSTRAINT SYS_C00958 ;


--  Recreate original table

CREATE TABLE DEMO.EMPLOYEE

  EMPLOYEE_ID     NUMBER(4)     NOT NULL,
  LAST_NAME       VARCHAR2(15),
  FIRST_NAME      VARCHAR2(15),
  MIDDLE_INITIAL  VARCHAR2(1),
  JOB_ID          NUMBER(3),
  MANAGER_ID      NUMBER(4),
  HIRE_DATE       DATE,
  SALARY          NUMBER(7,2),
  DEPARTMENT_ID   NUMBER(2),
   CONSTRAINT EMP_CHECK_TEST
          CHECK (employee_id <= 10000) )
 TABLESPACE USER_DATA PCTFREE 10
 STORAGE(INITIAL 10K NEXT 10K PCTINCREASE 50 ) ;


--   Copy the data from the renamed table

Insert into DEMO.EMPLOYEE (
EMPLOYEE_ID, LAST_NAME, FIRST_NAME, MIDDLE_INITIAL,
JOB_ID, MANAGER_ID, HIRE_DATE, SALARY,
DEPARTMENT_ID )
  SELECT
EMPLOYEE_ID, LAST_NAME, FIRST_NAME, MIDDLE_INITIAL,
JOB_ID, MANAGER_ID, HIRE_DATE, SALARY,
DEPARTMENT_ID
   FROM DEMO.EMPLOYEE_X1 ;

Commit ;

--    Recreate indexes EXCLUDING those created via Unique Constraints


--   Recreate the PKey Constraint

ALTER TABLE DEMO.EMPLOYEE ADD
  PRIMARY KEY ( EMPLOYEE_ID )
    USING INDEX  PCTFREE 10
      STORAGE(INITIAL 10240 NEXT 10240 PCTINCREASE 50 )
      TABLESPACE USER_DATA  ;


--   Recreate the FKey Constraints from the NEW table

ALTER TABLE DEMO.EMPLOYEE_X1 DROP CONSTRAINT SYS_C00959 ;

ALTER TABLE DEMO.EMPLOYEE ADD
 FOREIGN KEY (JOB_ID)
  REFERENCES DEMO.JOB (JOB_ID)  ;

ALTER TABLE DEMO.EMPLOYEE_X1 DROP CONSTRAINT SYS_C00960 ;

ALTER TABLE DEMO.EMPLOYEE ADD
 FOREIGN KEY (MANAGER_ID)
  REFERENCES DEMO.EMPLOYEE (EMPLOYEE_ID)  ;

ALTER TABLE DEMO.EMPLOYEE_X1 DROP CONSTRAINT SYS_C00961 ;

ALTER TABLE DEMO.EMPLOYEE ADD
 FOREIGN KEY (DEPARTMENT_ID)
  REFERENCES DEMO.DEPARTMENT (DEPARTMENT_ID)  ;


--   Recreate the FKey Constraints that reference the NEW table

ALTER TABLE DEMO.LINHAS_PROMOCAO ADD
 CONSTRAINT LIN_PROM_IDENTIFICADO_POR2
 FOREIGN KEY (COD_LINHA)
  REFERENCES DEMO.EMPLOYEE (EMPLOYEE_ID)  ;

ALTER TABLE DEMO.CUSTOMER ADD
 FOREIGN KEY (SALESPERSON_ID)
  REFERENCES DEMO.EMPLOYEE (EMPLOYEE_ID)  ;


--   Recompile any dependent objects

ALTER FUNCTION DEMO.SUM_SALARY COMPILE ;

ALTER PACKAGE  DEMO.TEST_PKG COMPILE PACKAGE ;

ALTER PROCEDURE DEMO.PROC_SUM_SALARY COMPILE ;

ALTER VIEW DEMO.GL_INTERFACE_SOURCE_V COMPILE ;


--   Grant any privs associated with the old table

--  *** Recompile triggers for the new table





--
Steve Chapman
TOAD Development Team
Quest Software, Inc.

www.toadsoft.com
www.quest.com



Paul Dorsey wrote in message ...


>If you are not on 8.1.5, you can't just

>

>RENAME old_name to new_name;

>CREATE TABLE old_name AS SELECT col1, col2 FROM new_name;

>DROP new_name;

>

>Mak sure you recreate all your constraints (PK,FK,check), indexes and

>storage parameters.

>

>--

>Paul Dorsey

>Dulcian, Inc.


>(212) 595-7223

>web address: http://www.dulcian.com

>email: pdorsey_at_dulcian.com

>

>

><pberetta_at_my-deja.com> wrote in message news:873re0$bdb$1_at_nnrp1.deja.com...

>> Peter,

>>    Unless you are using Oracle 8i, you cannot remove a column from a

>> table.  What you must do is something like this:

>>

>> RENAME old_name to new_name;

>> CREATE TABLE old_name AS SELECT col1, col2 FROM new_name;

>> DROP new_name;

>>

>> Not quite as simple as an ALTER TABLE, but it gets the job done.

>> Regards,

>> Paul

>>

>>

>> In article <38952DF3.9B9654EC_at_hmc.com.tw>,

>>   peter <misybh_at_hmc.com.tw> wrote:

>> > hi..

>> >        I  have a table named 'test' which has 3 columns..

>> >       Can  I  remove one column????

>> >

>> >      we all know that we can use SQL command to alter a table

>> >      but it seems that we only add or modify a column ..

>> >      How can we remove a column from a table???

>> >

>> >

>>

>>

>> Sent via Deja.com http://www.deja.com/

>> Before you buy.

>

>