Removing a column in table

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

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.

Do
alter table test drop column test_1;

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

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.

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

Sanjay

Quote:

---

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

Quote:

---

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

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

Quote:

---

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

The above don't proove anything except that you can rollback DROP COLUMN operation. But of course the operation itself generates undo information and requires rollback space. A lot of rollback space if you are dropping a column from a large table.

When you drop a column from a table, each and any block containing table rows is changed. The whole process can take a long time to complete. Immagine that you are dropping a column from a large table that can take an hour to complete. Suppose that after half an hour your database crashes, in the middle of your DROP COLUMN operation.

Now uppon the instance startup during the crash recovery Oracle must restore the table to the point as it was before you begun your DROP COLUMN operation as that information never completed! Where will it get the undo information? From the rollback segments, of course.

The fact that you can't rollback that operation once it has finished does not mean no information is written into rollback segments. Once it is finished you can't roll it back simply because of the implicit commit performed at the end of the operation to ensure that changes to the data dictionary are consistent and permanent, not because there is no information in the rollback segments.

Thanks a lot all of you for correcting me and letting me know the right thing.