DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Drop column generates undo?

  1. #1
    Join Date
    Jan 2001
    Posts
    3,134

    Drop column generates undo?

    Well, I am RT'ing the FM and there does not appear to be a way to stop a column drop from generating undo.
    Am I missing and double secret oracle parameters, i'd like to avoid this undo if possible.

    MH
    I remember when this place was cool.

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    You can't stop generating undo for any operation, DROP COLUMN is not an exception here. It's simply not possible.

    Perhaps you are looking for a way to minimize *redo* generation - generaly known as nologging (or unrecoverable). AFAIK this is not possible for DROP COLUMN.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Jan 2001
    Posts
    3,134
    I thought so, this is going to be a problem because this table is large and the box it is on now has limites resources (space).

    We may wind up unloading the data, dropping and re-creating the table.

    I am testing this now, we'll see.

    Thanks.

    MH
    I remember when this place was cool.

  4. #4
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    you could always mark the column as unusable and later drop the column off hours
    I'm stmontgo and I approve of this message

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    or:
    1. take a cold backup
    2. put your db in noarchivelog mode
    3. drop your column
    4. put your db in archivelog mode
    5. take a backup
    Jeff Hunter

  6. #6
    Join Date
    Jan 2001
    Posts
    3,134
    Thanks mongo, but the hours are not the problem. This will be for our release in July and we do this on the weekend. This is not a 24X7 box, the problem is that they a running out of space and I am not sure that they have enough disk space for the amount of undo that multiple column drops will generate.
    I would assume that the undo will re-use itself after each column drop. So I need to test this NOW in development, we do not want any surprises on release (production) weekend.


    My benchmark table has 3.6 million records and we dropped 12 columns. That generated 3 gigs of undo. Production will have an estimated 29 million records by July. The server (Windoze) has about 10 gigs free space on it, nice huh?

    I have been telling the Applications group that they need more DASD for about a year now. Now it is biting them in the arse!!

    AND YOU ALL WONDER WHY I HATE DEVELOPERS!!


    Ps, we are not in archive log mode but thanks Jeff, nice to see you again.




    MH
    I remember when this place was cool.

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Brain fart. I thought you were worrying about excessive redo. Pay no mind to me...
    Jeff Hunter

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    I would like to do the following steps.

    1. Mark all indexes as unusable

    2 Create temp table (only needed columns) as select in nologging and parallel options

    3 Truncate the orignal table

    4 Insert /*+ append */ into orig table in nologging mode from the temp table in parallel.

    5 Rebuild indexes.

    I would always give least/last priority for exp/imp. The reasons are many. Some thing may go wrong in exp/imp.

    You can also consider "ALTER TABLE MOVE" option.

    By the way, where is New Joisey? Is it in Europe?

    Tamil

  9. #9
    Join Date
    Jan 2001
    Posts
    3,134
    Well, er no.

    The apps need to do a data (scrub) conversion as well. So we are now looking at the possibilty of having them scrub the data using sqlloader on the way out. Dropping and re-creating the table, loading the data and building the indexes. This would be ideal but on a Windoze 2 CPU machine it could take three months to unload/scrub/load the data.

    Joisey is north of Jawga.

    MH
    I remember when this place was cool.

  10. #10
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by tamilselvan
    By the way, where is New Joisey? Is it in Europe?

Posting Permissions

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


Click Here to Expand Forum to Full Width