Drop Columns

570672

I have an Oracle table with 60million records in it. I have about 35 columns in it and it takes about 40GB of tablespace in the database. I would like to drop some columns to reduce the table size (& also to reduce tablespace usage). Am using Oracle 9i and tried the drop column command. I get the ORA-30036 unable to extend the segment by 8192 in undo tablespace 'UNDOTBS1'. How to I overcome this issue? Is there any effecient way to drop columns from such a large table? Please advise

558383

See the relevant Admin. Guide section.

570672

I could not find any relevant information that I could use in my case. Please suggest any other effecient means to achieve the result.

561093

Hi,

Check the following solutions:

1) Create a new table with the required columns, drop the original and rename the new table with that of the original, or
2) Increase the size of your UNDOTBS1 tablespace and continue to drop the columns, or
3) Mark the columns which are not required as UNUSED and drop them later.

Regards

414042

I think what you are looking for is this little gem:

ALTER TABLE yourtable DROP UNUSED COLUMNS CHECKPOINT 250;

First make the columns you want to drop UNUSED. Then use the above command to get rid of them.

Per the docs:

"This option causes a checkpoint to be applied after processing the specified number of rows, in this case 250. Checkpointing cuts down on the amount of undo logs accumulated during the drop column operation to avoid a potential exhaustion of undo space."

http://download.oracle.com/docs/cd/B10500_01/server.920/a96521/tables.htm#21015

Happy Holidays!

Greg

Bolev

1) Create a new table with the required columns, drop
the original and rename the new table with that of
the original, or
2) Increase the size of your UNDOTBS1 tablespace and
continue to drop the columns, or
3) Mark the columns which are not required as UNUSED
and drop them later.

Still not clear what is the best method.
Probobly first one.

What is difference in performance of DROP UNUSED and ALTER ...DROP ?

Nicolas Gasparotto

DROP UNUSED drop the column previously marked as unused.
ALTER TABLE ... DROP drop immediately the column.
The two are consumming resource as well, but the first one has the advantage to mark column as unused (no resource consumption) and really drop the column later, when the database is less use.

Nicolas.

Bolev

DROP UNUSED drop the column previously marked as
unused.
ALTER TABLE ... DROP drop immediately the column.
The two are consumming resource as well, but the
first one has the advantage to mark column as unused
(no resource consumption) and really drop the column
later, when the database is less use.

Nicolas.

Bolev

DROP UNUSED drop the column previously marked as
unused.
(no resource consumption) and really drop the

column

later, when the database is less use.

Thanks Nicolas,

Actually after we 've made column UNUSED, we lost it.
Because if there is no resource consumption we also can just DROP when the database is less use. So there is no much logic to make it UNUSED and then DROP.
Do you think the method of creation a new table is best for large tables or there is something else (in terms of performance)?

bol

247823

Hi,

You can drop columns in a table....following are the options with examples.

Option1: To drop a single column
ALTER TABLE emp DROP COLUMN comm;

Option2: To drop more than one column
ALTER TABLE emp DROP (comm, mgr);

Regards,
Sailaja

Nicolas Gasparotto

>>Actually after we 've made column UNUSED, we lost it.
Right.

>>Because if there is no resource consumption we also can just DROP when the database is less use. So there is no much logic to make it UNUSED and then DROP.
I maybe not very well explained.
Yes there is logic, if you don't want anymore a column, but the application is in used, it can be interesting to set the column as "unused" - that doesn't consumme too much resource, and really drop the column later. Because drop a column implies a reading a every block of the table, which can slow down the database, and all the system.

Create a new table may be better for performance, if you're allowed to do this, that implies the tale is no more accessible during the operation. And constraints, indexes should be rebuild as well.

Nicolas.

Bolev

Trying to figure out which method people actually implement in real life
for large tables

I am actually using
[pr] DROP UNUSED COLUMNS CHECKPOINT...[pr]
for mid size tables but not shure it's the best method for large ones

bol

Nicolas Gasparotto

Trying to figure out which method people actually implement in real life for large tables

I don't understand your comment, I tried to explain you what is commonly used, but you have to know some theorical point, which I tried to point out you.
Create an other table with columns as well is a way, only during a down time, your application won't be able to read the table...
Now, you have always the good choice to run tests, and choose by yourself the best method regarding your constraint, or at least the availability constraint of the database.

Nicolas.

570672

Thank you all for your responses. I tried setting a column unused and then ran the command to drop the unused column with a checkpoint of 250. The SQL command ran for 3 days and still it was running with no signs of an end. So I had to invariably kill the session.
I have about 35 columns to drop on this table and can't seem to figure out how I could do it effeciently. Moreover, I do not have the choice of creating a new table due to lack of tablespace. So I will have to drop the table and load the table from scratch which is a time consuming process. Would appreciate any other quick solutions from the folks.

Message was edited by:
user567669

Nicolas Gasparotto

I have about 35 columns to drop on this table

How is big your table ?
Cannot you live with unused columns until time which allowed you to create new table ?
Or rename the table, create a view with the old table name only with the wanted columns.

Nicolas.

520094

Hi,

I've more than 190,00,000 rows in a Table, Total 22 Columns and using Orcle 9i.
The particular column is LAST Column and have NOT NULL Constraint (Means, Column contains data).

I just issued below commands.

Alter Table pk_ocp.Case_History
Set Unused Column x_case_checked_by;


Table Altered.
Output "Table altered" appeared just with in microseconds.


But When i issued Command

Alter Table pk_ocp.Case_History
Drop unused Columns;


Table Altered.

Output "Table altered" appeared exact by system time 03 Minutes and few seconds.



I think with respect to data, timings are ok.

I would suggest Do as "N. Gasparotto" Suggests.


...