Most efficient way to perform table operations (spec. drop) on tables having table names that match a certain criteria I am performing a schema dump of certain tables. This accompanied by dropping the tables that will be dumped. For example I need to drop all tables that start with, let's say, "SRC_". You guys think of a more efficient way of dropping those tables than the script below? Thanks.

begin
  for rec in (select TABLE_NAME
                from ALL_TABLES
               where TABLE_NAME like 'SRC|_%' escape '|'
                 and OWNER = 'SOME_SCHEMA')
  loop
    execute immediate 'drop table ' || rec.TABLE_NAME;
  end loop;
end;
/
Why does the code need to be more efficient?  It seems unlikely that you would be dropping large numbers of tables frequently or that it would take enough time to drop tables that it would be worth optimizing.

What trade-offs are you willing to make to cause the code to be more efficient?  And what do you mean by "efficient" in this context-- wall clock time?  The resources consumed on the server?  Something else? The code that you have written is very straightforward and easy to follow.  You could complicate things by, for example, submitting multiple jobs that each drop a subset of the tables so that you can have multiple threads working simultaneously.  But that would involve making the code much more complicated-- you'd need to assign work to different threads and then coordinate the responses from each thread to verify that all the tables were dropped successfully.  It seems most unlikely that this would be a trade-off that you would really want to make though it would reduce the wall clock time needed to drop all the tables.