Q:

Oracle performance when dropping all tables

I have the following Oracle SQL:
Begin

-- tables
for c in (select table_name from user_tables) loop
execute immediate ('drop table '||c.table_name||' cascade constraints');
end loop;

-- sequences
for c in (select sequence_name from user_sequences) loop
execute immediate ('drop sequence '||c.sequence_name);
end loop;

End;

It was given to me by another dev, and I have no idea how it works, but it drops all tables in our database.
It works, but it takes forever!

I don't think dropping all of my tables should take that long.  What's the deal?  And, can this script be improved?
Note: There are somewhere around 100 tables.

A:

"It works, but it takes forever!"

Forever in this case meaning less than three seconds a table :)  
There is more to dropping a table than just dropping the table.  There are dependent objects to drop as well - constraints, indexes, triggers, lob or nested table storage, etc.  There are views, synonyms stored procedures to invalidate.  There are grants to be revoked.  The table's space (and that of its indexes, etc) has to be de-allocated.
All of this activity generates recursive SQL, queries which select from or update the data dictionary, and which can perform badly.  Even if we don't use triggers, views, stored procs, the database still has to run the queries to establish their absence.  
Unlike normal SQL we cannot tune recursive SQL but we can shape the environment to make it run quicker.
I'm presuming that this is a development database, in which objects get built and torn down on a regular basis, and that you're using 10g or higher.  

Clear out the recycle bin.  
SQL> purge recyclebin;
Gather statistics for the data dictionary (will require DBA privileges).   These may already be gathered, as that is the default behaviour in 10g and 11g.  Find out more.  
Once you have dictionary stats ensure you're using the cost-based optimizer.  Ideally this should be set at the database level, but we can fix it at the session level: 
SQL>  alter session set optimizer_mode=choose;

