cancelling drop table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: cancelling drop table

  1. #1
    Join Date
    Oct 2000
    Posts
    139
    Hi

    I would like to know what happens when I start to drop a table and it takes 3 hours then I cancel it after one hour. Will I lose anything from that table or Oracle will do a rollback? Or table will be unusable?

    Thanks

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    The table will not be unusable. If you manage to cancel it without crashing your instance, it will probably be OK.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Oct 2000
    Posts
    139
    Hi marist

    thanks for the reply
    does this means that Oracle will do a rollback?

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    DROP TABLE is a DDL command. DDL commands either complete successfully or don't complete successfully. There is no such concept as "rollback" for a DDL operation.

    For example, if you issue DROP TABLE xyz and it completes successfully, you can not issue ROLLBACK and get your table back.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Oct 2000
    Posts
    139
    Hi

    So I guess the table will remain intact right?

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    [B]The table will not be unusable. If you manage to cancel it without crashing your instance, it will probably be OK. [/B]
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Hi Sweetie

    if you manage to cancel it then your table will remain intact yes, like nothing was done to it

  8. #8
    Join Date
    Nov 2000
    Posts
    26

    Question

    I am sure u would be giving a whole lot of thought before u go in for a table drop which is very large, and if u have given the table drop oracle would be getting the required blocks into oracle memory or the sga ( if the blocks of course are not already there in the memory), here the blocks get cleaned out and the log of which is put into the log files(in case the table was created without the nolog option), now remember that there is no rollback data generated since the table drop is a ddl(as already pointed out),if now the table drop is cancelled which is obviously killing the process, then the database would become inconsitent with the memory image and file image being different, which also would mean that the locks on the table may not be released, effectively stopping u from accessing the table,now also since checkpointing occurs at the normal pace, the datafiles also will be having the latest information on the table's data deletion, now since the table is locked and the database inconsistent, the next startup would release the table's locks and complete consistency., this would mean some of uer data in the table is gone forever, unless u have taken a backup of uer table earlier.,
    but do remember to contact metalink for the full ramifications, or better still copy the table to a testing area and complete uer exp.s then do it on the actual database.
    beats me why u would like to drop a table then cancel the drop
    soren

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