I've got a production Oracle database which consists of a lot of data supported in tables that have been made throughout previous work. The tables are separate from one another and also the relaxation from the database.
I wish to remove these backup copies, ideally over night. I understand in additional recent versions of Oracle dropped tables don't really get dropped until cleared from in the trash can. I'll take of this.
Could it be safe to decrease them all at one time? It is possible to performance penalty throughout the DROP operation? It is possible to opportunity to exhaust assets throughout the operation?
What's the most secure method of doing this?
It's most likely safe to decrease them all at one time.
Generally, shedding a table is extremely quick no matter how big the table. DROP does not really change data, Oracle just changes the information dictionary to mark the area as available. I have dropped plenty of tables with 100s of gb or even more of information rather than were built with a problem. (Your datafiles might not be sized correctly any longer, but that is another problem.)
Apart from dependencies and locks, the only real time I have seen a drop have a (relatively) very long time was due to postponed block cleanout. Essentially, should you update, remove, or place (without append) lots of data, Oracle may write some transaction data towards the blocks. The reason behind this really is to create COMMIT immediate, however it implies that the following query that even reads in the table might have to cleanup that old transaction records.
But your odds of encountering this problem are small. For those who have large tables these were most likely produced with direct path card inserts, or another person has queried the table and washed the blocks. Even just in the worst situation, in case your system was sufficiently good to write the information it'll most likely be sufficiently good to eliminate it (even though you could encounter ORA-01555 snapshot too old when the transactions are extremely old, or from archive log space in the extra redo from postponed block cleanout, etc.).
When the tables don't have any loved ones and therefore are not being used, its safe to decrease them all at one time. If you're be worried about the brand new recyclebin feature, that you can do "drop table table_title purge" and will also bypass the recyclebin and obtain purge without needing to purge them in the recyclebin.