cancelling drop table

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

The table will not be unusable. If you manage to cancel it without crashing your instance, it will probably be OK.

Hi marist

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

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.

Hi

So I guess the table will remain intact right?

The table will not be unusable. If you manage to cancel it without crashing your instance, it will probably be OK.

Hi Sweetie

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

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