drop column question
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: drop column question

  1. #1
    Join Date
    Jun 2000
    Location
    dumfries,va,usa
    Posts
    227
    Hi all,

    I issued the following command on oracle7.3.4 and got an error message:

    SQL> alter table test3
    2 drop column control_file;
    drop column control_file
    *
    ERROR at line 2:
    ORA-00905: missing keyword

    Does anyone know what the problem is?

    Thanks,
    leonard905
    leonard905@yahoo.com

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    ALTER TABLE DROP COLUMN is not available in 7.3 (or even in 8.0.*), that's the problem. You can't drop it without recreating the whole table.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    alter table ... drop column ....;
    avaible only in 8i and high.

    u should create script for drop columns:
    ( i can't remember from that version i can use RENAME TABLE command, then i write 2 scripts skeletons)

    --- with RENAME TABLE ----

    1. create table tmp_... ( list of fields without control_file);
    2. insert into tmp_... ( list of fields without control_file) select ( list of fields without control_file);
    RENAME test3 to old_test3;
    RENAME tmp_test3 to test3;
    -- drop table old_test3;

    --- without RENAME TABLE ----

    1. create table tmp_... ( list of fields without control_file);
    2. insert into tmp_... ( list of fields without control_file) select ( list of fields without control_file);
    -- check data in tmp_... table
    3. drop table test3;
    4. create table test3 as select * from tmp_...;
    5. drop table tmp_...;

    good luck.

    PS : don't forgret about indexes, triggers, constraints ...


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width