In Oracle, Is it safe to drop tables containing a large amount of data?

I have a production Oracle database which contains a large amount of data backed up in tables whic开发者_C百科h were made during previous work. The tables are independent of each other and the rest of the database.

I want to remove these backups, preferably in one shot. I know in more recent versions of Oracle dropped tables don't actually get dropped until purged from from the recycle bin. I will take of that.

Is it safe to DROP them all at once? Is there a performance penalty during the DROP operation? Is there a chance to run out of resources during the operation?

What is the safest way to do this?

It's probably safe to drop them all at once.

In general, dropping a table is very quick regardless of the size of the table. DROP doesn't really change any data, Oracle just changes the data dictionary to mark the space as available. I've dropped lots of tables with hundreds of gigabytes or more of data and never had a problem. (Your datafiles may not be sized properly anymore, but that's another issue.)

Other than dependencies and locks, the only time I've ever seen a drop take a (relatively) long time was because of delayed block cleanout. Basically, if you update, delete, or insert (without append) a lot of data, Oracle may write some transaction data to the blocks. The reason for this is to make COMMIT instantaneous, but it means that the next query that even reads from the table may have to clean up the old transaction entries.

But your chances of running into that problem are small. If you have very large tables they were probably created with direct path inserts, or someone else has already queried the table and cleaned out the blocks. Even in the worst case, if your system was good enough to write the data it will probably be good enough to get rid of it (although you could run into ORA-01555 snapshot too old if the transactions are too old, or out of archive log space from the extra redo from delayed block cleanout, etc.).

If the tables have no dependents and are not in use, its safe to drop them all at once. If you are worry about the new recyclebin feature, you can do "drop table table_name purge" and it'll bypass the recyclebin and get purge without having to purge them from the recyclebin.