sql - Oracle performance when dropping all tables -

i have following oracle sql:

begin  -- tables c in (select table_name user_tables) loop execute immediate ('drop table '||c.table_name||' cascade constraints'); end loop;  -- sequences c in (select sequence_name user_sequences) loop execute immediate ('drop sequence '||c.sequence_name); end loop;  end; 

it given me dev, , have no idea how works, drops tables in our database.

it works, takes forever!

i don't think dropping of tables should take long. what's deal? and, can script improved?

note: there somewhere around 100 tables.

"it works, takes forever!"

forever in case meaning less 3 seconds table :)

there more dropping table dropping table. there dependent objects drop - constraints, indexes, triggers, lob or nested table storage, etc. there views, synonyms stored procedures invalidate. there grants revoked. table's space (and of indexes, etc) has de-allocated.

all of activity generates recursive sql, queries select or update data dictionary, , can perform badly. if don't use triggers, views, stored procs, database still has run queries establish absence.

unlike normal sql cannot tune recursive sql can shape environment make run quicker.

i'm presuming development database, in objects built , torn down on regular basis, , you're using 10g or higher.

1. clear out recycle bin.

   sql> purge recyclebin;

2. gather statistics data dictionary (will require dba privileges). these may gathered, default behaviour in 10g , 11g. find out more.

3. once have dictionary stats ensure you're using cost-based optimizer. ideally should set @ database level, can fix @ session level:

   sql> alter session set optimizer_mode=choose;