In Oracle, is it safe to delete tables containing large amounts of data?

I have an Oracle production database, which contains a large amount of data, backups in the tables that were made during the previous work. Tables are independent of each other and the rest of the database.

I want to delete these backups, preferably with one shot. I know that in later versions of Oracle, drop-down tables are not actually discarded until they are emptied from the trash. I will take it.

Is it safe to DROKE them all at once? Is there a penalty for performing a DROP operation? Is it possible to exhaust resources during an operation?

What is the safest way to do this?

+4
source share
2 answers

It is probably safe to drop them all at once.

In general, deleting a table is very fast, regardless of the size of the table. DROP really does not modify any data, Oracle just changes the data dictionary to mark the space as available. I have lost many tables with hundreds of gigabytes or more of data and have never experienced a problem. (Your data files can no longer be sorted, but this is another problem.)

With the exception of dependencies and locks, the only time I saw a crash was (relatively) a long time due to the delay in clearing the block. Basically, if you update, delete, or insert (without adding) a lot of data, Oracle can write some transaction data in blocks. The reason for this is to make COMMIT instant, but this means that the next query, which even reads from the table, can clear old transaction records.

But your chances to face this problem are small. If you have very large tables, they were probably created with direct path inserts, or someone else already requested the table and cleared the blocks. Even in the worst case scenario, if your system was good enough to write data, it will probably be good enough to get rid of it (although you can run the ORA-01555 snapshot too slow if the transactions are too old or from the archive the log space from additional repeat from deferred block cleaning, etc.).

+5
source

If there are no dependents in the tables and they are not used, you can immediately reset them. If you are worried about the new recyclebin function, you can do a "drop table_name purge" and it will go around the cart and get cleaned without trying to clear them from recyclebin.

+2
source

All Articles