Forum Stats

  • 3,724,464 Users
  • 2,244,764 Discussions
  • 7,851,024 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Drop Columns

570672
570672 Member Posts: 86
edited December 2007 in SQL & PL/SQL
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

Comments

  • 570672
    570672 Member Posts: 86
    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
    561093 Member Posts: 2,146
    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
    414042 Member Posts: 322
    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
    Bolev Member Posts: 566
    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
    Nicolas Gasparotto Member Posts: 25,514 Silver Crown
    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
    Bolev Member Posts: 566
    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
    Bolev Member Posts: 566
    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
    247823 Member Posts: 271
    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
    Nicolas Gasparotto Member Posts: 25,514 Silver Crown
    >>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
    Bolev Member Posts: 566
    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
    Nicolas Gasparotto Member Posts: 25,514 Silver Crown
    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
    570672 Member Posts: 86
    edited December 2007
    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
    Nicolas Gasparotto Member Posts: 25,514 Silver Crown
    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
    520094 Member Posts: 66
    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.


    ...
This discussion has been closed.