Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2005
    Posts
    26

    Unanswered: Dropping column use rollbacks?

    If I drop a column from a 200g partitioned table, will it use a rollback segment(s) to accomplish this?

    CougarTrace

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I'd say not, as DDL commands can not be rolled back. Why? Because every DDL command performs implicit COMMIT after its execution.

  3. #3
    Join Date
    Oct 2005
    Posts
    26

    But...

    when we drop the column, won't it have to go through the table and drop the data from every row? I would think this would suggest rollbacks would get involved.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Not every delete includes rollback; what about using the "TRUNCATE TABLE table_name"?

    I think rollback segment is not used. Perhaps you'll hear different answer from someone else.

  5. #5
    Join Date
    Oct 2005
    Posts
    26

    thanks

    thanks for info. I appreciate the help.

  6. #6
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    If you do drop column set unused then there should only be a very small amount of undo as it just does a change in the data dictionary. However if you do drop column then it will generate undo and redo as it clears the data out and will take a long time . To reduce the amount of undo generated there is a checkpoint clause which defaults to do doing a checkpoint every 512 rows.

    NOTE if you have to abort the operation then V_$FAST_START_TRANSACTIONS will give you an idea of how long it will take to rollback.

    Alan

  7. #7
    Join Date
    Oct 2005
    Posts
    26

    yes..however

    this column is a clob column and we are dropping to recover space. This function will need do what is necessary to free space.

  8. #8
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    One thing you may wish to try is to use dbms_redefinition if you have the disk space as dropping a column on a prod system may cause locking issues if the table is sill being accessed.

    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •