Removing a column in table
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Removing a column in table

  1. #1
    Join Date
    Mar 2002
    Posts
    29
    Hello all,

    Ok i have a little problem (problem to me). I want to remove a column from a table without removing data from any other columns.

    This is little urgent, so any help will be apriciated.

    Thanks

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Since you didn't specify your version, I'll assume your're on Solaris 2.8 and 8.1.7.

    You can drop a column by the ALTER TABLE command. See syntax for details.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,591

    Do
    alter table test drop column test_1;

    checkout..
    http://otn.oracle.com/docs/products/...2a.htm#2055873
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  4. #4
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    In 8.1.7 Oracle RECOMMENDS making the column UNUSABLE first before the DROP. Remember to have a lot of ROLLBACK SEGMENT space available for the DROP. There is a CHECKPOINT option in the DROP syntax, more appropratle would've been a COMMIT=number option. I dropped a column from one table.... 6 GB of rollback later... it was gone.
    OCP 8i, 9i DBA
    Brisbane Australia

  5. #5
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Prior to 8i
    Create table as select.
    Drop original table.
    Rename the new table to original.
    Create indexes and constraints.

    Sanjay

  6. #6
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Originally posted by grjohnson
    In 8.1.7 Oracle RECOMMENDS making the column UNUSABLE first before the DROP. Remember to have a lot of ROLLBACK SEGMENT space available for the DROP. There is a CHECKPOINT option in the DROP syntax, more appropratle would've been a COMMIT=number option. I dropped a column from one table.... 6 GB of rollback later... it was gone.

    why do we require a lot of rollback space to issue a drop command.

    This operation cannot be rollbacked ????
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  7. #7
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Ronnie, I know, but every record is changing therefore redo will be generated for the 'before view' of the row. Even if it can't be recovered. As far as I'm aware, you can't perfom a NOLOGGING on a DROP COLUMN either... which is a right pain in the butt.
    OCP 8i, 9i DBA
    Brisbane Australia

  8. #8
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Originally posted by grjohnson
    Ronnie, I know, but every record is changing therefore redo will be generated for the 'before view' of the row. Even if it can't be recovered. As far as I'm aware, you can't perfom a NOLOGGING on a DROP COLUMN either... which is a right pain in the butt.
    I still believe that Rollback wont be generated as there wont be a before view at all in this case.


    Others please correct me if I am wrong.

    Thanks
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  9. #9
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  10. #10
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Originally posted by marist89
    Clearly, Undo is used: http://otn.oracle.com/docs/products/...2a.htm#2055873

    SQL> create table testaaa ( a number,
    2 b number);

    Table created.

    SQL> alter table testaaa drop column b;

    Table altered.

    SQL> desc testaaa
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    A NUMBER

    SQL> rollback;

    Rollback complete.

    SQL> desc testaaa
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    A NUMBER
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

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