Removing a column in table

[Helix]

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

[marist89]

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.

[Tarry]

Do
alter table test drop column test_1;

checkout..
http://otn.oracle.com/docs/products/...2a.htm#2055873

[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.

[SANJAY_G]

Prior to 8i
Create table as select.
Drop original table.
Rename the new table to original.
Create indexes and constraints.

Sanjay

[ronnie]

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 ????

[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.

[ronnie]

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

[marist89]

Clearly, Undo is used: http://otn.oracle.com/docs/products/...2a.htm#2055873

[ronnie]

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